I have written over a dozen articles covering almost all aspects of SQL Server IDENTITY columns. The intention of this page is to serve as an index, so that people can find the right post based on the specific piece of information they are looking for.

How do I create an Auto-number column in SQL Server?

This is the first article in the series. It provides some basic information about IDENTITY columns in SQL Server. It explains the basic syntax and demonstrates how to create IDENTITY columns with different SEED and INCREMENT values.

What are the data types supported in IDENTITY columns?

This post performs a deeper investigation into the different data types supported by IDENTITY columns. It also explains how to query the system catalog views to find out the identity column and their data type of each table.

How do I create an Auto-number (IDENTITY) column that counts backwards?

This post demonstrates how to create an IDENTITY column that counts backwards. This can be achieved by creating an IDENTITY column with a negative INCREMENT value. The post shows a few examples demonstrating this.

Restrictions on the SEED and INCREMENT values of an IDENTITY column

This post explains the various restrictions SQL Server applies on the ROOT and INCREMENT attributes of an IDENTITY column.

My IDENTITY values are not sequential! Why do I have missing numbers in the IDENTITY column?

This post intends to answer a very common question I hear from people around. Identity values are not expected to be sequential. The articles explores the different scenarios that can create missing IDENTITY values.

IDENTITY - How to change/reset the IDENTITY values?

This post explains how to reset the SEED value of an IDENTITY column.

How do I Insert an explicit value into an IDENTITY column? How do I Update the value of an IDENTITY Column?

This post explains how to insert explicit values to an IDENTITY column. Further, it demonstrates a few other points related to explicit IDENTITY insert.

Why do I have duplicate identity values?

IDENTITY values are not guaranteed to be unique, unless you have a unique index on the column. This post demonstrates a few cases that can result in duplicate IDENTITY values.

How to find out the SEED and INCREMENT values of an IDENTITY column?

This post explains how to retrieve the SEED and INCREMENT attributes of an IDENTITY column. It also explains how to retrieve the CURRENT identity value of the column as well.

@@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT()

This post compares @@IDENTITY variable with SCOPE_IDENTITY() and IDENT_CURRENT() functions. It explains the characteristics as well as differences of each one of them.

How to retrieve the identity values generated by a multi-row insert?

@@IDENTITY and SCOPE_IDENTITY() retrieves the last IDENTITY value generated in the current session and scope. If the operation inserts more than one row, they will give you only the last value. This article explains how to retrieve the IDENTITY values inserted by a multi-row insert.

IDENTITY property and IDENTITY() function

IDENTITY property and IDENTITY() function are different. This post explains the differences.

IDENTITY related functions, commands, system variables, catalog views

This post summarizes all the functions, commands, variables and system catalog views related to IDENTITY columns.

A few more interesting facts

I made this post to add a few interesting points that I missed in the previous posts. It points out that you cannot change the INCREMENT value of an IDENTITY column. It also looks into the differences between NUMERIC and DECIMAL data types.