Identity Property Part – 2

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.

CREATE TABLE Employee (EMPID int IDENTITY, EMPNAME Varchar(50)
GO

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.

IF(Object_ID(‘Employee’) IS NOT NULL)
DROP TABLE Employee
GO
CREATE TABLE Employee (EMPID int IDENTITY, EMPNAME varchar(50))
GO
INSERT Employee (EMPNAME) VALUES (‘Sandip’)
GO
SELECT IDENTITYCOL FROM Employee
GO

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’)

IF(Object_ID(‘Employee’) IS NOT NULL)
DROP TABLE Employee
GO
CREATE TABLE Employee (EMPID int IDENTITY, EMPNAME varchar(50))
GO
SELECT IDENT_SEED(‘Employee’) AS SEED_VALUE, IDENT_INCR(‘Employee’) AS INCREMENT_VALUE
GO

indentity-property-part-2-2

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

IF(Object_ID(‘Employee’) IS NOT NULL)
DROP TABLE Employee
GO
CREATE TABLE Employee (EMPID int IDENTITY(1,1) NULL , EMPNAME varchar(50))
GO

indentity-property-part-2-3

Fact 5– Only one identity column allowed per table

IF(Object_ID(‘Employee’) IS NOT NULL)
DROP TABLE Employee
GO
CREATE TABLE Employee (SNO int IDENTITY(1,1) NULL,EMPID int identity(2,2), EMPNAME varchar(50))
GO

indentity-property-part-2-4

 

Avatar

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

Leave a Reply

Your email address will not be published. Required fields are marked *