Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Problem with identity

Author  Topic 

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-07 : 08:41:10
hi everyone,
I have some columns in the tables with identity propert
set to increment by 1.but iam unable to figure out why the order starts from 0 when ever i truncate the data in the tables and insert the data again.
please help me

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-07 : 08:52:04
Check what you have set for the seed property in the identity column.
It should be 1 to start with identity 1.

Vaibhav T

If I cant go back, I want to go fast...
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-07 : 09:05:45
quote:
Originally posted by vaibhavktiwari83

Check what you have set for the seed property in the identity column.
It should be 1 to start with identity 1.

Vaibhav T

If I cant go back, I want to go fast...


It is set to start with 1
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-07 : 10:13:24
To expand on what vaibhav was saying:
First run this query
select IDENT_SEED ('YourTableName');
If that shows 1 as the identity seed, check in your scripts where you are doing the truncation to see if it is reseeding the table with a new identity value after the truncation. It would look something like this:
DBCC CHECKIDENT ( 'YourTableName', RESEED, 0 )
If that doesn't seem to be the case, check whether the insert statements that result in a zero value in the identity column are explicitly inserted with zero for the value in the identity column. If that is the case, you would see a statement to set IDENTITY_INSERT option to ON.
Go to Top of Page
   

- Advertisement -