SQL Server Performance Forum – Threads Archive
Truncate vs Drop & Recreate Table
Hello, I am moving data from production to report server nightly. I am just wondering what is the best way to do it. Option 1:– Truncate Table
– Drop Indexes
– Insert data
– Recreate Indexes Option 2:
– Drop Table
– Select Into
– Create Indexes Or maybe there are any other better options? Thanks!
Option 1 is ideal option as far as I’m concerned.
If its a continued process then use a DTS job to accomplish the task. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Instead of using DTS, I created SP and use Linked Server i.e. ServerName.DBName.dbo.TableName. Is DTS better or Linked Server better? Thanks!
On performance basis DTS is better than Linked server approach.
Why not schedule a own job with a SP. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Yes, I schedule the SP to run nigntly. By the way, why is Option 1 better?
Its faster (I believe) compare to option 2 where you drop the table and do select into… _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>