Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 t-sql truncate table not good in production

Author  Topic 

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-12-16 : 14:24:15
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?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-12-16 : 14:32:48
There is no basis to categorically state that truncating a table is a bad idea. It depends on the nature of your data and what your goals are.

If you have a table with a million rows, and only two rows change each day, then truncating the table and repopulating the table just so you can capture the two changed rows into the table is not a good idea.

How you would update the table to capture changes depends on what your table structure is, and where your source data that is to be used to update the table is. A simple example is on MSDN page here: http://technet.microsoft.com/en-us/library/ms188724(v=sql.105).aspx
Go to Top of Page
   

- Advertisement -