SQL 2005+

SQL Server Identity Columns (2)

The twelfth part of the SQL Server Programming Fundamentals tutorial describes identity columns. By applying an identity specification to a numeric column, the column's value is defaulted to a new value for every row inserted into the table.

Inserting Rows

Once the identity value has been applied, you can add new rows to the table using SSMS. Right-click the Contracts table in the Object Explorer and choose "Open Table" from the menu that appears. The table's twenty-one rows will be visible

Note that the numbers in the ContractNumber column are already using the values from one to twenty-one. These values were added manually by the JoBS database script. However, we can now add a new row to see the result of the identity column.

Move the cursor to the blank row at the bottom of the grid. Leave the first cell in this row blank, then add the following values to the remaining cells:

After typing all of the information, press the Tab key to move to the next row. You should see that the ContractNumber column is automatically populated. However, sometimes the column is not immediately updated in the grid and incorrectly displays "NULL". If this is the case, refresh the grid by pressing Ctrl-R. You will see that SQL Server has recognised that some of the identity range has already been used and has therefore set the contract number for the new row to twenty-two.

You can, of course, use Transact-SQL statements to insert data into the table. When creating new rows, you should not attempt to insert a value into the identity column, as this will cause an error.

Adding an Identity Specification Using Transact-SQL

In most cases you will determine the requirement for an identity column during the design of a table. When creating a table using T-SQL, the identity specification is simply added to the column specification, following the data type. The identity constraint syntax is as follows:

As an example, consider the following script. This creates the Contracts table and includes an identity specification on the ContractNumber column. The identity has a seed of one and an increment of one.

CREATE TABLE Contracts
(
    ContractNumber INT IDENTITY(1,1) NOT NULL,
    CustomerAddressId INT NOT NULL,
    RenewalDate SMALLDATETIME,
    RenewAutomatically BIT,
    ContractValue MONEY NOT NULL
    CONSTRAINT PK_Contracts PRIMARY KEY CLUSTERED 
    (
        ContractNumber
    )
)

In this case, it is acceptable to not include the seed and increment values. This specifies the default option of one for both the seed and increment values. The revised statement is as follows:

CREATE TABLE Contracts
(
    ContractNumber INT IDENTITY NOT NULL,
    CustomerAddressId INT NOT NULL,
    RenewalDate SMALLDATETIME,
    RenewAutomatically BIT,
    ContractValue MONEY NOT NULL
    CONSTRAINT PK_Contracts PRIMARY KEY CLUSTERED 
    (
        ContractNumber
    )
)