Data Management

How do I... Reseed a SQL Server identity column?

Microsoft SQL Server’s identity column generates sequential values for new records using a seed value. Seeding and reseeding an identity column is easy and relatively safe, if you do it correctly. Susan Sales Harkins shows you how it works.

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.

About identity

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.

Seeding

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.

Figure A

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.)

Figure B

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

Parameter

Description

NORESEED

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.

RESEED

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.

newreseedvalue

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.

Checks

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).

Figure C

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.

Balances

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.

Figure D

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.

Figure E

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 basics

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 ssharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

10 comments
epsilonv
epsilonv

I had to execute the procedure twice for each table, first it set the current column and next time it set the current identity. I did it through Visual Studio though so that might have something to do with it.

DEK46656
DEK46656

I have used IDENTITY often (much to the disappointment of Joe Celko), and have one question. How would you fill in holes in an IDENTITY column? Say I have been using the column as a generic Primary Key generator for a BI application. Some old data gets deleted, foreign keys are also deleted, etc. Now I have holes that I would like to reuse; can reseed be used to address this somehow? I???m wondering if the error produced by attempting to use an IDENTITY value that already exist could be caught in a TRY / CATCH process and re-attempted somehow. Any idea?

medbiller
medbiller

I have a 16,449 rows table with a primary key which started seeded at 1 with increment of 1. I add 2 rows to make it 16,451. Then I delete these 2 last rows I added. dbcc checkident (table) yields, Checking identity information: current identity value '16451', current column value '16451'. I thought I should get current identity value '16451', current column value '16449'.

ssharkins
ssharkins

Personally, I wouldn't reuse deleted identity values. If some business rule requires a complete list of consecutive values you have a few choices: 1.) Rethink the business rule (my personal favorite). A lot of times these rules are unnecessary once you analyze their origin and use. 2.) Use a calculated column (not sure you can do this with a cc) or a sp to generate the numbers instead of using the identity property. 3.) Don't delete records. Flag them as deleted, but don't actually remove them. Accommodate them in your queries and views.

ssharkins
ssharkins

It's always great to hear such a nice response. I truly appreciate it. Thank you.

ssharkins
ssharkins

I apologize for the confusion because the terminology does imply what you've inferred. However, I checked with expert Francisco Tapia and he confirmed the following for me: CHECKIDENT returns the last identity value, even if it's no longer in the table -- so that's what I meant by "current value." He suggests you take a look at scope_identity, although, again, it won't tell you if a record's been deleted.

DEK46656
DEK46656

Thanks for the response. It sounds like IDENTITY is still not the answer / replacement compared to the various approaches that have been used historically in database design. With data Warehouses, keeping expired or "replaced" data is not viable when resources are a concern.

Editor's Picks