Identity Columns

Introduction to Surrogate Keys

A surrogate key is a column whose values are provided, or generated, automatically, by the database engine. A surrogate key is used when there is no clear or justifiable way to use the values of a known column as the primary key. The values of a surrogate key are usually integers with no obvious or clear meaning. This also means that the values of a surrogate key mean nothing to the user and in fact the user doesn't have to know or care about them.

Microsoft SQL Server 2012 provides many ways to create a surrogate key. Two of the solutions are identify keys and sequences.

One of the goals of a good table is to be able to uniquely identity each record. In most cases, the database engine should not confuse two records. Consider the following table:

Imagine that you want to change the value of an item named "Long-sleeve jersey dress". Because you must find the item programmatically, you can start looking for an item with that name. This table happens to have two items with that name. You may then decide to look for an item using its category. In the Category column, there are too many items named "Women". In the same way, there are too many records that have a "Large" value in the Size column, same problem in the Unit Price column. This means that you don't have a good criterion you can use to isolate the record whose Item Name is Long-sleeve jersey dress.

To solve the problem of uniquely identifying a record, you can create a column whose main purpose is to distinguish one record from another. To assist you with this, the SQL allows you to create a column whose data type is an integer type but the user doesn't have to enter data for that column. A value would automatically be entered into the field when a new record is created. This type of column is called an identity column.

You cannot create an identity column on an existing table, only on a new table.

To create an identity column, if you are visually working in the design view of the table, in the top section, specify the name of the column. By tradition, the name of this column resembles that of the table but in singular. Also, by habit, the name of the column ends with id, _id, Id, or ID.

After specifying the name of the column, set its data type to an integer-based type. Usually, the data type used is int. In the bottom section, click and expand the Identity Specification property. The first action you should take is to set its (Is Identity) property from No to Yes.

Once you have set the value of the (Is Identity) property to Yes, the first time the user performs data entry, the value of the first record would be set to 1. This characteristic is controlled by the Identity Seed property. If you want the count to start to a value other than 1, specify it on this property.

After the (Is Identity) property has been set to Yes, the SQL interpreter would increment the value of each new record by 1, which is the default. This means that the first record would have a value of 1, the second would have a value of 2, and so on. This aspect is controlled by the Identity Increment property. If you want to increment by more than that, you can change the value of the Identity Increment property.

If you are programmatically creating a column, to indicate that it would be used as an identity column after its name and data type, type identity followed by parentheses. Between the parentheses, enter the seed value, followed by a comma, followed by the increment value. Here is an example:

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnCreateTable;
    
    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnCreateTable = new Button();
        btnCreateTable.Text = "Create Table";
        btnCreateTable.Width = 120;
        btnCreateTable.Location = new Point(12, 12);
        btnCreateTable.Click += new EventHandler(btnCreateTableClick);

        Text = "Exercise";
        Controls.Add(btnCreateTable);

        StartPosition = FormStartPosition.CenterScreen;
    }

    private void btnCreateTableClick(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdStoreItems =
                new SqlCommand("CREATE TABLE StoreItems" +
                               "(" +
                               "    ItemID int IDENTITY(1, 1) NOT NULL, " +
                               "    Category nvarchar(50), " +
                               "    [Item Name] nvarchar(100) NOT NULL, " +
                               "    Size varchar(20), " +
                               "    [Unit Price] money" +
                               ");",
                               cntExercise);
            cntExercise.Open();
            cmdStoreItems.ExecuteNonQuery();
        }
    }
}

public class Program
{
    [STAThread]
    static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

After creating an identity column, when performing data entry, don't specify a value for that column. Here is an example:

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnCreateRecord;
    
    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnCreateRecord = new Button();
        btnCreateRecord.Text = "Create Record";
        btnCreateRecord.Width = 120;
        btnCreateRecord.Location = new Point(12, 12);
        btnCreateRecord.Click += new EventHandler(btnCreateTableClick);

        Text = "Exercise";
        Controls.Add(btnCreateRecord);

        StartPosition = FormStartPosition.CenterScreen;
    }

    private void btnCreateTableClick(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdStoreItems =
                new SqlCommand("INSERT INTO StoreItems(Category, [Item Name], Size, [Unit Price]) " +
                               "VALUES(N'Men', N'Simplicity Leather Dress Oxfords', N'9', 65.85);",
                               cntExercise);
            cntExercise.Open();
            cmdStoreItems.ExecuteNonQuery();
        }
    }
}

public class Program
{
    [STAThread]
    static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

If you provide a value for the identity column, you would receive an error.

Creating a Value for an Identity Column

If you want to specify a value for the identity column, call the SET IDENTITY_INSERT flag. The formula it uses is:

SET IDENTITY_INSERT [ database_name.[schema_name].] table { ON | OFF }

The database_name is the optional name of the database that owns the table. If you previously use the USE statement, in most cases, you can omit the name of the database. The schema_name is the (optional) name of the schema in which the table was created. The table factor is the name of the table to which the identity column belongs. After specifying the name of the table, set the flag as ON to allow a value for the identity column, or OFF to disallow it.

If you decide to use the SET IDENTITY_INSERT flag, you must provide a list of columns after the name of the table in the INSERT or INSERT INTO statement. Here is an example:

USE Exercise1;
GO

SET IDENTITY_INSERT StoreItems ON;
GO
INSERT INTO StoreItems(ItemID, Category, [Item Name], Size, [Unit Price])
VALUES(10, N'Girls', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T', 18.95);
GO

This time, the data entry would not produce an error.

As mentioned already, after setting the IDENTITY_INSERT ON, you can put it back OFF and add a record without a value for the identity column. Here is an example:

USE Exercise1;
GO

SET IDENTITY_INSERT StoreItems ON;
GO
INSERT INTO StoreItems(ItemID, Category, [Item Name], Size, [Unit Price])
VALUES(50, N'Men', N'Simplicity Leather Dress Oxfords', N'10.5', 65.85);
GO

SET IDENTITY_INSERT StoreItems OFF;
GO

INSERT INTO StoreItems(Category, [Item Name], Size, [Unit Price])
VALUES(N'Women', N'Fiona High Heel Boot', N'6.50', 295.00);
GO
INSERT INTO StoreItems(Category, [Item Name], Size, [Unit Price])
VALUES(N'Women', N'Saddle East/West Leather Hobo Bag', N'Medium', 345.50);
GO

If you do this, the next value of the identity column would be the increment from the previous value.

As we know already, to get a list of values from a column, you can use a SELECT statement and give the name of the column. If a table is using an identity column, Transact-SQL provides the $IDENTITY flag that allows you to get the values of that column. Here is an example of using it:

USE Exercise;
GO
SELECT $IDENTITY FROM StoreItems;
GO