Microsoft SQL Server’s identity column generates sequential values for new records using a seed value. The term seed refers to the internal value SQL Server uses to generate the next value in the sequence. By default, an identity column’s first value is 1 and each new value increments by one (1, 2, 3, 4, and so on).
You can control the column’s first and subsequent values, by specifying those values when you create the column. For instance, you might want to begin with a purchase order number of 1001 rather than 1.
Once the table’s in production you can reset, or reseed the column. In other words, you can change the column’s seed value at any time. For instance, you might reseed a column after deleting records or moving data to an archive table.
The good news is that seeding and reseeding an identity column is easy and relatively safe, if you do it correctly. It’s common to seed a new table or to reseed a production, but empty table. However, as a general rule, it’s not a great idea to reseed an identity column in a table that contains data without some serious checks and balances.
Throughout this article, I use SQL Server Express and Management Studio Express because both are free and therefore available to all readers. However, the logic and instructions are the same in SQL Server.
This blog post is also available in the PDF format as a TechRepublic Download.
Developers and writers alike tend to refer to SQL Server’s identity column property as a data type. It’s actually a column property. In purpose, it’s similar to Microsoft Access‘ AutoNumber column, although SQL Server’s identity column is more flexible. However, there are a few restrictions:
- An identity column must be one of the following numeric data types: decimal, int, numeric, smallint, bigint, or tinyint.
- An identity column can’t accept or store NULL.
- Each table can contain only one identity column.
When you create an identity column, you specify two values: Identity Seed and Identity Increment. The seed value specifies the column’s first (or next) value. SQL Server adds the increment value to the last identity value to generate the next value, in sequence.
For instance, a seed value of 1 and an increment value of 2 will generate the values 1, 3, 5, 7, and so on. By default, both the seed and incremental values are 1. Business rules often require a bit of customization.
Using Management Studio is probably the easiest way to seed an identity column when you create the table. In this case, it’s a simple matter of setting the appropriate property value, as you can see in Figure A. You can also use Management Studio to reseed an existing identity column.
Seed an identity column when you create it in Management Studio.
If you create the table using code, you can easily seed the table’s identity column using the Transact-SQL (T-SQL) CREATE TABLE statement in the following form:
CREATE TABLE tablename ( columnname datatype identity [(seed, increment) [NOT FOR REPLICATION]], [columnname ...] )
In the code, datatype is a numeric column. Both seed and increment are optional and the default value for both is 1.
Figure B shows the result of using CREATE TABLE to create a table named Orders and setting the OrderID column’s identity seed and increment values to 100 and 10, respectively. As you can see, the first identity value is 100 and each subsequent value increases by 10. (You can decrease identity values by specify a negative value for increment.)
Use CREATE TABLE to seed an identity column.
Checking and reseeding
For instance, if you copy all the table’s records to an archive table and then delete all the records in the source table, you might want to reseed the source table’s identity column, so you can control the sequence. Use T-SQL’s DBCC CHECKIDENT as follows to reseed an identity column:
DBCC CHECKIDENT ( tablename [, [NORESEED | RESEED [, newreseedvalue]]] ) [WITH NO_INFOMSGS]
Table A defines this statement’s optional parameters.
Table A: DBCC CHECKIDENT
|Returns the current identity value and the current maximum value of the identity column, without reseeding. These values are usually (and should be) the same.|
|Changes the current identity value, using the maximum value in the identity column, if the current identity value is less than the maximum identity value stored in the identity column.|
|Specifies the new seed value when reseeding. If the table is empty, the first identity value (after executing DBCC CHECKIDENT) will equal newreseedvalue. If the table contains data, the next identity value will equal newreseedvalue + the current increment value (the default is 1). This behavior is new to SQL Server 2005 (and remains in 2008). SQL Server 2000 always increments the seed value.|
WITH NO INFOMSGS
|Suppresses all informational messages.|
Technically, DBCC CHECKIDENT checks and corrects an identity value. Simply put, use it to learn the current identity value or to reseed an existing identity column.
Once a table’s in production, you might want to determine the seed value, as shown in Figure C. Executing DBCC CHECKIDENT with no argument other than the table’s name returns the column’s current identity value, which is the last value generated and stored in the table (see Figure B).
Return an identity column’s current and maximum values.
These two values will usually be the same. If they’re not, reset the identity value to avoid errors or gaps in the sequence of values.
SQL Server won’t execute a statement that duplicates existing identity values if the identity column is part of a primary key. Figure D shows what can happen when an identity column isn’t part of a primary key. In this case, DBCC CHECKIDENT reseeds OrderID and accepts a new record. However, the identity column generates a duplicate value. It’s important to remember that an identity column does not guarantee uniqueness.
What can happen when an identity column isn’t part of a primary key.
When the identity column is part of a primary key, SQL Server protects the validity of the existing data by returning a violation error, as shown in Figure E. Now, the question is, did SQL Server reset the seed value or not — it did. You can reseed the column to a value that has the potential to create a duplicate, but SQL Server won’t let you enter the record. That’s why you must be careful to check existing identity values before reseeding.
A violation error.
When reseeding identity values, keep the following behaviors in mind:
- Setting the newreseed value below existing values will eventually generate an error if the column is part of a primary key.
- Use the exact seed value when running DBCC CHECKIDENT against a new table that has never contained data. For instance, a newreseed value of 10 will generate an initial identity value 10.
- When resetting an empty table (after deleting records), the identity column will generate newreseed + 1. If the next identity value should be 1, use a newreseed value of 0.
- Deleting records from a table using DELETE doesn’t reset an identity column; using TRUNCATE TABLE does. TRUNCATE TABLE is usually faster than DELETE, but in this case, not necessarily the best choice for the job, unless you mean to reset the identity column.
Reseeding an identity column is a common task, but you must know the basics or you might get into trouble. Specifically, an identity column can generate duplicate values. Knowing when and why is the key to keeping an identity column in check.
Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is “Mastering Microsoft SQL Server 2005 Express,” with Mike Gunderloy, published by Sybex. Other collaborations with Mike Gunderloy are “Automating Microsoft Access 2003 with VBA,” “Upgrader’s Guide to Microsoft Office System 2003,” “ICDL Exam Cram 2,” and “Absolute Beginner’s Guide to Microsoft Access 2003″ all by Que. Currently, Susan volunteers as the Publications Director for Database Advisors at http://www.databaseadvisors.com. You can reach her at email@example.com.