How to reset identity seed in Sql Azure

azure-sql-database c# entity-framework sql

Question

To reset the Identity Seed of a column, I've tried the following:

DBCC CHECKIDENT ('dbo.Stuff', RESEED, 0)

This does not function on SQL Azure, therefore I was unsure of the best course of action. Drop and build a new table?

1
16
9/13/2012 10:03:30 PM

Accepted Answer

You've already seen it, Windows Azure SQL Database does not presently support DBCC CHECKIDENT.. The best approach to reseed the identity is to create a new table with the same structure, set the identity to the Reseed value IDENTITY(reseedval,1), load any existing entries using SET IDENTITY INSERT tablename ON, remove the old table, and rename the new one. Reset restrictions, permissions, etc.

6
12/6/2012 7:52:26 PM

Popular Answer

I believe @haldyr's response was incorrect and was probably unjustly removed.

SQL Azure's most recent version (v12) DOES supportDBCC CHECKIDENT But if your Azure database server isn't brand-new, you'll need to update (and many others).

It's simple to upgrade. On the new portal, go to your Azure DB Server (the server, not the database itself), and click the largeLatest Update button.

enter image description here

NOTE: Be aware that there are several limitations to updating (such as the fact that certain older software doesn't support it), therefore it's important to read the article http://azure.microsoft.com/en-us/documentation/articles/sql-database-preview-whats-new/#V12AzureSqlDbPreviewGaTable.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow