December 16, 2014 at 12:24 pm
I am working with a a sql server 2012 database. There are some production stored procedures that run daily that truncate tables and recreate the same tables in the same stored procedure. In my company, I am told that truncating a table is not a good idea. The better solution is to only update the tables with the changes that are made that day.
Thus I have the following questions:
1. Can you tell me why the truncate table is not the preferred method to execute in production daily?
2. Can you show me some code and/or point me to a url(link) where only changes for the day are updated in the tables that currently are updated in production?
December 16, 2014 at 12:57 pm
Truncate and reloading a table vs running a differential load daily are just two different kinds of jobs. There's no answer to why one is always better than the other and without knowing more about your particular set up and needs there's no way to say which is better in your case.
Why you would do a truncate then recreate the table I don't know, just the truncate should be enough.
December 16, 2014 at 1:11 pm
Use the right tool for the job. Sometimes that means truncating and (re)loading from scratch is best. Sometimes you don't KNOW what changes to apply so you have to do the above. Sometimes you do know and doing the appropriate INSERT/DELETE/UPDATE operations on existing data is best.
My question is why do the powers-that-be say that "truncating a table is not good"? What are their justification(s) for that statement?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 16, 2014 at 4:31 pm
Truncating tables is a great idea when applicable. It's vastly less logging overhead than deleting all the rows instead. Dropping the table and recreating is also more overhead than simply truncating the table.
In short, "truncating tables is a bad idea" is just another db myth that keeps getting passed around. Kind of like the myth "an identity should be the default clustered key on tables". Once such myths get started, they can be hard to correct.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply