Wednesday, April 5, 2017

How to determine if a column is an identity column in SQL server

In SQL Server, the column can be identified whether it is identity column or not using the built-in function COLUMNPROPERTY.


                                     (table_or_procedure_id int,
                                     column_name nvarchar(128),
                                     property_name varchar)

It returns an integer value 0 or 1. If value is 1, it is an identity column, if 0 it’s not.


SELECT COLUMNPROPERTY(OBJECT_ID('dbo.Person1'),'BusinessEntityID','IsIdentity')

In the below image, the column BusinessEntityID is not an identity column, hence the result is 0.

Now we made the column as identity column. Not the query returns value 1.

Share This
Previous Post
Next Post

TekGrabs, established in 2017, is in the business of sharing Information. It provides news, articles and photos about the different technologies, inventions in different domains. Provides information on programming and databases.