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?
- Come search SO SQL Azure Reseed and top hit stackoverflow.com/questions/11644507/…paparazzo– paparazzo2012-09-13 22:06:49 +00:00Commented Sep 13, 2012 at 22:06
Add a comment |
3 Answers
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
Comments
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.
1 Comment
hawbsl
see @undefined's answer. this answer, while accepted, is obsolete
I have recently come up against this problem myself. I resolved it by inserting and deleting items in the table until I reached the desired seed value.
In the example below, this will reseed the Users table so the next new item will have Id of 3500.
BEGIN TRANSACTION GO DECLARE @max_id int SELECT @max_id = max([Id]) FROM users WHILE (@max_id < 3499) BEGIN INSERT INTO Users (Username, Password) VALUES ('','') SELECT @max_id = max([Id]) FROM Users DELETE FROM Users WHERE [Id] = @max_id END COMMIT Hope this helps.
