Identities Overview

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Code Management - Identities

The column property known as an identity is a monotonically increasing counter whose value is automatically placed in the column. The end-user need not provide the value and developers do not have to write any special code to create the values.

Identities are established using the CREATE TABLE statement. You may also place an identity column into a pre-existing table using the ALTER TABLE command. In either case, the identity property can be assigned to any column with an integer data type

When assigning identities to a table, the seed value is the integer indicating the starting value of the first identity inserted into the table (usually 1). The increment value is an integer indicating the amount of increase when every new row is inserted into the table. The default for both seed and increment is 1. But if you skip one, you must skip both when defining an identity column.

The following example creates a table called agency containing an identity column, agency_id. 

CREATE TABLE agency (agency_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, agency_name CHAR(50) NOT NULL, phone_number VARCHAR(23) NOT NULL)

You may use the ALTER TABLE statement to only ADD or DROP identity columns from the table: 

ALTER TABLE agency ADD (counter_column INT IDENTITY(100,10))

This statement adds a column, column_counter, to the agency table. The first record insterted with a value for column_counter will start at 100 and every subsequent row will increase by 10. 

Records that existed in the table prior to the execution of the ALTER TABLE command will be populated with an IDENTITY value when a new column is added. 

Note: The counter values are calculated off of the first index defined for the table, not for the primary key or for the clustered index as you might guess. 

Identity properties cannot be added to or removed from a pre-existing column. 

To programmatically change the identity property of an existing column without losing the column itself, you would have to create a new table with the appropriate constraints, transfer the data, drop the old table, and finally rename the new table with the appropriate column constraints. Fortunately, you can very easily modify the identity attributes of an existing column with a few simple clicks in Enterprise Manager. To drop an identity constraint in Enterprise Manager, right-click the table, select "Design Table", select the identity column, and choose "No" under the "Identity" property. SQL Server will do all the necessary work behind the scenes. The process is nearly identical to add an identity constraint using Enterprise Manager.

IDENTITY_INSERT

IDENTITY_INSERT allows you to insert specific values into an IDENTITY column. Basically, it temporarily turns off the IDENTITY constraint for the table. This can be useful for filling in rows that were deleted by mistake.

Note: Remember that the identity property of a column is in place for a reason. When you use IDENTITY_INSERT, you’re circumventing that restriction. Don’t use this lightly! 

CREATE TABLE table1 (id_col int identity(1,1), [name] varchar(100))

INSERT INTO table1 VALUES ('TV')
INSERT INTO table1 VALUES ('Movies')
INSERT INTO table1 VALUES ('Books')
INSERT INTO table1 VALUES ('Radio')

DELETE FROM table1 where [name] = 'Books'
SELECT * FROM table1
Returns
1 TV
2 Movies
4 Radio 
SET IDENTITY_INSERT table1 ON

INSERT table1 (id_col, [name]) VALUES (3,'Magazines')

SELECT * FROM table1
Returns
1 TV
2 Movies
3 Magazines
4 Radio

You might notice that the insert statement here contains the column list (id_col, [name]). This is required when inserting to a table when IDENTITY_INSERT is ON.


IDENTITY_INSERT is session specific; if you turn IDENTITY_INSERT ON (for example) in a Query Analyzer window, and try to insert an explicit value to the identity column in another window, you will get the error " Cannot insert explicit value for identity column in table 'table1' when IDENTITY_INSERT is set to OFF."

Note that only one table in a session can have IDENTITY_INSERT ON. And, if you insert a value larger than the biggest current identity value, SQL will pick it up from there automagically (after IDENTITY_INSERT is turned OFF): 

INSERT table1 (id_col, [name]) VALUES (45,'Books')
SET IDENTITY_INSERT table1 OFF
INSERT table1 ([name]) VALUES ('MP3 Player')

SELECT * FROM table1
Returns
1 TV
2 Movies
3 Magazines
4 Radio
45 Books
46 MP3 Player

Finally, here's a quick script to find gaps in the sequence of identity values for a column: 

SELECT A.id_col, B.id_col from table1 A 
LEFT OUTER JOIN table1 B ON A.Id_col + 1 = B.id_col
For our table above, this query will return
1 2
2 3
3 4
4 NULL
45 46
46 NULL

We can see that there are missing steps after identity values 4 and 46 (as 46 is the last item in the list). Be sure to change the + 1 to your increment value if you're not using an increment value of 1 in the identity column.

Author Credits

Jen McCown

Jennifer McCown contributed to this article.

Jen McCown is a SQL Server developer and DBA with over 10 years experience. She writes book and product reviews for ITBookworm.com, makes training videos and blogs for MidnightDBA.com, and maintains both websites.

Her online presences include:

Retrieved from "http://sqlserverpedia.com/wiki/Identities_Overview"