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"

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

Hi

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

Cheers

David

Answer 5

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?

Answer 6

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.

Answer 7

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

Answer 8

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?

Answer 9

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

Answer 10

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