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 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.<\/p>\n Script 1: Creating a table with an identity column<\/strong><\/p>\n The WidgetID<\/em> is the identity column with a seed value of 1 and an increment value of 1.<\/p>\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 Script 2: Code to insert and display three rows added to table Widget<\/strong><\/p>\n When the code in Script 2 runs, the output in Report 1 is displayed.<\/p>\n Report 1: Output when Script 2 is run<\/strong><\/p>\n In Script 2, three rows are inserted into the newly created Widget <\/em>table. My script only provided column values for the 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 Script 3: Using different seed and increment value <\/strong><\/p>\n The 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 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.<\/p>\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 Figure 1: Displaying column definitions for tables created by Script 1 and Script 3<\/p>\n <\/strong> 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 Figure 2: Properties the dbo.Widget.WidgetId <\/em>column<\/strong><\/p>\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 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 Script 4: Script to display all identity values in a database<\/strong><\/p>\n Script 4 returns the output in Report 2.<\/p>\n Report 2: Output when script 4 is run<\/strong><\/p>\n Note that the 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 Script 5: Adding an identity column<\/strong><\/p>\n The output of Script 5 is shown in Report 3.<\/p>\n Report 3: Rows in Invoices table<\/strong><\/p>\n Report 3 shows that the new As already stated, SQL Server does not allow using the To accomplish modifying an existing column to be an identity column, the script uses the Script 6: Altering an existing column to be an identity column<\/strong><\/p>\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 To use the Make sure to reseed the identity column of the new table using the All foreign keys will need to be dropped prior to running the 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 While the 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 In the previous example, I reseeded the identity column value by using the The 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 The When the current identity value is less than the maximum, or there is a large gap in the identity values, the Script 6 shows how to reseed an identity column value using Script 7: Using the RESEED option<\/strong><\/p>\n Be careful with using the 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>\nDefining identity column using a CREATE TABLE statement<\/h2>\n
CREATE TABLE Widget\r\n(\r\n WidgetID int identity(1,1) not null,\r\n WidgetName varchar(100) not null, \r\n WidgetDesc varchar(200) not null\r\n);<\/pre>\n
INSERT INTO Widget VALUES \r\n('thingamajig','A jig you cannot remember'),\r\n('doodad','A hair style you cannot remember'), \r\n('whatchamacallit', 'A thing for which you cannot remember');\r\nSELECT * FROM Widget;<\/pre>\n![\"An](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/08\/text-description-automatically-generated.png\")
<\/p>\nWidgetName<\/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>\nWidgetID<\/code> column.<\/p>\nCREATE TABLE DifferentSeedIncrement\r\n(\r\n ID int identity(1000,10),\r\n A varchar(100), \r\n B varchar(200)\r\n);<\/pre>\n
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>\nID<\/code> values generated for each new row are inserted into the DifferentSeedIncrement<\/em> table and how the table is defined to SQL Server.<\/p>\nUniqueness of an identity column<\/h2>\n
Identifying identity columns and their definitions in database<\/h2>\n
\n\n
\n \n ![\"An](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/08\/word-image-2.png\")
<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nWidgetID<\/code> column in the Widget<\/em> table.<\/p>\n![\"An](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/08\/word-image-3.png\")
<\/p>\nSELECT \r\n\tOBJECT_SCHEMA_NAME(tables.object_id, db_id())\r\n\tAS SchemaName,\r\n\ttables.name As TableName,\r\n\tidentity_columns.name as ColumnName,\r\n\tidentity_columns.seed_value,\r\n\tidentity_columns.increment_value,\r\n\tidentity_columns.last_value\r\nFROM sys.tables tables \r\n\tJOIN sys.identity_columns identity_columns \r\nON tables.object_id=identity_columns.object_id\r\nGO<\/pre>\n
![\"An](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/08\/graphical-user-interface-text-description-automa.png\")
<\/p>\nlast_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>\nAdding an identity column to an existing table<\/h2>\n
CREATE TABLE Invoices\r\n(\r\n InvoiceDate date, \r\n InvoiceNumber varchar(100),\r\n PayTo varchar (100)\r\n);\r\nINSERT INTO Invoices VALUES\r\n(getdate(), 'GL_0001', 'Greg Larsen'),\r\n(getdate(), 'GL_0002', 'Greg Larsen');\r\n-- Add Identity Column\r\nALTER TABLE Invoices \r\n ADD InvoiceID int identity;\r\n-- Review Rows\r\nSELECT * FROM Invoices;<\/pre>\n
![\"An](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/08\/word-image-4.png\")
<\/p>\nInvoiceID<\/code> column was added, the identity values for this column were automatically populated on all existing rows.<\/p>\nAltering an existing table to define an identity column<\/h2>\n
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>\nALTER 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>\nDROP TABLE Invoices -- clean up from prior example\r\nGO\r\n-- Step 1: Create Invoices table and populate with data\r\nCREATE TABLE Invoices\r\n( \r\n InvoiceID int NOT NULL, \r\n InvoiceDate date, \r\n InvoiceNumber varchar(100),\r\n PayTo varchar (100)\r\n);\r\nINSERT INTO Invoices VALUES\r\n(1, getdate(), 'GL_0001', 'Greg Larsen'),\r\n(2, getdate(), 'GL_0003', 'Greg Larsen');\r\n-- Step 2: create temporary work table with same schema, but has identity column\r\nCREATE TABLE Invoices2\r\n( \r\n InvoiceID int identity(1,1), \r\n InvoiceDate date, \r\n InvoiceNumber varchar(100),\r\n PayTo varchar (100)\r\n);\r\n-- Step 3: Switch Tables, drop original, and rename\r\n ALTER TABLE Invoices SWITCH TO Invoices2;\r\n -- drop original table\r\n DROP TABLE Invoices;\r\n -- Rename temp table back to original table name\r\n EXEC sp_rename 'Invoices2','Invoices'; \r\n-- Step 4: Update the current seed value for new Invoices table\r\n DBCC CHECKIDENT('Invoices');<\/pre>\nSWITCH<\/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>\nDBCC<\/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>\nALTER TABLE \u2026SWITCH<\/code> command.<\/p>\nALTER 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>\nReseeding an identity column<\/h2>\n
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>\nDBCC CHECKIDENT<\/code> command is used to reseed an identity value for a table. This command has the following syntax:<\/p>\n DBCC CHECKIDENT\r\n (\r\n table_name \r\n [, { NORESEED | { RESEED [, new_reseed_value ] } } ] \r\n) \r\n[ WITH NO_INFOMSGS ] <\/pre>\nDBCC 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>\nNORESEED<\/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>\nRESEED<\/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>\nDBCC<\/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>\nDBCC CHECKIDENT('Invoices',RESEED,2);<\/pre>\nRESEED<\/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>\nThe SQL Server identity column<\/h2>\n