Tags

,


If you are working on the database for sometime now, then I am sure you felt the need to copy the contents of an entire table into another table. Right?

Well I can say in my case I have to copy the contents of an entire table into the other table very frequently for various reasons like archiving large tables or phase the data out.

Most of the time,developers like me will use SELECT INTO or INSERT INTO statements. Although these are the most common methods but I am going to show you that these are not the fastest methods available. Good News for us, right???


I have worked in many databases like Oracle, DB2 and MySQL as well but I am not aware of any functionality like this that exists (correct me if I am missing or overlooking any functionality), till then Great work Microsoft.
Basically this method is based on the new partitioning functionality of SQL Server 2005. In this blog I will be stressing on SWITCH partitioning feature. In SWITCH, the SQL Server does not actually move the data, it only updates the pointers to the data. So the movement of data occurs almost instantly irrespective of number of rows.
Now I will illustrate this point with an example.


— CHANGING THE DATABASE TO TEMPDB
USE tempdb

— CREATING THE SOURCE TABLE WHICH WILL HOLD THE DATA

CREATE TABLE [dbo].[DATA_2010]
(
Row_ID INT PRIMARY KEY,
Some_Data CHAR(1)
)

— INSERTING THE DATA IN THE SOURCE TABLE

DECLARE @row_count INT
SET @row_count=1
WHILE (@row_count<1000000)
BEGIN
INSERT INTO [DATA_2010] VALUES (@row_count,’X’)
SET @row_count=@row_count+1
END

— SELECT COUNT(*) FROM [DATA_2010] –999999 rows
— CREATING THE DESTINATION TABLE WHERE THE DATA FROM THE SOURCE TABLE WILL BE INSERTED.

CREATE TABLE [dbo].[NEW_DATA_2010]
(
Row_ID INT PRIMARY KEY,
Some_Data CHAR(1)
)

— NOW THE MOST INTERESTING AND THE TRICKY PART..HOLD YOUR BREATHE AND FASTEN YOUR SEAT BELT!!!!!
SET STATISTICS TIME ON
SET STATISTICS IO ON

ALTER TABLE [dbo].[DATA_2010] SWITCH TO [dbo].[NEW_DATA_2010]

SET STATISTICS TIME OFF
SET STATISTICS IO OFF
GO
/*
SQL Server Execution Times:
CPU Time = 0 ms, elapsed time = 0 ms
SQL Server Execution Times:
CPU Time = 0 ms, elapsed time = 1 ms
*/

To verify the result:
SELECT COUNT(*) FROM [dbo].[DATA_2010] — 0
SELECT COUNT(*) FROM [dbo].[NEW_DATA_2010] –999999

So we have successfully moved around 10 million rows of data into new table in 1 ms and with no IO, although IO has to be incurred to update the meta data but it should be minimal.

Criteria: There are some stipulation that have to met to use SWITCH. Some of the important and basic stipulations are mentioned below:

  1. Both tables must exist before the SWITCH operation.
  2. The Destination table has to be empty.
  3. Both the Source and the Destination table must have the same schema.
  4. Partitions must be on the same column.
  5. Source and Target tables must share the same FILEGROUP.

References: http://technet.microsoft.com/en-us/library/ms191160.aspx

I hope you enjoyed the trick and will apply this in your practical scenario. Till then enjoy!!!

Cheers,
Subhro Saha

Advertisements