Identity Columns
-
The IDENTITY columns
are auto incrementing columns provided by SQL Server.
-
There can only be
one IDENTITY column per table.
-
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.