move data
in [Server Configuration]
|
Prev: How to get list of string for particular condt
Next: One table with two date fields returning records in a date range
From: someone on 17 Jun 2010 11:21 Hi, I have a big table(20 GB), I want to archive the data and clean up the table to fast access. Is it this the better way to min downtime: select * into myBigTable20100617 from myBigTable truncate table myBigTable
From: Erland Sommarskog on 17 Jun 2010 17:32 (someone(a)js.com) writes: > Hi, I have a big table(20 GB), I want to archive the data and clean up the > table to fast access. > Is it this the better way to min downtime: > select * > into myBigTable20100617 > from myBigTable > truncate table myBigTable For this to be effiecient, you need to have the database in bulk_logged or simple recovery, or else SELECT INTO will be fully logged. But even faster would be to rename the existing table and create a new table. Just don't forget indexes, constraints and triggers. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: John Bell on 17 Jun 2010 17:40
On Thu, 17 Jun 2010 11:21:15 -0400, <someone(a)js.com> wrote: >Hi, I have a big table(20 GB), I want to archive the data and clean up the >table to fast access. >Is it this the better way to min downtime: >select * >into myBigTable20100617 >from myBigTable >truncate table myBigTable > > Assuming noone is trying to access the table you can rename (sp_rename) the old table and create a new one. You would need to set up FKs etc John |