Archive for the ‘SQL’ Category

Windows Azure SDK 1.7 and new features

June 21, 2012

NHDN Cloud Computing talk Jun 20th, 2012.

We discussed All new features from Jun 7th announcement and SDK 1.7.  How to utilize all the cloud services, cloud storage and virtual machine and the web sites.

click here to download the presentation

click here to download the sample code on caching and service bus ( before running the sample replace the value for appsetting keys )

Advertisements

SQL v.Next DENALI SEQUENCING

August 23, 2011

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