Feeds:
Posts
Comments

Posts Tagged ‘Difference between Identity and Truncate’

In case of truncate operation identity column value is reset to
N (IDENTITY (N, INR_BY)) but in

case of delete it will start from last inserted identity column value+1.

 

Alternatively ,If identity is set on column and current identity
col is 3

then if truncate and delete is done then for truncate identity
col will be 1 and for delete then it will be 4.

 

Run the below script to check value of identity column after delete
and truncate operations in SQL Server.

 

Case 1: Truncate

 

–Create table 

CREATE
TABLE [dbo].[I_Test](

           
[Cnt] [int] IDENTITY(1,1) NOT NULL,

           
[Name] [nvarchar](100)
NULL

)

 

 

–Insert values

INSERT
INTO [dbo].[I_Test]

VALUES(‘first’)

 

–Truncate table

TRUNCATE
TABLE [dbo].[I_Test]

 

–Insert values

INSERT
INTO [dbo].[I_Test]

VALUES(‘second’)

 

–Truncate table again

TRUNCATE
TABLE [dbo].[I_Test]

 

–Insert values

INSERT
INTO [dbo].[I_Test]

VALUES(‘third’)

 

 

–Truncate table again

TRUNCATE
TABLE [dbo].[I_Test]

 

 

–Insert values

INSERT
INTO [dbo].[I_Test]

VALUES(‘fourth’)

 

Now what is expected value of identity column in table!

 

Execute : SELECT * FROM I_Test

 

Identity column value is reset to N(Here N=1)

 

 

Result:

Cnt            Name

1             fourth    

 

 

 

Case 2: Delete

 

–Create table 

CREATE
TABLE [dbo].[I_Test](

           
[Cnt] [int] IDENTITY(1,1) NOT NULL,

           
[Name] [nvarchar](100)
NULL

)

 

–Insert values

INSERT
INTO [dbo].[I_Test]

VALUES(‘first’)

 

–Truncate table

DELETE
[dbo].[I_Test]

 

–Insert values

INSERT
INTO [dbo].[I_Test]

VALUES(‘second’)

 

–Truncate table again

DELETE 
[dbo].[I_Test]

 

–Insert values

INSERT
INTO [dbo].[I_Test]

VALUES(‘third’)

 

 

–Truncate table again

DELETE
[dbo].[I_Test]

 

 

–Insert values

INSERT
INTO [dbo].[I_Test]

VALUES(‘fourth’)

 

 

Now what is expected value of identity column in table!

 

Execute : SELECT * FROM I_Test

 

Value is last inserted identity column value+1

 

 

Result:

Cnt            Name

4             fourth    

 

 

Read Full Post »

%d bloggers like this: