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"

• Moved by Brian AurichMicrosoft employee Tuesday, September 28, 2010 11:40 PM migration (From:SQL Azure)
• Moved by Brian AurichMicrosoft employee Thursday, February 10, 2011 9:59 PM (From:Windows Azure Storage)

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):

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

identity_insert TESTUSER off  -- then turn it back on

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

* FROM TESTUSER

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

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

Hi

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

Cheers

David

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).

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.

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

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

Is there a workaround for just getting the current identity value? Thanks.

DBCC CHECKIDENT('Production.Product');

In SQL Azure, you can get it with:

SELECT IDENT_CURRENT('TableName')

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.