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.
"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.
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.
Puzzled by simple recovery model and transaction l
Posted - 02/14/2007 : 13:46:31
