Resetting ID Fields in SQL Server
Part numbers, quotation numbers, item numbers, revisions, serial numbers…they’re all sequential values that need to be unique, and need to be tracked. In most cases (Design Automation, Product Data Management, CRM, etc.) this means the creation of a database table to hand out and track these identifying numbers and their related data. To ensure that the numbers are unique, most tables will be setup with a unique ID field that will automatically be populated by a database server. But what happens when your ID counter (automatic sequencer) needs to be reset?
There are a couple of ways to setup a database to auto-increment ID values. Database triggers that run on the INSERT command are a dependable method of achieving this. Within SQL Server, you have another option – the IS IDENTITY property. By setting the IS IDENTITY property to YES, and filling in a seed value and an increment, the database engine will take care of the auto-incrementing for you.
Since you’re going to test out your functionality (you do test things first, don’t you?), you will naturally fill your table with bogus records. Before rolling out to production, you’ll probably clean up a bit and remove your test data. The first record you add after this point, however, will not start back at 1. The ID fields that you’ve setup will continue to count from where they left off in testing. This is most likely not the desired behavior, but there’s no obvious RESET button anywhere in the SQL Server Management Studio. 
Fortunately, you can achieve the desired result (resetting your counter) with a quick SQL statement using the TRUNCATE command (this works in multiple versions of SQL Server including SQL Server 2005 and SQL Server 2008).
To use this nifty TRUNCATE command, right-select the name of the database in the Object Explorer on the left side of your window and choose NEW QUERY. In the query window, type in the command: TRUNCATE TABLE tablename, where tablename is the name of the database table that contains the ID field that you’ve setup to auto-increment. This method cleanly removes all of the data from the table while leaving the table structure intact. The bonus is that TRUNCATE will also reset your ID fields.
Have you found other ways to get this done using SQL or some other means? If so, please leave us a comment. If you’re looking for more help on TRUNCATE or on SQL syntax and database management in general, please contact us.
Tags: ID, part numbers, SQL, SQL INSERT, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server Management Studio, SQL trigger, SQL TRUNCATE
Read more posts by Paul Gimbel
