Many applications need sequentially incremental number as unique/primary key of records. SQL Server 2005 today supports identity column as the primary mean to general sequence number, which generates the sequence number upon the execution of DML (insert) or bulk insert. The value of the insertion is known (when using insertion DML), post the execution of the DML. In some implementations, the sequence numbers (or next unique incremental number) need to be generated prior to the DML operation. For example, if sequence number can be retrieved prior to the DML operation, it can be easily implemented in situations where multiple DML (inserts, or even updates) using the same sequence number can be performed, and data can be insert into different tables, for example, parent-child, or based on the sequence value, data can be insert into different tables.
SQL Server 2005 Migration Assistant (SSMA) provides a different mechanism for sequence number generation. SSMA’s approach is to use a single table to hold all the sequence numbers. Each sequence number object is represented by a single row of record, which holds the property of the sequence, e.g. sequence name, current value, increments…etc. The generation of the next value is invoked by an extended stored procedure in order to minimize the impact on application concurrency – the execution of extended procedure is not within the transaction scope of the DML statement, and it holds very short duration locks on the row that represents the sequence number.
This BLOG describes two more methods of sequence number generation.
Option 1
This option is easy to use and maintain, but it is suitable only with lower volume invocations. It is also one of the widely used approaches, and often misused. This method uses a single table to hold all the sequences needed for the application, and each sequence is represented by one single record, thus serializes operation requesting next value of the same sequence number.
1. Create the following objects
Table AllSequences holds the information of all the sequences. As a simple example, the table and subsequent stored procedure is kept quite simple. Further enhancement is certainly possible based on the needs – for example, additional information of sequence such as maximum range, minimum range, loopback(yes/no) can be added.
· SeqName – name of the sequence
· Seed – seed value of the sequence
· Incr – incremental value of the sequence
· Currval – current value of the sequence
Create table AllSequences (
SeqName nvarchar(255) primary key, -- name of the sequence
Seed int not null default(1), -- seed value
Incr int not null default(1), -- incremental
Currval int
)
Go
The following stored procedure is used to create a sequence. The following input parameter is expected:
· SeqName – name of the sequence
· Seed – seed value of the sequence
· Incr – incremental value of the sequence
create procedure usp_CreateNewSeq
@SeqName nvarchar(255),
@seed int = 0,
@incr int = 1
as
begin
declare @currval int
if exists (
select 1 from AllSequences
where SeqName = @SeqName )
begin
print 'Sequence already exists.'
return 1
end
if @seed is null set @seed = 1
if @incr is null set @incr = 1
set @currval = @seed
insert into AllSequences (SeqName, Seed, Incr, CurrVal)
values (@SeqName, @Seed, @Incr, @CurrVal)
end
go
The following stored procedure is used to generate the next value of the specified sequence through input parameter SeqName.
create procedure usp_GetNewSeqVal
@SeqName nvarchar(255)
as
begin
declare @NewSeqVal int
set NOCOUNT ON
update AllSequences
set @NewSeqVal = CurrVal = CurrVal+Incr
where SeqName = @SeqName
if @@rowcount = 0 begin
print 'Sequence does not exist'
return
end
return @NewSeqVal
end
go
2. To create the new sequence, for example:
usp_CreateNewSeq N'TestSeq'
3. To retrieving the new sequence value, for example for sequence “TestSeq”
usp_GetNewSeqVal N'TestSeq'
One thing to be careful is to keep the execution of the stored procedure usp_CreateNewSeq outside the scope of subsequent transaction which uses the sequence number generated. This can minimize impact of blocking.
Typical usage
Declare @NewSeqVal int
Execute @NewSeqVal=usp_GetNewSeqVal @seqname=N'TestSeq'
print @NewSeqVal
Option 2
This method is intended for higher-throughput environments – such as thousands of transactions per second. A table with identity column is created for every sequence. Since the generation of identity value is lightweight, and not within the transaction scope of subsequent DML, this method can significantly improve the concurrency over the first method. In addition, it also consumes less resource than extended procedure.
1. Create one table with the following structure for each sequence, assuming seed 1, with increment of 1).
create table <tablename> (
SeqID int identity(1,1) primary key,
SeqVal varchar(1)
)
2. Create the following stored procedure. A delete operation is performed to remove the rows in the sequence table to reduce maintenance. This obviously adds some load to the transaction however it should be quite minimal. As an alternative, for higher throughput, the delete statement can be removed and a maintenance job can be scheduled during off-hour to purge the records in the table (truncate should not be used since it resets the identity value).
create procedure GetNewSeqVal_<tablename>
as
begin
declare @NewSeqValue int
set NOCOUNT ON
insert into <tablename> (SeqVal) values ('a')
set @NewSeqValue = scope_identity()
delete from <tablename> WITH (READPAST)
return @NewSeqValue
end
3. To retrieve the new value in a result set, simply execute the following:
GetNewSeqVal_<tablename>
Typical usage
Declare @NewSeqVal int
Exec @NewSeqVal = GetNewSeqVal_<tablename>
Among the two options described above, Option 1 is widely used in different variations but prone to blocking/deadlocking. Option 2 improves concurrency significantly with a simple approach.
分享到:
相关推荐
A thesis submitted to McGill University in partial fulfillment of the requirements of the degree of Master’s of Science
and artists an introduction to methods of simulating, animating, and rendering ocean water environments. CG water has become a common tool in visual effects work at all levels of computer graphics, ...
Simulating planar reflection using two-pass rendering and texture mapping 1. There ‘s a table with flat rectangular semi-reflective table-top in the 3D scene 2. On/Around the table are some 3D ...
simulating wireless communication systems C++代码 systems sims.zip
S7-PLCSIM Simulating Modules由西门子公司推出,可以替代西门子硬件PLC的仿真软件,当培训人员设计好控制程序后,无须PLC硬件支持,可以直接调用仿真软件来验证。
费曼的《Simulating Physics with Computers》,量子计算机这个概念应该就是从这篇文章提出的,UCBerkerly的一门课程的阅读材料,课程网址:http://www.eecs.berkeley.edu/~christos/classics/
Library of Congress Control Number: 2009932687 Springer-Verlag Berlin Heidelberg 2009 Springer Series in Aerospace Technology ISSN 1869-1730 e-ISSN 1869-1749
1.13 Looping in Parallel over Index and Sequence Items 1.14 Looping Through Multiple Lists 1.15 Spanning a Range Defined by Floats 1.16 Transposing Two-Dimensional Arrays 1.17 Creating Lists ...
Simulating Husky 仿真代码
simulation quantitative
In this paper, we augment existing techniques for simulating flexible objects to include models for crack initiation and propagation in three-dimensional volumes. By analyzing the stress tensors ...
abbr_ Synthesizing, and Simulating ASICs and FPGAs using VHDL or Verilog.part04
Simulating Clouds with Procedural Texture
通信信道的绝好资料!里面附有MATLAB相应代码~~
simulating-ocean-wate.pdf
Virtual Reality: Simulating and Enhancing the World with Computers is a revised and expanded edition of Virtual Reality: Computers Mimic the Physical World, which was published in 1998 as part of the ...
Looping over Items and Their Indices in a Sequence Recipe 4.5. Creating Lists of Lists Without Sharing References Recipe 4.6. Flattening a Nested Sequence Recipe 4.7. Removing or Reordering ...
PEP 3333: Python Web Server Gateway Interface v1.0.1 Other Language Changes New, Improved, and Deprecated Modules email elementtree functools itertools collections threading datetime and time ...
用于模拟在通用色散介质中电磁波传播仿真的一阶段蛙跳交替方向隐式FDTD方法,王向华,尹文言,本文的主要内容是基于改进的一阶段蛙跳交替方向隐式时域有限差分方法对通用色散介质中电磁波的传播特性进行仿真。...