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.
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.
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.
This post explains the various restrictions SQL Server applies on the ROOT and INCREMENT attributes of an 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.
This post explains how to reset the SEED 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.
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.
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.
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.
@@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 are different. This post explains the differences.
This post summarizes all the functions, commands, variables and system catalog views related to IDENTITY columns.
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.