Common uses of IDENTITY in SQL

In recent project I have used Identity column significantly, would like to write a small blog as quick reference:

• Syntax:
create table myTable(id int identity(1,1), name varchar(60))

• Seed and Increment value can be negative, default value of Seed and Increment is 1

• IDENT_SEED, IDENT_INCR can be used to see the SEED and INCREMENT value

• Different ways to get the Identity value
o @@IDENTITY – Session Level
o SCOPE_IDENTITY – Scope Level
o IDENT_CURRENT – Table Level

• To Manually Insert Identity value use the Syntax

• DBCC command can be used to RESET the SEED

• Identity can be used in INSERT INTO Syntax

• Truncating the table will reset the SEED to Original value configured

• INSERT INTO automatically inserts IDENTITY property into new table

Example:

-- Table Creation Syntax
create table myTable(id int identity(-1,-2), name varchar(60)) -- Seed and Increment can be nagetive 
insert into myTable Values ('Vijay')
insert into myTable Values ('Vijay2')
insert into myTable Values ('Vijay3')
insert into myTable Values ('Vijay4')

-- Check ID Values
select * from myTable

-- How to Manually insert some values into Identity Column
SET IDENTITY_INSERT myTable ON
insert into myTable(ID,Name) Values (46,'Vijay46')
SET IDENTITY_INSERT myTable OFF

-- Check ID Values
select * from myTable

-- USE Identity in INSERT INTO 
SELECT IDENTITY(Int, 1,1) AS Col_ID, name INTO myTable2 
FROM myTable

-- RESEED (I use it when I delete the table content/ I had a situation, I can not truncate the table)
DBCC CHECKIDENT ('myTable', RESEED, 20);
insert into myTable Values ('Vijay6')
insert into myTable Values ('Vijay7')

select * from myTable
select * from myTable2

-- Check SEED and INCREMENT values 
select IDENT_SEED('myTable2')
select IDENT_INCR('myTable2')


drop table myTable
drop table myTable2
--
--
Advertisements
This entry was posted in knowledge, Productivity, SQL Server and tagged , , , , . Bookmark the permalink.

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