Reset Identity In SQL Server

Reset  Identity In SQL Server

• Truncate command is used to reset identity  in sql server.
  
• It is not always useful for the all scenarios .

Eg. When table has set any kind of relationship with other table.

Like Foreign key

       Eg:

Crate a table:

GO

/****** Object:  Table [dbo].[TestTable1]    Script Date: 12/19/2012 17:06:30 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[EMPTable](

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

            [name] [varchar](50) NULL,

            [address] [varchar](50) NULL,

 CONSTRAINT [PK_EMPTable] PRIMARY KEY CLUSTERED

(

            EMPid ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

insert into [EMPTable] ( name,address)

VALUES ('abc','Mumbai')

insert into [EMPTable] ( name,address)

VALUES ('PQR','Mumbai')

insert into [EMPTable] ( name,address)

VALUES ('XYZ','Mumbai')

Delete One of the record

Delete FROM [EMPTable] WHERE  [EMPid] = 3

While creating a table EMPTable I have set identity column incremental

Now :

DBCC CHECKIDENT('[EMPTable]', RESEED, 2)

insert into [EMPTable] ( name,address)

VALUES ('sandeep','Mumbai')

You can see data with id = 3

Another Part of the Description contains insert data Manually in identity column

This can be achieved by

Synax

set IDENTITY_INSERT  <TableName> ON

Query :

set IDENTITY_INSERT [EMPTable] ON

insert into [EMPTable] (EMPid,name,address)

VALUES  (10,  'sandeep','Mumbai')

• You can see data can be inserted in identity column with EmpID = 10 ,by executing above query
• To auto increment identity column you have to execute below query

set IDENTITY_INSERT  <TableName> OFF

Query :

set IDENTITY_INSERT [EMPTable] OFF