Pooja-462303 (4/29/2010)
Can you pls give me an instance/how the identity column can have duplicate.
Sure!
Using SET IDENTITY_INSERT ON:
CREATE TABLE Demo1
(Id int NOT NULL IDENTITY,
Other varchar(20)
);
INSERT INTO Demo1 (Other)
VALUES ('First');
SET IDENTITY_INSERT Demo1 ON;
INSERT INTO Demo1 (Id, Other)
VALUES (1, 'Another first?');
SELECT * FROM Demo1;
DROP TABLE Demo1;
Using DBCC CHECKIDENT with RESEED:
CREATE TABLE Demo2
(Id int NOT NULL IDENTITY,
Other varchar(20)
);
INSERT INTO Demo2 (Other)
VALUES ('First');
DBCC CHECKIDENT (Demo2, RESEED, 0);
INSERT INTO Demo2 (Other)
VALUES ('Another first?');
SELECT * FROM Demo2;
DROP TABLE Demo2;