How to turn auto-generation of ID numbers off

identityAlthough not recommended, sometimes it is necessary to turn auto-generation of ID numbers off in your database. For instance, I was in a situation where I was completely redesigning a database (deleting tables, renaming tables, purging data, entering new data, ec.). However, the client was insistent on maintaining the ID numbers that were associated with specific items that they wanted to keep.

This is how I turned off the auto-generation of ID numbers in Microsoft SQL Server 2008 Management Studio:

  1. Right-click on the table name and hit Design
  2. In the Column Properties tab, find Identity Specification and expand the section
  3. For (Is Identity) change the dropdown to No
  4. Hit Save

If you encounter the following error message, you will need to perform an additional step:error

Go to Tools > Options and click “Designers”.

Uncheck the “Prevent saving changes that require table re-creation” and hit OK.


Try the above steps again.

When you are done, make sure to turn auto-generation back on!!!

Editing ALL rows in MS SQL Server Management Studio 2008

Edit all rows in MS SQL Server Management Studio 2008

Edit all rows in MS SQL Server Management Studio 2008

After I upgraded from Microsoft SQL Server 2005 to 2008, one of the first changes that I noticed was the menu that appears when you right-click on a table was missing my beloved ‘Open Table’ option.  In the 2005 version, I was able to click this option and my table would open up all the rows in it.  This option was replaced by an “Edit top 200 rows” in the 2008 version.

While this is all well and good, why go through the trouble of clicking the “Edit top 200 rows”, and then have to modify the query to have it return all rows?  To get rid of the “Edit Top 200 Rows”, go to Tools > Options > SQL Server Object Explorer and in the “Table and View Options“, change the following:

  • Value for Edit Top <n> Rows command = 0
  • Value for Select Top <n> Rows command = 0

Hit OK.

And there you have it.  When you right-click a table, you will see the option for “Edit All Rows”.