x

Create Big Table

I have a table that is expecting to get 40M rows every day. This data is incremental, but with enough changes that warrant it to be treated like it is brand new.

The original idea was to DELETE-INSERT, but that filled up the Tx log.

The solutions were to TRUNCATE-INSERT or DROP-INSERT_INTO.

The current process drops and recreates the tables every day. Therefore, as data is added, pages are allocated.

Is there a better way for me to handle this than a DROP-INSERT_INTO?
more ▼

asked May 03 '11 at 05:56 AM in Default

Raj More gravatar image

Raj More
1.7k 77 82 84

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If you need to delate a large amount of old data and insert a lage amount of new data and have Enterprise version of SQL server, table partitioning could be an option.

  1. Insert data into a temp table
  2. Alter parititon function and partition scheme to add a new partition for the new data
  3. Switch the temp table into the new partition
  4. Switch out partition with old data into a temp table
  5. Drop/truncate a the temp table
  6. Alter the partition function and scheme to merge the space left after switching the partition out.

The partitioning possibility depends on your concrete needs and scenario, but you didn't provided much info about the process itself.

You can check MSDN for some details including The Data Loading Performance Guide and [Designing Partitioned Tables and Indexes][2].

[2]: http://msdn.microsoft.com/en-us/library/ms175533.aspx
more ▼

answered May 03 '11 at 11:25 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

(comments are locked)
10|1200 characters needed characters left

You could do the DELETE-INSERT in batches, with Tx log backups.

Other possibilities (depending on what dependencies hang off this table): insert into new table, rename old table, rename new table as old table, drop old table.
more ▼

answered May 03 '11 at 06:55 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

yeah, I'd go with this process or investigate partitioning. You can move massive amounts of data very fast simply by partition switching. MSDN - http://msdn.microsoft.com/en-us/library/cc966380.aspx and Technet - http://technet.microsoft.com/en-us/library/ms191160.aspx
May 03 '11 at 11:25 AM Fatherjack ♦♦
I do not have permissions to mess with the Tx logs.
Sep 01 '11 at 02:03 PM Raj More
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x562
x28

asked: May 03 '11 at 05:56 AM

Seen: 1305 times

Last Updated: May 03 '11 at 05:56 AM