Reset Identity SQL Azure?

Question

Any work around to RESET Identity Keys in SQL Azure?

Actually I want to refresh my tables which has Identity columns.

Thanks & Regards,

Sandip

 

---

Thanks, Sandip

Answer 1

Hello Sandeep,

I guess I would need more clarity on your question, what do you mean by set identity keys in SQL Azure ? If you want to insert an explicit value in an identity column in SQL Azure then use SET IDENTITY_INSERT statement. Refer http://msdn.microsoft.com/en-us/library/ee336270(v=MSDN.10).aspx .If this didn't answer your question then please clarify your scenario.

---

Anurag Sharma

Answer 2

Hi Sandip,

In SQL Server, we can use the following DBCC command to reset the identity seed value:

DBCC CHECKIDENT ("TableName", RESEED, 10)

However, DBCC command 'CHECKIDENT' is not supported in SQL Azure. Please refer to the following sample to “modify” the identity seed and increment:

CREATE TABLE newtable (col1 INT IDENTITY(1,1) PRIMARY KEY, col2 INT)

INSERT INTO newtable VALUES (1)
INSERT INTO newtable VALUES (2)

SELECT * FROM newtable

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_newtable
	(
	col1 INT IDENTITY(10,10) PRIMARY KEY,
	col2 INT 
	)  
GO
SET IDENTITY_INSERT dbo.Tmp_newtable ON
GO
IF EXISTS(SELECT * FROM dbo.newtable)
	 EXEC('INSERT INTO dbo.Tmp_newtable (col1, col2)
		SELECT col1, col2 FROM dbo.newtable')
GO
SET IDENTITY_INSERT dbo.Tmp_newtable OFF
GO
DROP TABLE dbo.newtable
GO
EXECUTE sp_rename N'dbo.Tmp_newtable', N'newtable', 'OBJECT' 
GO

GO
COMMIT

INSERT INTO newtable VALUES (3)
INSERT INTO newtable VALUES (4)

SELECT * FROM newtable

--DROP TABLE newtable

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

Answer 3

Hi Sandip,

In SQL Server, we can use the following DBCC command to reset the identity seed value:

DBCC CHECKIDENT ("TableName", RESEED, 10)

However, DBCC command 'CHECKIDENT' is not supported in SQL Azure. 

Hi Jian,

Is there any idea on when this command will be supported?

Thanks,

James

Answer 4

I am afraid that swapping millions of rows between tables is not a practical solution for IDENTITY RESEED.

---

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