Menu
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?
434
"To change the original seed value and reseed any existing rows, you must drop the identity column and recreate it specifying the new seed value. When the table contains data, the identity numbers are added to the existing rows with the specified seed and increment values.
Delete does not reset identity values.
Sometimes you may need to reset the auto incremented identity column to start again from the beginning or from a specific number again. Here, we will see how to reset identity seed in SQL Server. To re-seed the identity column, you can use use the DBCC CHECKIDENT management comment.
If you want to reset the identity column in SQL Server, you can use the DBCC CHECKIDENT procedure with extra parameters: Resetting our produce table to use a value of 1 is done using this command: However, there are existing records in the table, so if we reset it and insert a new record, there will be an error.
The seed value is the value inserted into an identity column for the very first row loaded into the table. All subsequent rows contain the current identity value plus the increment value where current identity value is the last identity value generated for the table or view.
Change the original seed value that was specified for an identity column when the table or view was created. Reseed existing rows in a table or view. To change the original seed value and reseed any existing rows, you must drop the identity column and recreate it specifying the new seed value.
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.
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
83
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.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
