Sql Azure 2012 Reseed Identity

Question

User-1824994464 posted

Hi,

I am  using Sql Azure 2012 in my  MVC Web Application. Every couple of days the identity column is jumped by 1000 numbers. I heard that in sql azure 2012 there is no guarantee of the idenity column. Instead they were telling to use the sequence which is new in  sql 2012. Is it possible to use the sequence for 1 table( instead of whole database) if so can you guys give me some example.? Thanks for your help.

Answer 1

User-821857111 posted

Sequences apply to the whole database but you can create multiple named sequences. All you need to do is ensure that you use the same one for a specific table and don't use it for any others. 

CREATE Sequence OrderTableSeqence
    START WTH 1
    INCREMENT BY 1;
GO
INSERT INTO Orders 
(
    OrderId,
    CustomerId,
    DateCreated,
    etc...) 
VALUES
(
    SELECT NEXT VALUE FOR OrderTableSeqence,
    @CustomerId,
    GetDate(),
    etc....)

See more here: http://technet.microsoft.com/en-us/library/ff878091.aspx