BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Identity Columns

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.

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:

IDENTITY(seed, increment)

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
    )
)

Adding an Identity Specification to an Existing Table

Sometimes you will want to add an identity specification to an existing table. This is the case for the JoBS database as all of the tables already exist and are populated with data. Unfortunately there is no method for achieving this using Transact-SQL. To add such a constraint you must recreate the table and copy all of the existing data into it.

When adding an identity using SSMS, the script generated by the utility actually performs a series of six steps:

  • A new table is created. The name of the table is the same as that being modified except that it has a prefix of "Tmp_", for example "Tmp_Contracts". This table has the new identity specification applied.
  • A command is issued that permits explicit values to be inserted into the identity column.
  • All of the data from the old table is copied into the new table.
  • A command is issued that stops explicit values being inserted into the identity column.
  • The old table is dropped from the database.
  • The new table is renamed to match the name of the dropped table.

As you may imagine, this is a reasonably complex script. To avoid the possibility of mistakes and lost data, it is advisable to only add identities to existing tables using the table designer.

JoBS Database Modifications

Using the table designer, add the following identity columns to existing tables in the JoBS database.

TableColumnSeedIncrement
CustomerAddressesCustomerNumber11
CustomerComplaintsComplaintReference11
CustomersCustomerNumber11
EngineersEngineersId11
StandardJobsStandardJobId11
10 September 2008