Identity Columns

An IDENTITY column is a column that automatically gets it’s value set by the database engine when a new record is added. This is one of the oldest ways Microsoft SQL has of making sure a record is unique. Even if a user were to insert the same record twice, the IDENTITY will always be UNIQUE.

To create an IDENTITY column in Transact-SQL, after the name and data type of the column, add IDENTITY followed by parentheses. Between the parentheses, enter the seed value, followed by a comma, then the increment value. Here’s an example:

CREATE TABLE person(
      personID INT IDENTITY(1, 1) NOT NULL
    , firstName VARCHAR(50) NOT NULL
    , lastName VARCHAR(50) NOT NULL
)

While simple, this shows exactl how to set up your table to use IDs starting at 1, and incrementing by one each time a new record is inserted.

You could add an IDENTITY column to your table after the fact too!

ALTER TABLE person ADD personID INT IDENTITY(1, 1) NOT NULL

This will work, so long as the column personID didn’t already exist. If it did, you’d have to alter the column definition. But beware, doing this can have some serious issues. If the data type isn’t compatible (read: not an INT), or if there is data in there already (read: make sure you know what data is in there already, and set your seed to be an integer GREATER than you already have).

ALTER TABLE person ALTER COLUMN personID INT IDENTITY(1, 1) NOT NULL

Usually rather than trying to alter a column, you’ll find I create a backup of the existing table, drop the original table. Then script recreating the table with the ID I want, then select the data back into the newly created table, allowing the IDENTITY to do its thing.

Much easier than other options, if I do say so myself.

If you have any questions, let me know! I’m here to help!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *