Identity column after DELETE

Question

Identity column after DELETE

Airolg Icsadjag

SSC Eights!

Points: 961
More actions
November 23, 2005 at 1:33 am

#90924

hi!
i have a big question:
Is there any solution to reach that after delete all rows from table (DELETE FROM Table1) having an identity column called ID, the new first row's ID became 1 again?
i need another way than drop and create the table.
thanks for the answer!
bye,
G

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

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

David.Poole SSC Guru Points: 75923 More actions · Answer 1

There are two solutions and they require db_owner or db_DDLAdmin privileges.

If you want to DELETE FROM Table1 then use TRUNCATE TABLE Table1. Not only will this reset the Identity column but it will also be much faster as it is a minimally logged operation.

The other method is to use DBCC CHECKIDENT(Table1 , RESEED ,1)

LinkedIn Profile

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 2

WOW!!!

thanks, David, very much!

i choosed checkindent. it is what i need.

thanks again and have a nice day!

bye,

Gloria

david russell-253790 SSCarpal Tunnel Points: 4042 More actions · Answer 3

just remember that a truncate is an unlogged transaction and that it invalidates incremental backups. so, after a backup, if you really, really want to be backed up (and you should), you must take another one. truncate is not a really good solution to a lot of problems I see it applied.

Cheers,

david russell

david russell-253790 SSCarpal Tunnel Points: 4042 More actions · Answer 4

by the way... using 2005 here, and I have test data that I frequently load, delete after a partial update, and reload... and I use delete * from table... and the next row does NOT start with one as you say yours does. If I want it to start over again I have to (in the GUI) turn it off, save, turn it on, and save again before it starts over at 1.

Cheers,

david russell

SQLWannabe SSCrazy Points: 2842 More actions · Answer 5

DRussell, using DELETE FROM will not reset your uniqueidentifier count to 1, but using TRUNCATE TABLE will. I use it all the time and it IS a logged operation in 2000, but not in 7 or earlier.

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 6

Thanks Lance,

As i dont need to log i found that DBCC CHECKINDENT (TBL1, RESEED, 0) is a perfect solution to my problem, so i get used this method.

Ciao,Gloria

david russell-253790 SSCarpal Tunnel Points: 4042 More actions · Answer 7

The original poster said that delete was reseting the identifier and I said that it did not for me. As for whether truncate is logged, or not, I will stand corrected... for various sites with a google serach I find the following:

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

It was also referred to as being "minimally logged". Anyway, this seems to me to be pretty good news because I have known developers all of my life to use truncate - not just during development; but built into code. This means that backups were invalidated and the dba never knew.

thanks for the info.

Cheers,

david russell

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 8

Hi!

I have to mention that u cannot TRUNCATE TABLE if it is being referenced by a FOREIGN KEY constraint.

If u r in this situation use DELETE TABLE & DBCC CHECKINDENT. It works.

Have a nice day!

bye, Gloria

SQLWannabe SSCrazy Points: 2842 More actions · Answer 9

Airolq, I'm glad the community could help solve your problem. I also *think* that you have to have certain/higher rights/permissions to use the TRUNCATE command. DRussel, I never even thought about what would happen to the invalidated backups. Thanks for the tip. And I was referring to the ROLLBACK ability for the TRUNCATE command in 2000 and not so much the logging of the records.