how to stop identity - SQL Server Forums

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 Programming

how to stop identity

Author

Topic

soori457
Yak Posting Veteran

85 Posts

Posted - 2009-06-03 : 07:36:50

Hai All

I created a table with identity(1,1) column(id). I inserted 100 rows in it, and deleted all the rows in the table. Now If I insert one row, then id(column) should start with again from 1 onward, but not with 101

Any one help me, how to do this...

Thanks in Advance

Suresh Kumar

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 07:47:38

If you use truncate table instead of delete the identity will be reset.

No, you're never too old to Yak'n'Roll if you're too young to die.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-03 : 08:20:38

If u delete all the rows in a table and if u want to reset the idenitiy value . use this

Dbcc Checkident('TableName',Reseed,0)

After deleting the rows , Execute the above statement twice

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 12:16:37

also please remember that for truncating the table there should not be any foreign keys refering the table.

soori457
Yak Posting Veteran

85 Posts

Posted - 2009-06-03 : 23:41:41

Thanks Raky

your Query is working fine

Suresh Kumar

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-04 : 05:43:44

Welcome...