I design large enterprise system. One of the challenge i always had is increasing the concurrency while maintaining the integrity. Sequencing solves this problem. Our database system records are designed to use BIGINT as a identifier and distrubuted by number of servers can go up to 2,147,483,647 servers. Every server gets maximum of Int32.MaxValue range of Ids and servers can be synched each other by Peer-Peer or Hub-Spoke synchronization model using sync framework.
Identity column is not a good solution because it can only go upto 2 servers (-ve Ids and +ve ids) other than that we need to get lots long time blocking and locking to work.
Custom stored procedure with table works good but got into concurrency issues.
Solution: Sequencing is the fast and efficient solution that i had ever liked. Here is how it works.
if(not(exists(selectnamefromsys.objectswheretype=‘u’andname=‘mytable’)))
begin
create tabledbo.MyTable
(
someidbigintnotnullprimarykeyclustered,
somevaluenvarchar(50)
)
end
go
CREATE SEQUENCE[dbo].[MyTableSequence]
AS [bigint]
STARTWITH 1
INCREMENTBY 1
MINVALUE 1
MAXVALUE 2147483648
CACHE
GO
–GETTING SING ID
declare @id bigint
select @id=NEXT VALUE FOR [dbo].[MyTableSequence] –GETTING NEXT SEQENCE
insert into mytable(someid,somevalue) values (@id,convert(varchar(40),newid()))
GO
–There are situation that i need to get multiple ids that case we can use sp_sequence_get_range function
–GETTING MULTIPLE IDS
DECLARE @fv sql_variant,@lv sql_variant;
EXEC sys.sp_sequence_get_range @sequence_name=‘[MyTableSequence]’,@range_size= 10,@range_first_value=@fv OUTPUT,@range_last_value=@lv OUTPUT;
–SELECT fv = CONVERT(bigint, @fv), lv = CONVERT(bigint, @lv), next = NEXT VALUE FOR dbo.[MyTableSequence];
–Here is how i use this query
declare @fv1 bigint,@lv1 bigint
set @fv1=convert(bigint,@fv)
set @lv1=convert(bigint,@lv)
while (@fv1<=@lv1)
begin
insert into mytable(someid,somevalue) values (convert(bigint,@fv1),convert(varchar(40),newid()))
set @fv1=@fv1+1
end
go
Summary: I see a Sequencing gives better performance than identity cloumns and easy to use my existing design
You can download the source code here