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.