SQLcommitted

Committed with SQL

SQL Server: 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 (SQL Server: Identity Property 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.

image

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

image

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

 

image

 

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

image

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

image

 

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

About these ads

December 4, 2013 - Posted by | Identity | , , , , ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 76 other followers

%d bloggers like this: