{"id":92181,"date":"2021-08-23T17:34:39","date_gmt":"2021-08-23T17:34:39","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=92181"},"modified":"2026-10-08T15:53:25","modified_gmt":"2026-10-08T15:53:25","slug":"sql-server-identity-column","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/sql-server-identity-column\/","title":{"rendered":"SQL Server identity column"},"content":{"rendered":"\n

A SQL Server identity column is a numeric column that SQL Server populates automatically with an incrementing integer each time a row is inserted, defined with IDENTITY (seed, increment) in CREATE TABLE or added later with ALTER TABLE. A table can have one identity column; its values are not guaranteed unique or gap-free unless you add a key and understand how reseeding and rollbacks behave. <\/strong><\/p>\n\n\n\n

This article covers the basics: what an identity column is, how to define one, how to find the identity columns in a database, how to add or alter one on an existing table, and how to reseed it. Another guide here on Simple Talk covers manual inserts, the identity functions, and caching<\/a>.<\/strong><\/p>\n\n\n\n

When designing a table for a database, a column might need to be populated with a different number on every row inserted. An identity column might be a good way to automatically populate a numeric column each time a row is inserted. In this article, I will discuss what a SQL Server identity column is and how it works. For a deeper dive into manual overrides, scope functions, and performance, check out the advanced identity column article: IDENTITY_INSERT, @@IDENTITY, SCOPE_IDENTITY and caching.<\/a><\/p>\n\n\n\n

What is a SQL Server identity column?<\/h2>\n\n\n\n

An identity column is a numeric column in a table that is automatically populated with an integer value each time a row is inserted. Identity columns are often defined as integer columns, but they can also be declared as a bigint, smallint, tinyint, or numeric or decimal as long as the scale is 0. An identity column also can not be encrypted using a symmetric key, but can be encrypted using Transparent Data Encryption (TDE). Additionally an identity column\u2019s definitions must not allow null values. One possible drawback of using an identity column is that only one identity column per table can be used. If more than one numeric field must be populated automatically per table, consider looking at the sequence object, which is outside the scope of this article.<\/p>\n\n\n\n

The values automatically generated for each row inserted are based on the seed and an increment property of the identity column. The following syntax is used when defining an identity column:<\/p>\n\n\n\n

IDENTITY [ (seed , increment) ]<\/pre>\n\n\n\n
Seed is the first value loaded into the table, and increment is added to the previous identity value loaded to create the next subsequent value. Both seed <\/em>and increment <\/em>values need to be supplied together if you wish to override the defaults. If no seed<\/em> and increment<\/em> values are provided, then the default values for seed and increment are both 1.<\/p>\n\n\n\n
Defining identity column using a CREATE TABLE statement<\/h2>\n\n\n\n
When a table is designed, most data architects will create the layout, so the first column in the table is the identity column. In reality, this is only a standard practice and not a requirement of an identity column. Any column in a table can be an identity column, but there can only be one identity column per table. Script 1 creates a new table named Widget<\/em> that contains an identity column. For more on the INT\/BIGINT choice, see how to choose the right data types and column options<\/a>.<\/p>\n\n\n\n
Script 1: Creating a table with an identity column<\/strong><\/p>\n\n\n\n
CREATE TABLE Widget\n(\n    WidgetID int identity(1,1) not null,\n    WidgetName varchar(100) not null, \n    WidgetDesc varchar(200) not null\n);<\/pre>\n\n\n\n
The WidgetID<\/em> is the identity column with a seed value of 1 and an increment value of 1.<\/p>\n\n\n\n
The seed value determines the identity value for the first row inserted into a table. The increment value is used to determine the identity value of subsequent rows inserted into the table. For each row inserted after the first row, the increment value is added to the current identity value<\/em> to determine the identity value for the new row being added. The current identity value<\/em> is an integer value for the identity column of the last row inserted into the table. To see how this works, run Script 2.<\/p>\n\n\n\n
Script 2: Code to insert and display three rows added to table Widget<\/strong><\/p>\n\n\n\n
INSERT INTO Widget VALUES \n('thingamajig','A jig you cannot remember'),\n('doodad','A hair style you cannot remember'), \n('whatchamacallit', 'A thing for which you cannot remember');\nSELECT * FROM Widget;<\/pre>\n\n\n\n
When the code in Script 2 runs, the output in Report 1 is displayed.<\/p>\n\n\n\n
Report 1: Output when Script 2 is run<\/strong><\/p>\n\n\n\n
\"An<\/figure>\n\n\n\n
<\/p>\n\n\n\n
In Script 2, three rows are inserted into the newly created Widget <\/em>table. My script only provided column values for the WidgetName<\/code>, and WidgetDesc<\/code> columns and didn\u2019t provide values for the WidgetID<\/code> column. The value for the WidgetID<\/code> column for the first row inserted was based on the seed defined in the CREATE TABLE<\/code> statement, which was identified in Script 1. The WidgetID<\/code> value 2 for the row with a WidgetName<\/code> of doodad<\/em> was created by adding the increment value of 1 to the last identity value inserted. The WidgetID<\/code> value of 3, for the row with whatchamacallit.<\/em> WidgetName<\/code>, got its identity value by adding 1 to the identity value used on the second row insert.<\/p>\n\n\n\n
Remember that the seed and increment values do not have to be 1 and 1, respectively; they could be whatever values are appropriate for the table. For example, a table could use a seed value of 1000 and an increment of 10, as I have done for the WidgetID<\/code> column.<\/p>\n\n\n\n
Script 3: Using different seed and increment value <\/strong><\/p>\n\n\n\n
CREATE TABLE DifferentSeedIncrement\n(\n    ID int identity(1000,10),\n    A varchar(100), \n    B varchar(200)\n);<\/pre>\n\n\n\n
The ID<\/code> column in Script 3 doesn\u2019t have the NOT<\/code> NULL<\/code> property identified, in the CREATE<\/code> TABLE<\/code> statement, as I had done with the identity column defined in Script 1. The not null column requirement can be left off because, behind the scenes, the database engine will automatically add the NOT<\/code> NULL<\/code> property for any identity column being created.<\/p>\n\n\n\n
I\u2019ll leave it up to you to run the code in Script 3 and insert a few rows in the DifferentSeedIncrement<\/em> table. This way, you can see for yourself that the ID<\/code> values generated for each new row are inserted into the DifferentSeedIncrement<\/em> table and how the table is defined to SQL Server.<\/p>\n\n\n\n
Uniqueness of an identity column<\/h2>\n\n\n\n
Creating an identity column on a table doesn\u2019t mean an identity value will be unique. The reason identity column values might not be unique is that SQL Server allows identity values to be manually inserted, as well the seed value can be reset. I will be covering both the inserting identity values and resetting the seed value concepts in a follow up article. The SQL Server documentation clearly states that uniqueness must be enforced by using a primary key, unique constraint, or unique index. Therefore, to guarantee that an identity column only contains unique values, one of the aforementioned objects must force uniqueness for each value in an identity column. See also SQL Server sequence objects<\/a>, the alternative when you need more than one generator per table.<\/p>\n\n\n\n
Identifying identity columns and their definitions in database<\/h2>\n\n\n\n
There are a number of ways to identify the identity columns and their definitions in a database. One way is to use SQL Server Object Explorer, however, the identity column can\u2019t be determined by just displaying the columns in a table, as shown in Figure 1<\/p>\n\n\n\n
\n
\"An<\/figure>
<\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Figure 1: Displaying column definitions for tables created by Script 1 and Script 3<\/strong><\/p>\n\n\n\n
To determine which column is actually an identity column, the column’s properties need to be reviewed. To show the properties of a column, right-click on the column in Object<\/em> Explorer<\/em> and then click on the Properties<\/em> item from the drop-down context menu. Figure 2 shows the properties of the WidgetID<\/code> column in the Widget<\/em> table.<\/p>\n\n\n\n
\"An<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Figure 2: Properties the dbo.Widget.WidgetId <\/em>column<\/strong><\/p>\n\n\n\n
If the Identity<\/em> property has a value of True<\/em>, then the column is an identity column. The seed and increment values are also displayed.<\/p>\n\n\n\n
Using the Object Explorer properties method in a database with lots of tables might take a while to determine which columns are identity columns. Another method to display all the identity columns in a database is to use the sys.identity_column <\/em>view, as shown in the TSQL code in Script 4.<\/p>\n\n\n\n
Script 4: Script to display all identity values in a database<\/strong><\/p>\n\n\n\n
SELECT \n\tOBJECT_SCHEMA_NAME(tables.object_id, db_id())\n\tAS SchemaName,\n\ttables.name As TableName,\n\tidentity_columns.name as ColumnName,\n\tidentity_columns.seed_value,\n\tidentity_columns.increment_value,\n\tidentity_columns.last_value\nFROM sys.tables tables \n\tJOIN sys.identity_columns identity_columns \nON tables.object_id=identity_columns.object_id\nGO<\/pre>\n\n\n\n
Script 4 returns the output in Report 2.<\/p>\n\n\n\n
Report 2: Output when script 4 is run<\/strong><\/p>\n\n\n\n
\"An<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Note that the last_value<\/code> column for the TableName <\/em>of DifferentSeedIncrement<\/em> has a value of NULL<\/code>. This means no rows have been inserted into this table to be able to set the LastValue.<\/em><\/p>\n\n\n\n
Adding an identity column to an existing table<\/h2>\n\n\n\n
An existing column cannot be altered to make it an identity column, but a new identity column can be added to an existing table. To show how this can be accomplished, run the code in Script 5. This script creates a new table, adds two rows, and then alters the table to add a new identity column.<\/p>\n\n\n\n
Script 5: Adding an identity column<\/strong><\/p>\n\n\n\n
CREATE TABLE Invoices\n(\n    InvoiceDate date, \n    InvoiceNumber varchar(100),\n    PayTo varchar (100)\n);\nINSERT INTO Invoices VALUES\n(getdate(), 'GL_0001', 'Greg Larsen'),\n(getdate(), 'GL_0002', 'Greg Larsen');\n-- Add Identity Column\nALTER TABLE Invoices  \n   ADD InvoiceID int identity;\n-- Review Rows\nSELECT * FROM Invoices;<\/pre>\n\n\n\n
The output of Script 5 is shown in Report 3.<\/p>\n\n\n\n
Report 3: Rows in Invoices table<\/strong><\/p>\n\n\n\n
\"An<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Report 3 shows that the new InvoiceID<\/code> column was added, the identity values for this column were automatically populated on all existing rows. For more on this, see the basics of inserting data into a SQL Server table.<\/a><\/p>\n\n\n\n
Altering an existing table to define an identity column<\/h2>\n\n\n\n
As already stated, SQL Server does not allow using the ALTER TABLE\/ALTER COLUMN<\/code> command to change an existing column into an identity column directly. However, there are options to modify an existing table column to be an identity column. The following example shows an option that uses a work table to alter a column in an existing table to be an identity column.<\/p>\n\n\n\n
To accomplish modifying an existing column to be an identity column, the script uses the ALTER TABLE \u2026 SWITCH<\/code> command. The SWITCH<\/code> option was added to the ALTER TABLE<\/code> statement in SQL Server 2005 as part of the partitioning feature. The TSQL code in Script 6 uses a temporary work table and the SWITCH<\/code> option to support altering an existing column to make it an identity column.<\/p>\n\n\n\n
Script 6: Altering an existing column to be an identity column<\/strong><\/p>\n\n\n\n
DROP TABLE Invoices -- clean up from prior example\nGO\n-- Step 1: Create Invoices table and populate with data\nCREATE TABLE Invoices\n(   \n    InvoiceID int NOT NULL, \n    InvoiceDate date, \n    InvoiceNumber varchar(100),\n    PayTo varchar (100)\n);\nINSERT INTO Invoices VALUES\n(1, getdate(), 'GL_0001', 'Greg Larsen'),\n(2, getdate(), 'GL_0003', 'Greg Larsen');\n-- Step 2: create temporary work table with same schema, but has identity column\nCREATE TABLE Invoices2\n(   \n    InvoiceID int identity(1,1), \n    InvoiceDate date, \n    InvoiceNumber varchar(100),\n    PayTo varchar (100)\n);\n-- Step 3: Switch Tables, drop original, and rename\n ALTER TABLE Invoices SWITCH TO Invoices2;\n -- drop original table\n DROP TABLE Invoices;\n -- Rename temp table back to original table name\n EXEC sp_rename 'Invoices2','Invoices';  \n-- Step 4: Update the current seed value for new Invoices table\n DBCC CHECKIDENT('Invoices');<\/pre>\n\n\n\n
Script 6 went through 4 steps to alter an existing column to be an identity column. Below are some things to consider when using this method to add an identity column to an existing table:<\/p>\n\n\n\n
To use the SWITCH<\/code> option on the ALTER<\/code> TABLE<\/code> statement, the column being changed to an identity column on the original table must not allow nulls. If it allows null, then the switch operations will fail.<\/p>\n\n\n\n
Make sure to reseed the identity column of the new table using the DBCC<\/code> CHECKIDENT<\/code> command. If this is not done, then the next row inserted will use the original seed value, and duplicate identity values could be created if there is not a primary key, or unique constraint, or unique index on the identity column.<\/p>\n\n\n\n
All foreign keys will need to be dropped prior to running the ALTER TABLE \u2026SWITCH<\/code> command.<\/p>\n\n\n\n
If indexes exist on the original table, then the temporary table will also need the exact same indexes, or the switch operation will fail.<\/p>\n\n\n\n
While the ALTER TABLE \u2026SWITCH<\/code> command is running, there must be no transactions running against the table. All new transactions will be prevented from starting while the switch operation is being performed.<\/p>\n\n\n\n
When switching tables, security permissions could be lost because the security permissions are associated with the target table when a switch operation is performed. Therefore, make sure permissions of the original table are recreated on the target table either before or shortly after the switch operation.<\/p>\n\n\n\n
Reseeding an identity column<\/h2>\n\n\n\n
In the previous example, I reseeded the identity column value by using the DBCC CHECKIDENT<\/code> statement. There are other reasons why an identity column value might need to be reseeded, like when several rows were incorrectly inserted into a table, or erroneous rows were deleted. Mistakenly inserting erroneous rows causes the current identity to be increased for each row added. Therefore, after all the bad rows have been deleted, the next row will use the next identity value and leave a large gap in identity values. If this mistake has been made, then reseeding the identity value ensures there isn\u2019t a big gap of missing identity values.<\/p>\n\n\n\n
The DBCC CHECKIDENT<\/code> command is used to reseed an identity value for a table. This command has the following syntax:<\/p>\n\n\n\n
 DBCC CHECKIDENT\n (\n    table_name  \n        [, { NORESEED | { RESEED [, new_reseed_value ] } } ]  \n)  \n[ WITH NO_INFOMSGS ]  <\/pre>\n\n\n\n
The table_name<\/em> parameter is the name of the table that contains an identity specification. The table must contain an identity column, otherwise, an error will occur when the DBCC CHECKINDENT<\/code> command is run. If no other options are added along with this command, then the current identity value will be reset to the maximum value found in the existing identity column.<\/p>\n\n\n\n
The NORESEED<\/code> option specifies not to change the seed value. This option is useful to determine the current and maximum identity value. If the current and maximum values are different, then the identity value should be reseeded.<\/p>\n\n\n\n
When the current identity value is less than the maximum, or there is a large gap in the identity values, the RESEED<\/code> option can be used to reset the current identity value. The RESEED<\/code> option can be specified with or without a new_reseed_value<\/em>. When no new_reseed_value<\/code> is specified, the current identity value will be set to the maximum value stored in the identity column of the table specified.<\/p>\n\n\n\n
Script 6 shows how to reseed an identity column value using DBCC<\/code> CHECKINDENT<\/code> command without using the RESEED<\/code> option. The TSQL code in script 7 shows how to set the current seed value to 2 using the RESEED<\/code> option.<\/p>\n\n\n\n
Script 7: Using the RESEED option<\/strong><\/p>\n\n\n\n
DBCC CHECKIDENT('Invoices',RESEED,2);<\/pre>\n\n\n\n
Be careful with using the RESEED<\/code> option with a new seed value. SQL Server doesn\u2019t care what value is used for the new seed. If the new seed is set to a value less than the maximum seed value in the table, duplicate identity values might be created.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
An identity column will automatically generate and populate a numeric column value each time a new row is inserted into a table. The identity column uses the current seed value along with an increment value to generate a new identity value for each row inserted. This article only covered some fundamental aspects of using the identity column. In a future article<\/a>, more nuances of the identity columns will be explored.<\/p>\n\n\n\n
If you liked this article, you might also like Improving performance with instant file initialization.<\/a><\/em><\/p>\n\n\n\n
 <\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Fast, reliable and consistent SQL Server development…<\/h3>\n                
\n                                            …with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
\n    
FAQs<\/h2>\n\n                        
1. What is an identity column in SQL Server?<\/h3>\n            
\n                
A column defined with the IDENTITY property that SQL Server fills automatically with an incrementing number – by default starting at 1 and increasing by 1 – each time a row is inserted. It is usually an INT or BIGINT and is commonly used as a surrogate primary key, but the property alone does not make the column a key or guarantee uniqueness.<\/p>\n            <\/div>\n                    
2. How do you add an identity column to an existing table?<\/h3>\n            
\n                
You cannot add the IDENTITY property to an existing column. Either add a new identity column with ALTER TABLE … ADD ColumnName INT IDENTITY(1,1), or create a new table with the identity column, copy the data across with IDENTITY_INSERT on, and rename. The article shows both.<\/p>\n            <\/div>\n                    
3. How do you reseed an identity column?<\/h3>\n            
\n                
Use DBCC CHECKIDENT (‘TableName’, RESEED, new_value) to set the current identity value; the next insert uses new_value plus the increment. Reseeding below existing values can produce duplicates if the column is not constrained by a key.<\/p>\n            <\/div>\n                    
4. Should a primary key be an identity column, a GUID, or a natural key?<\/h3>\n            
\n                
It depends on the table. An identity column gives a small, sequential, cheap key that clusters well; a GUID (uniqueidentifier) is unique across servers but four times wider and random, which fragments clustered indexes unless you use NEWSEQUENTIALID; a natural key uses real-world data and needs no surrogate but is fragile if that data changes. Most transactional tables use an identity or sequence surrogate with a unique constraint on the natural key. For more on this, see effective clustered indexes.<\/a><\/span><\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
What a SQL Server identity column is and how it works: defining one in CREATE TABLE, uniqueness, finding them, adding, altering and reseeding.…<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[53,143525],"tags":[5134,145453],"coauthors":[11330],"class_list":["post-92181","post","type-post","status-publish","format-standard","hentry","category-featured","category-learn","tag-sql-prompt","tag-sql-server-identity-column"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92181","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=92181"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92181\/revisions"}],"predecessor-version":[{"id":113553,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92181\/revisions\/113553"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=92181"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=92181"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=92181"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=92181"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}