How to reset identity count in sql tables?

Question

Post reply

How to reset identity count in sql tables?

joel_langlois

Old Hand

Points: 375
More actions
November 25, 2008 at 5:02 pm

#196636

Hi Folks,
I have a table with an identity column that increments by 1 per record…
I’ve been adding records and removing records and I noticed that when I remove a record the identity count remains the same…
For example if I add a record and the identity column is 5…. And then I remove that record the identity column becomes 6.
So, the identity count is cumulative….
( I remove records by deleting them in SQL Management Studio Express )
I’d like to reset the identity column/count to 0 when I’m done doing the testing.
(Because if I don’t…my first record will be number “14564564”!..)
How can I reset the identity count?
Regards,
- Joel

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Drop and recreate the table.

(You can use SSMS to generate a sql script for the table)

That said, the identity column is really there for uniqueness and the actual numbers shouldn't matter.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

TRUNCATE TABLE will do it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

joel_langlois Old Hand Points: 375 More actions · Answer 3

Thank you very much,

"Truncate Table" is what I was looking for... This clears the table and resets the identity colums...

For anybody who looks at this post and who don't know about "Truncate Table"

I found this link to help me using "Truncate Table" for the first time.

http://www.1keydata.com/sql/sqltruncate.html

Nandy Ten Centuries Points: 1016 More actions · Answer 4

Hi,

However I have a small solution to reseed the identity column from the number you want. I have done many times like this and I got the solution. May you can try this way as well.

eg:

Yaks: Column Name

You can give number from where you want to start your Identity column value.

DBCC CHECKIDENT('Yaks', RESEED, 7)

Cheers,

Nandy

krayknot SSCertifiable Points: 7998 More actions · Answer 5

joel_langlois (11/25/2008)
Hi Folks,
I have a table with an identity column that increments by 1 per record…
I’ve been adding records and removing records and I noticed that when I remove a record the identity count remains the same…
For example if I add a record and the identity column is 5…. And then I remove that record the identity column becomes 6.
So, the identity count is cumulative….
( I remove records by deleting them in SQL Management Studio Express )
I’d like to reset the identity column/count to 0 when I’m done doing the testing.
(Because if I don’t…my first record will be number “14564564”!..)
How can I reset the identity count?
Regards,
- Joel

Why to truncate the table.

To check the next ID value of Orders table, use this command

DBCC CHECKIDENT (orders, NORESEED)

To set the value of the next ID to be 1000, use this command:

DBCC CHECKIDENT (orders, RESEED, 999)

Note that the next value will be whatever you reseed with + 1, so in this case I set it to 999 so that the next value will be 1000

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

CHECKIDENT works fine, but the OP said he's deleting all rows from the table anyway, so TRUNCATE

TABLE meets both needs.

Greg

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

My thoughts exactly.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 8

good point.

Just case someone wants to Use CHECKIDENT.

Other method of doing it is to

DBCC CHECKIDENT(mytable,reseed,0)

--seed it to 0 first

DBCC CHECKIDENT(mytable,reseed)

--This will automatically reseed to the next correct seed value.

Using both statements means you don't have to look up the seed value first.

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life