Thursday, August 22, 2013

TSQL Interview Question: Identity column

Identity is the property of column which inserts incremental value in the column at the insert of new row in parent table of that column.
For example in Employee table when we set ID as identity then for every inserted employee record there will be  an auto incremental value get inserted in the ID column automatically.
For Identity you can set the increment value i.e. the value by which next inserted value increment. So if you set increment by 10 then inserted value will be like 10, 20, 30, 40……..

These properties can be set in for table while designing it or opening existing table in design mode.

Identity key doesn't guarantee
  • Uniqueness of the value
  • Consecutive values within a transaction – If there are multiple transaction then the identity value for every row can be differ in a single transaction.
  • Reuse of values – if there is error while inserting row that the identity generated will lost and next time SQL server will insert new identity value. It doesn't reuse it.
The seed value is the value inserted into an identity column for the very first row loaded into the table.

No comments:

Post a Comment