SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Puzzled by simple recovery model and transaction l
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/14/2007 :  13:46:31  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

DELETE v. TRUNCATE

Does TRUNCATE TABLE reset the IDENTITY seed (assuming you have an identity column)?

Can't remember, sorry!

Kristen



Yes it does.



CODO ERGO SUM
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37460 Posts

Posted - 02/14/2007 :  13:47:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
http://vyaskn.tripod.com/sql_server_check_identity_columns.htm

I always use DBCC CHECKIDENT though if I want to reseed it. I don't rely on my memory of which command resets it, so I just always script the DBCC CHECKIDENT with it.

Tara Kizer
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/14/2007 :  14:03:46  Show Profile  Reply with Quote
"I don't rely on my memory of which command resets it"

Well my memory clearly isn't any use!

I raised it in case the resetting of IDENTITY might make a difference if sql_er wanted the ID numbers to run-on, after deleting the old data, rather than resetting again.

Kristen
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 02/14/2007 :  17:23:38  Show Profile  Reply with Quote
Tkizer: What if a CHECKPOINT occurs during an open transaction of a db using simple recovery model? Can I still ROLL it back?

Jay: Why would you ever want to reset the identity field? What exactly does it achieve?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37460 Posts

Posted - 02/14/2007 :  17:33:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes. CHECKPOINT only affects those transactions that have completed either COMMIT TRAN or ROLLBACK TRAN.

Resetting the identity column would be used if you wanted to start at 1 again (or whatever value you started at). Perhaps you've loaded millions of rows of data into the table, but now you want to get rid of those. So you run truncate. The seed is now at 1 again (or whatever value was used in the CREATE TABLE statement). A reason to reset it is so that you don't run out of values. Of course I'd just use bigint for the identity column if I ever thought I'd need more than int could support.

Tara Kizer
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 02/14/2007 :  18:26:53  Show Profile  Reply with Quote
Great. That definitely clears up my misunderstandings about the db in simple recovery model and its behavior with the transaction log.

As for TRUNCATE vs DELETE, good to know that the IDENTITY value is reset in the TRUNCATE case.

Thanks a lot!

Edited by - sql_er on 02/14/2007 18:27:57
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000