Identity Columns

  1. The IDENTITY columns are auto incrementing columns provided by SQL Server.

  2. There can only be one IDENTITY column per table.

  3. SQL Server will take care of incrementing this column automatically.

The following query gives you the list of all the identity column names in a database along with the table names

SELECT t.TABLE_NAME,c.COLUMN_NAME,c.TABLE_CATALOG,c.TABLE_SCHEMA FROM
INFORMATION_SCHEMA.COLUMNS AS c JOIN INFORMATION_SCHEMA.TABLES AS t
 ON t.TABLE_NAME = c.TABLE_NAME
WHERE COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME),c.COLUMN_NAME,'IsIdentity') = 1
 AND t.TABLE_TYPE = 'Base Table'
AND t.TABLE_NAME NOT LIKE 'dt%'
AND t.TABLE_NAME NOT LIKE 'MS%'
AND t.TABLE_NAME NOT LIKE 'syncobj_%'

If you are inserting data from some other source to a table with an identity column, you need to ensure you retain the identity values, you can temporarily allow inserts to the identity column. Without doing so, you will receive an error if you attempt to insert a value into the identity column.

To allow inserts into the identity column of a table, execute the following statement

set identity_insert <tablename> on

After we are done with insert on the table, we need to execute the following statement

set identity_insert <tablename> off

This is because only a single table in a session can have the identity_insert set to on. If you attempt to enable this for a table and another table already has this enabled, you will receive an error and will not be able to do so until you first turn this off for the other table.

While inserting the data into a table with identity column we also have to mention all the column names explicitly.