How to reset identity seed in Sql Azure

azure-sql-database c# entity-framework sql

Question

I have tried the following in order to reset my Identity Seed of a column:

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

This does not work in sql azure, I was wondering what would be the best way to go about doing this. Drop and recreate table?

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

Accepted Answer

As you have seen, DBCC CHECKIDENT is not currently supported in Windows Azure SQL Database. The most complete way to reseed the identity would be to create a new table with the same structure, and set the identity to the Reseed value IDENTITY(reseedval,1), load existing records from the original table using SET IDENTITY_INSERT tablename ON, then drop the old table, and rename the new one. Reset permissions, reset constraints, etc.

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

Popular Answer

I think @haldyr's answer was probably unfairly deleted as its correct.

The latest version of SQL Azure (v12) DOES support DBCC CHECKIDENT (and a bunch of others) however if your azure database server isn't brand new you will need to upgrade.

Upgrading is easy, go to your azure DB Server on the new portal (the actual server not the DB itself) and click the big Latest Update button.

enter image description here

NOTE: Be aware there are a bunch of caveats to upgrading (Like some older stuff doesn't support it), so its worth reading 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