Truncate or DBCC CHECKIDENT ?

[thamms]

Hi,

I have a table where I reset the identity field upon inserting new data. I truncate the old data before inserting to accomplish this.

However, I have read that using "truncate" invalidates the transaction log. I've never really figured out what this means and if it is a problem.

That being the case, is it better to use DBCC CHECKIDENT than truncate if I want to reset the identity field?

Thanks

 

[mrdenny]

Truncate doesn't invalidate the transaction log. While delete is a logged operation (read record which is deleted is written to the log so that it can be rolled back) the truncate operation isn't logged. Basically an entry is put into the transaction log saying that the table was truncated so that when the log is rolled forward any and all data in the table is wiped.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[thamms]

Ok thanks. But I have specifically read in places that it "invalidates the truncate log" but I guess it's more an issue of symantics. It never made sense to me that SQL Server would design a product in this way anyways, as if this were the case it would be a critical design problem.

 

[mrdenny]

It's devinetly a symentics issue then.

Breaking the transaction log is about the last thing that Microsoft would want to do. It would sort of defeate the purpose.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[SQLSister]

Basically truncate simply means you will not be able to rollback the change and recover if you do it in error. So the transaction log would not be broken, but for this particular data, it wouldn't be useful either. Other transactions would still be able to be rolled back. Other issues with Truncate: it will not work if you have foreign key constraints and it will not fire any triggers, so if you have auditing for instance, that will not happen.

Of more concern to me is that you want to reset identity fields. If you have any related tables (one not defined with a formal foreign key relationship), this would be a bad practice. You could end up with data that should be related to an old record that is now related to a differnt record. If this is a stand alone table or a one time truncate to get rid of test data, then forget I said this.

"NOTHING is more important in a database than integrity." ESquared

 

[ca8msm]

You should also have a read of Denis's blog:

http://sqlservercode.blogspot.com/2007/06/sql-myth-truncate-cannot-be-rolled-back.html

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[thamms]

The identity field is just used to determine which row (in a staging table) is next to another for our programmer's purposes; the rows change every day anyways, there's no fixed data to begin with.