In this blog series, I’m covering all about Identity property. This is the second part of this series. You can read the first part here (Part – 1).

After reading this post you will know 5 facts about Identity column.

Fact 1– By default both Identity seed and increment value will set to 1.

Run the above script  and open design view of Employee table, you can find seed and increment value set to 1 for both.

indentity-property-part-2

Fact 2- Keyword IDENTITYCOL automatically refers to the specific column in a table that has identity property defined.

indentity-property-part-2-1

Note: If there is no column defined as Identity then querying IDENTITYCOL will throw an error.

Fact 3– Function IDENT_SEED and IDENT_INCR are used to find the seed and increment value respectively. It returns NULL if identity property not defined for a table.

Syntax: IDENT_SEED(‘Table Name’)

indentity-property-part-2-2

Fact 4– Identity property can’t be defined on a nullable column.

indentity-property-part-2-3

Fact 5– Only one identity column allowed per table

indentity-property-part-2-4