As you’ve experienced, test data and junk typically ends up in a development database. At some point, you might want to get your tables in order because Lookup table Id columns sometimes match to constants in your code, and so

Here is what you’d do on SQL Server…

If you want to insert specific IDs, you would typically use IDENTITY_INSERT:

SET IDENTITY_INSERT [myTable] ON

INSERT myTable (ID) VALUES (42)
INSERT myTable (ID) VALUES (43)
..

SET IDENTITY_INSERT [myTable] OFF

You typically do this if you want to jump ahead of existing IDs.
Note: If the Ids already exist, you will get  a Primary key violation error. 
After this operation, new rows will start with IDs from 44.

If you want to backtrack on IDs (and add new data) and want to use auto-increment as is, you would want to delete rows in a table and reset the auto-increment id value.

In order to do this, we need to turn to the back-door man in SQL Server, viz. dbcc

e.g. The following statement will set the next Id of myTable to 42

DBCC CHECKIDENT (myTable, RESEED, 42)
GO

For details on CheckIdent, go to: http://msdn.microsoft.com/en-us/library/aa258817(SQL.80).aspx

Advertisements