SQL v.Next DENALI SEQUENCING

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

http://msdn.microsoft.com/en-us/library/ff878091.aspx