February 18, 2011 at 2:44 am
after reseeding the identity column of a table it doesnt start with value 1 but it starts with 2
i am using the code as below, please help me
Employee
EmpIDbigintPK and Identity (1,1)
FirstNamenvarchar
LoginNamenvarchar
Passwordnvarchar
/*Disable Constraints & Triggers again*/
ALTER TABLE Employee NOCHECK CONSTRAINT ALL
ALTER TABLE Employee DISABLE TRIGGER ALL
/*Perform delete operation on all table for cleanup*/
DELETE Employee
-- I cant use truncate because many tables are related
/*Reset Identity on tables with identity column*/
IF OBJECTPROPERTY(OBJECT_ID('Employee'), 'TableHasIdentity') = 1
BEGIN DBCC CHECKIDENT ('Employee',RESEED,1) END
Insert into Employee(FirstName, LoginName, Password)
Values ('scott','scott','tiger')
Select * from Employee
will display as below
EmpidFirstNameLoginNamePassword
2scottscotttiger
but i want it to start with 1. I know if i do like this it will work, but i dont want to do
SET IDENTITY_INSERT [dbo].[Employee] ON
Insert into Employee(EmpID, FirstName, LoginName, Password)
Values (1, 'scott','scott','tiger')
SET IDENTITY_INSERT [dbo].[Employee] OFF
/*Enable Constraints & Triggers again*/
ALTER TABLE Employee CHECK CONSTRAINT ALL'
ALTER TABLE Employee ENABLE TRIGGER ALL'
February 18, 2011 at 2:48 am
This is expected behaviour , see example c here http://technet.microsoft.com/en-us/library/ms176057.aspx
February 18, 2011 at 2:52 am
If you want it to start at 1, reseed it to 0.
BEGIN DBCC CHECKIDENT ('Employee',RESEED,0) END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply