none
SQL AZURE - Identity Reseed

    Question

  • Below the body of this post are PRIOR POSTS regarding SQL Azure Identity Reseed function not being available in 2009,  running this function presently also shows it not yet available in Azure in Sept 2010

    I have a meeting tomorrow to speak to this matter as we have multiple projects we'd like to move to SQL Azure.   Is there any other options to move existing on-premise SQL tables to SQL Azure, retaining the original primary key and its IDENTITY seed and then of course the table resuming its auto-incrementing from the last record?   This of course speaks to the concern of data integrity with foreign keys in numerous normalized tables.

    thanks for any help and input in helping us get ramped up to Azure.

    PRIOR POSTS ON THIS SUBJECT:

    "As DBCC CHECKIDENT (tablename, RESEED, 0) is not supported in this build of SQL Azure, how can you reseed Identity columns?  As I struggle to work out how to manage replication issues between cloud-based data and standard data-sets, a way to reseed an ident without creating a new table is desirable"
    reply:  "At this point, I am not aware of any way to accomplish this without creating a new table.  However, we do have this on the table for a future release"

    Wednesday, September 22, 2010 8:44 PM

All replies

  • I am not sure I understand what you are trying to achieve exactly, but I am going to assume the following conditions: you currently have data in a SQL Azure table and you would like to append records coming from a SQL Server, forcing the identity values into SQL Azure. Once done, you would like to turn the identity column back on starting at the latest value inserted by the job.

    If my understanding is correct you should be able to do something like this (this was tested in SQL Azure):

    -- create table TESTUSER (id int identity(1,1) primary key, name nvarchar(100) not null) -- test table for this example

    -- let's assume TESTUSER is the table in SQL Azure with a primary key and identity of (1,1)
    -- let's add some data first:

     


    INSERT
    INTO TESTUSER VALUES ('test1')
    INSERT INTO TESTUSER VALUES ('test2')
    INSERT INTO TESTUSER VALUES ('test3')

    set
    identity_insert TESTUSER on  -- this basically turns off IDENTITY

    INSERT INTO TESTUSER (id, name) VALUES (5, 'test5')   -- so we can jam any value for column ID

    set

     

    identity_insert TESTUSER off  -- then turn it back on

    INSERT

     

    INTO TESTUSER VALUES ('test6') -- ID starts at 6 from this point

    SELECT

     

    * FROM TESTUSER

     


    Herve Roggero - SQL Server Firewall and Auditing Consultant
    Friday, September 24, 2010 1:46 AM
    Moderator
  • Hello,

    In addition to what Herve said, I can suggest using SQL Azure Migration Wizard. It prefectly migrates On premise SQL Server data to SQL Azure, keeping all relationships and identity columns. If you do this on a fresh clean database, your identity columns will countinue to increment from the last inserted during migration value. If you already have existing tables in SQL Azure, it will be harder to migrate the data from on premise SQL Server, but not impossible. The approach would be something like described from Herve.

    Reseeding to 0 will only be needed in case you want to clean data from all the tables that already exists, and this can be achieved via the TRUNCATE TABLE command.

    I can confirm that in version 10.25.9386.0:

    Microsoft SQL Azure (RTM) - 10.25.9386.0   Jul 21 2010 12:47:47   Copyright (c) 1988-2009 Microsoft Corporation

    Identity reseed is still not available

    Msg 40518, Level 16, State 1, Line 1
    DBCC command 'CHECKIDENT' is not supported in this version of SQL Server.

    Wednesday, September 29, 2010 7:53 PM
  • Hi

    I have a table that contains data from multiple servers where I have used various ranges of the bigint primary key value to prevent duplicates. I now want to have an identity seed of 2000 but as there is data with values over 1000000000 in the table, SQL Azure sets my seed as a number higher than this. This is regardless of what I set in the CREATE TABLE statement.

    Is there a way I can change the seed value without using DBCC CHECKIDENT? I can reload the table's data, but every time I do the identity seed updates to a high number...

    Cheers

    David

    Thursday, December 09, 2010 3:32 PM
  • Hi

    Anyone from Microsoft care to comment on this? Is there a workaround/solution to my problem?

    Cheers

    David

    Tuesday, December 14, 2010 9:16 AM
  • I'm assuming from the lack of answers that there isn't a solution until SQL Azure starts to support DBCC CHECKIDENT. Is this on the roadmap?
    Thursday, December 16, 2010 11:28 AM
  • A standard solution would be to move from an int identity to a string based approach, and create your id's with a prefix for each server + DateTime.Now ticks. Be aware of clustering index issues though (I vaguely remember a post on the SQL Azure blog why it is bad to use guids as PKs, that might apply to this approach too).

     

     


    Find apps fast on eblizz.com . Follow me on twitter at twitter.com/martin_sunset . Get on the invite list for freshfugu.com , a fun way to stay on top of your favorite shops and never miss out on that perfect fashion piece.
    Friday, December 17, 2010 2:13 PM
  • The way we got around the seeding issue was to script the tables out and then before the create statement alter the Seeding of them in the create statement, then import the data.

     


    http://www.dragandtag.com
    Thursday, December 08, 2011 4:15 PM
  • I am not sure I understand what you are trying to achieve exactly, but I am going to assume the following conditions: you currently have data in a SQL Azure table and you would like to append records coming from a SQL Server, forcing the identity values into SQL Azure. Once done, you would like to turn the identity column back on starting at the latest value inserted by the job.

    If my understanding is correct you should be able to do something like this (this was tested in SQL Azure):

    -- create table TESTUSER (id int identity(1,1) primary key, name nvarchar(100) not null) -- test table for this example

    -- let's assume TESTUSER is the table in SQL Azure with a primary key and identity of (1,1)
    -- let's add some data first:

     


    INSERT
    INTO TESTUSER VALUES ('test1')
    INSERT INTO TESTUSER VALUES ('test2')
    INSERT INTO TESTUSER VALUES ('test3')

    set
    identity_insert TESTUSER on  -- this basically turns off IDENTITY

    INSERT INTO TESTUSER (id, name) VALUES (5, 'test5')   -- so we can jam any value for column ID

    set

     

     identity_insert TESTUSER off  -- then turn it back on

    INSERT

     

     

     INTO TESTUSER VALUES ('test6') -- ID starts at 6 from this point

    SELECT

     

     * FROM TESTUSER

     


    Herve Roggero - SQL Server Firewall and Auditing Consultant
    Thank you, This worked wonderfully. A word to the wise though the seed value will continue to increment from it's highest position unless you trunc the table. So if you inserted too many records your faced with leaving identity inserts on until you catch up, or trunc and start over. Any word on when sql azure will get CHECKIDENT?
    Thursday, January 05, 2012 11:58 PM
  • Reseeding to 0 will only be needed in case you want to clean data from all the tables that already exists, and this can be achieved via the TRUNCATE TABLE command.

    TRUNCATE Table command is useless because it can not be used if there is any foreign key, and even if you disable foreign keys, TRUNCATE still will not allow you  to truncate the table.

    Regards,

    LuxSpes

    Wednesday, April 11, 2012 8:34 PM
  • Is there a workaround for just getting the current identity value? Thanks.

    DBCC CHECKIDENT('Production.Product');


    Kalman Toth SQL 2008 GRAND SLAM
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Tuesday, February 05, 2013 10:02 PM
  • I would also like to know if there is a work around
    Thursday, August 15, 2013 1:58 PM
  • In SQL Azure, you can get it with:

    SELECT IDENT_CURRENT('TableName')


    Monday, September 30, 2013 7:21 PM
  • These commands do not work, did exactly as suggested, but the sequence does not return the desired point.
    Sunday, October 13, 2013 9:25 PM
  • Use a GUID as the PK.  If you want it sequential then have a look at COMBs.

    Azure is multi-tenant, and the hardware is abstracted.  You have no control over physical location of your database files, and sequential disk access is therefore not guaranteed.  The fragmentation caused by using a GUID as your clustering key is mitigated somewhat by this, as it's not necessarily going to cause performance degradation.

    Alternatively, keep your source identity values as a separate column and set that as your clustering key, but keep the PK as a GUID (nonclustered).

    This approach also gives you the flexibility to scale out (and back in again) through sharding, i.e. splitting /partitioning your data across several databases.  If scaling in, you need to merge your data which introduces the same issue, however using a GUID as the PK allows this without issue.

    Monday, October 14, 2013 10:05 AM