Understanding the MS-SQL Server IDENTITY Property

You can apply the IDENTITY property to one (and only one) of the columns in a table to have MS-SQL Server supply an incrementing, non-NULL value for the column whenever a row is added that does not specify the column's value. Suppose, for example, that you wanted to create an EMPLOYEE table that included an EMPLOYEE_ID column, but you did not want to supply the EMPLOYEE_ID each time you added a new employee to the table. You can have MS-SQL Server supply the "next" EMPLOYEE_ID each time a row is added by creating the EMPLOYEE table using an SQL statement similar to the following:

 CREATE TABLE employee
  (id    INTEGER IDENTITY(10,10),
   name  VARCHAR(35),
   quota SMALLINT)

The format of the IDENTITY property is:

 IDENTITY (initial_value, increment)

If you omit the initial_value and increment, MS-SQL Server will set both the initial_value and the increment to 1.

The CREATE TABLE statement in the current example tells MS-SQL Server to assign a 10 to the ID column of the first row added to the EMPLOYEE table. Then, when you add subsequent rows to the table, MS-SQL Server will add 10 to the ID value in the last row of the table and assign that value to the ID column of the new row to be added. Thus, executing the SQL statements

INSERT INTO employee (name, quota)
 VALUES ('Sally Smith', NULL)
INSERT INTO employee (name, quota)
 VALUES ('Wally Wells', 5)
INSERT INTO employee (name, quota)
 VALUES ('Greg Jones', 7)
SELECT * FROM employee

MS-SQL Server will insert the three employee rows into the display and display them similar to the following:

id  name            quota
----------------------------
10  Sally Smith     NULL
20  Wally Wells     5
30  Greg Jones      7

You can apply the IDENTITY property only to columns of type INTEGER, INT, SMALLINT, TINYINT, DECIMAL, or NUMERIC—and only if the column does not permit NULL values.

Note

Specifying the IDENTITY property for a column does not guarantee that each row will have a unique value in that column. Suppose, for example, that you executed the SQL statements on the table in the current example: SET IDENTITY_INSERT employee ON INSERT INTO employee (id, name, quota) VALUES(20, 'Bruce Williams', NULL) SET IDENTITY_INSERT employee OFF INSERT INTO employee (name, quota) VALUES('Paul Harvey', 9) SELECT * FROM employee MS-SQL Server will display table rows similar to the following: id name quota ---------------------------- 10 Sally Smith (null) 20 Wally Wells 5 30 Greg Jones 7 20 Bruce Williams (null) 40 Paul Harvey 9 Because the first INSERT statement specifies the value for the ID column, the DBMS puts a 20 in the ID column of the Bruce Williams row. The second INSERT statement does not include a value for the ID column. As a result, the DBMS adds 10 (the increment) to the highest ID (30) and uses the result (40) as the ID for the new Paul Harvey row.

If you want to guarantee that the IDENTITY column contains a unique value in each row of the table, you must create a unique index based on the IDENTITY column, which you will learn how to do in Tip 161, "Using the CREATE INDEX Statement to Create an Index."