Tags

, ,


We can use DBCC CHECKIDENT to reset the identity value for a table. I will illustrate this with a simple example:

I am creating a small table with Identity value:

CREATE TABLE tblCheckIdent
(
id INT IDENTITY (1,1),
FName VARCHAR(10) DEFAULT(‘Smith’)
)

— Now inserting three demo records in the table created above:

INSERT INTO tblCheckIdent (Fname) VALUES (DEFAULT)
INSERT INTO tblCheckIdent (Fname) VALUES (DEFAULT)
INSERT INTO tblCheckIdent (Fname) VALUES (DEFAULT)

— Selecting the records

SELECT * FROM tblCheckIdent 

Now suppose I want to have the Identity as 10 for the next record, for that we need to run the following TSQL script:

DBCC CHECKIDENT (‘dbo.tblCheckIdent’,reseed,9)

— Inserting the next row.

INSERT INTO tblCheckIdent (Fname) VALUES (DEFAULT)

— Selecting records from the table.

Permission Needed for the Command:

Caller must be the owner of  the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Thanks,

Subhro Saha

Advertisements