Switching data in and out of a SQL Server 2005 data partition

By:   |   Updated: 2008-01-07   |   Comments (17)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Partitioning


Problem

One of the challenges of working with large datasets or datasets that become stale is the need to move large chunks of data in and out of your tables.  This can be done with large INSERT and DELETE statements or by using views, but with SQL Server 2005 data partitioning makes this task much easier to manage than in previous versions of SQL Server.  In this tip we will take a look at how to use the SWITCH operator to move data in and out of partitions.

Solution

In a previous tip, Handling Large Tables with Data Partitioning we looked at how to setup data partitioning in SQL Server 2005, but we did not discuss how to move data in and out of existing partitions.  In this tip we will look at a few examples of how to use the SWITCH operator to manipulate data within your partitions.

To begin with let's setup a simple example.  The first set of code creates a partition function, partition scheme and then applies the partition scheme to the new table "partTable".

-- create partition function
CREATE PARTITION FUNCTION partRange1 (INT)
AS RANGE LEFT FOR VALUES (10, 20, 30) ;
GO

-- create partition scheme
CREATE PARTITION SCHEME partScheme1
AS PARTITION partRange1
ALL TO ([PRIMARY]) ;
GO

-- create table that uses this partitioning scheme
CREATE TABLE partTable (col1 INT, col2 VARCHAR(20))
ON partScheme1 (col1) ;
GO

Now that this is setup we can run the following command to get a look at how our partition has been setup.

SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('partTable')

switch1

Now that we have our partitioned table setup, let's insert a few sample rows of data and then select the data to show what is in the table.

-- insert some sample data
INSERT INTO partTable (col1, col2) VALUES (5, 'partTable')
INSERT INTO partTable (col1, col2) VALUES (6, 'partTable')
INSERT INTO partTable (col1, col2) VALUES (7, 'partTable')

-- select the data
SELECT * FROM partTable

switch2

 

Switch Data In
The first process we will do is to switch data into a partitioned table from another table.  Do to this we created a new table called "newPartTable" which has the same schema as our partitioned table, but we also need to create a CHECK constraint on "col1" to match how this data will be loaded into our partition.  The data that will be loaded into this table will eventually be going into partition 4 of our partitioned table.  So because of this we make sure that our CHECK constraint matches how the partition function was setup.  In addition we need to specify that this value can not be NULL.  This is all specified in this line below:

col1 INT CHECK (col1 > 30 AND col1 <= 40 AND col1 IS NOT NULL

After the table has been created we are inserting some sample records and then just selecting back what was inserted.

-- switch in
CREATE TABLE newPartTable (col1 INT CHECK (col1 > 30 AND col1 <= 40 AND col1 IS NOT NULL), 
col2 VARCHAR(20))
GO

-- insert some sample data into new table
INSERT INTO newPartTable (col1, col2) VALUES (31, 'newPartTable')
INSERT INTO newPartTable (col1, col2) VALUES (32, 'newPartTable')
INSERT INTO newPartTable (col1, col2) VALUES (33, 'newPartTable')

-- select the data
SELECT * FROM partTable
SELECT * FROM newPartTable

Here we can see we now have data in both partTable and newPartTable.

switch3

The next set of commands actually does the switch. We are using the ALTER TABLE command to say take the data in newPartTable and move this data to partition 4 in partTable.  The reason we are specifying partition 4 is because of the values we inserted into col1 and these need to match the partition function that was setup in the first setup of this process.  After the switch is made we select the data to see that everything has moved from newPartTable to partTable.  In addition, although the data has moved the newPartTable still exists.  This table did not get absorbed in the process, just the data was moved.

-- make the switch
ALTER TABLE newPartTable SWITCH TO partTable PARTITION 4;
GO

-- select the data
SELECT * FROM partTable
SELECT * FROM newPartTable

switch4

 

Switching Data Out
On the reverse of the above, there may be the need to move data out of your partitioned table into another table in your database. Let's take a look at how to do this.  First we create a new table called "nonPartTable" with the same schema as above.  Notice that the columns only have the basis information we do not need to create a CHECK constraint for this process.

Once the table is created we again use the ALTER TABLE statement to switch the data in partition 1 from "partTable" to the new table "nonPartTable".  Data in partition 1 will be any records that have a "col1" value less than 10. 

After the move we are selecting the data.

-- switch out
CREATE TABLE nonPartTable (col1 INT, col2 VARCHAR(20))
ON [primary] ;
GO

-- make the switch
ALTER TABLE partTable SWITCH PARTITION 1 TO nonPartTable ;
GO

-- select the data
SELECT * FROM partTable
SELECT * FROM nonPartTable

As you can see we now have data in partTable and data in nonPartTable. 

switch5

I hope this gives you an idea of how helpful data partitioning can be to move sets of data and maintain large datasets.  Although this is very simple on the outside there are several things that need to be thought about before implementing a data partitioning scheme.

Next Steps
  • If you have not already explored using Data Partitioning take the time to see if this new feature is something that could be helpful in your environment.
  • Keep in mind that Data Partitioning only exists in the Enterprise and Developer editions of SQL Server 2005


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2008-01-07

Comments For This Article




Monday, August 1, 2016 - 9:46:04 PM - Shub Back To Top (43024)

 I am currently using partition switching using two way switch method using 3 tables. The steps involved are: 

1. Populate only the changed or delta data in a table in switch schema.   

2. Switch only partiton numbers that have received the changed data from table in dbo schema (which is the main table) to swap schema.

3. Switch partitions out from table in switch schema to dbo schema.

4. Truncate data in swap schema

This enables me to keep the table online without affecting the data while the data is being loaded. 

I am a bit confused in terms of indexing requirements. After reading few posts, it became clear that the source and the target tables involved in the partiton switching should have the same indexes on same columns. However while running the below code, I can see that the table in the swap schema doesnt need to have the same index as the table in dbo schema. Can someone throw some light on why is this so?

CREATE

 

PARTITION FUNCTION partRange1 (INT)

 

 

 

 

AS

 

RANGE LEFT FOR VALUES (10, 20, 30) ;

 

 

 

 

GO

 

 

 

 

 

 

-- create partition scheme

 

 

 

CREATE

 

PARTITION SCHEME partScheme1

 

 

 

 

AS

 

PARTITION partRange1

 

 

 

 

ALL

 

TO ([PRIMARY]) ;

 

 

 

 

GO

 

 

 

 

 

 

-- create table that uses this partitioning scheme

 

 

 

CREATE

 

TABLE partTable (col1 INT, col2 VARCHAR(20))

 

 

 

 

ON

 

partScheme1 (col1) ;

 

 

 

 

GO

 

 

 

 

 

 

CREATE

 

COLUMNSTORE INDEX CLIX_IN ON dbo.partTable (col1,col2)

 

 

 

 

-- insert some sample data

 

 

 

ALTER

 

INDEX CLIX_IN ON dbo.partTable DISABLE

 

 

 

 

INSERT

 

INTO partTable (col1, col2) VALUES (5, 'partTable')

 

 

 

 

INSERT

 

INTO partTable (col1, col2) VALUES (6, 'partTable')

 

 

 

 

INSERT

 

INTO partTable (col1, col2) VALUES (7, 'partTable')

 

 

 

 

ALTER

 

INDEX CLIX_IN ON dbo.partTable REBUILD

 

 

 

 

-- select the data

 

 

 

SELECT

 

* FROM partTable

 

 

 

 

-- switch in

 

 

 

CREATE

 

TABLE switch_partition.partTable (col1 INT,col2 VARCHAR(20)) ON partScheme1 (col1) ;

 

 

 

 

GO

 

 

 

 

 

 

CREATE

 

COLUMNSTORE INDEX CLIX_IN ON switch_partition.partTable (col1,col2)

 

 

 

 

-- insert some sample data into new table

 

 

 

ALTER

 

 

INDEX CLIX_IN ON switch_partition.partTable DISABLE

 

 

 

 

INSERT

 

INTO switch_partition.partTable (col1, col2) VALUES (35, 'PartTable_IN')

 

 

 

 

INSERT

 

INTO switch_partition.partTable (col1, col2) VALUES (36, 'PartTable_IN')

 

 

 

 

INSERT

 

INTO switch_partition.partTable (col1, col2) VALUES (37, 'PartTable_IN')

 

 

 

 

INSERT

 

INTO switch_partition.partTable (col1, col2) VALUES (38, 'PartTable_IN')

 

 

 

 

ALTER

 

INDEX CLIX_IN ON switch_partition.partTable REBUILD

 

 

 

 

--switch out

 

 

 

CREATE

 

TABLE swap.partTable (col1 INT,col2 VARCHAR(20)) ON partScheme1 (col1) ;

 

 

 

 

GO

--Stored proc which does the partition switching 

 

EXEC

 

 

dbo.sp_switch_partitions 'partTable'

 


 

 

Wednesday, January 20, 2016 - 1:33:28 PM - darlove Back To Top (40456)

Hint to All: If you plan on splitting a partition (usually the first one or the last one), please MAKE SURE the partition is empty (no data in there). If you do, you will be able to split seamlessly without having to drop any indexes or any other structures. This is very important to stress---keep the partitions at the extremes EMPTY. I think even Microsoft guidelines for implementing the 'sliding window scenario' point this out. This hint should answer some of the questions people have asked...

Thursday, April 30, 2015 - 3:52:31 PM - kyle Back To Top (37076)

What about data that is related to that you are switching out and is enfourced through contraints?

Monday, April 14, 2014 - 5:10:51 PM - Vikas MS Back To Top (30065)

Hi All,

I want to use Switch partition to truncate the old data. As of now i have very less free space in that drive. So i want to know whether the Swicth partition requires additional space.  

Sunday, October 27, 2013 - 11:34:44 AM - Abhishek Back To Top (27291)

Hello Greg,

 

Is there any way in SQL Server 2008 to truncate (something similar to what truncate does but only on the specific partition) a specific partition.

Thanks for your help.

Friday, September 6, 2013 - 12:58:20 AM - Gabriel Back To Top (26638)

Hello Greg,

 

I was able to fix the problem, some Columnstores werre not aligned with the partitions.

 

Thanks

Thursday, September 5, 2013 - 9:07:33 AM - Greg Robidoux Back To Top (26617)

Hi Gabriel,

I have not had a chance to do this yet, so not sure.

I did find this video demo that is switching data in and out.  This does not look like you need to disable the columnstore index.

http://channel9.msdn.com/posts/SQL11UPD02-REC-02

Thursday, September 5, 2013 - 5:19:10 AM - Gabriel Back To Top (26613)

Hello Greg,

We have setup our DB fact tables to use partitionning and also use Columnstores in SQL Server 2012 Enterprise...

The fact tables are partitionned using date boundaries and we add (SPLIT) the partitions dynamically every day.

The incoming daily data is uploaded into a work table wich is then smitched in the fact table to the new partition.

Before the the SPLIT and SWITCH, we disable the Columnstore on the fact table.

Now the problem we have is that it seems that Columnstores of all the fact tables that are partitionned must be disabled before the SPLIT and not only the Columnstore of the fact table we are switching...

With all Columnstores disabled before the SWITCH works perfectly, if not we get the usual error message :

 

SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.

 

Is that the correct behavior or am i missing something...

If it is the right behavior we are concerned about the time the REBUILD of all Columnstores would take

Thanks in advance.

Thursday, July 25, 2013 - 6:25:09 AM - Greg Robidoux Back To Top (25996)

@Dan - you are correct I didn't need to include the <=40. To be honest not sure why I included that in the constraint based on the values I entered, but as you mentioned it is not needed for this example.

Wednesday, July 24, 2013 - 6:47:29 PM - Dan Back To Top (25989)

Just wondering...why did you feel the need to include "AND col1 <= 40" when creating newPartTable?  As long as the value is greater than 30 everything is fine.

Thursday, April 25, 2013 - 6:50:53 AM - Mahdi Back To Top (23554)

Hi,

I have 2 tables :

partition table : GL_Transactions_part1

non partition table GL_Transactions_90_2

I want to assign this table as 6th partition of first table

i use this code

alter table GL_Transactions_90_2
add constraint checkEffectiveDate CHECK
 (Effective_FarsiDate >= '1390/07/01'
  and Effective_FarsiDate < '1391/01/01')
  
alter table dbo.GL_Transactions_90_2 switch to dbo.GL_Transactions_Part1 partition 6

 

but I received this erro:

Msg 4972, Level 16, State 1, Line 6
ALTER TABLE SWITCH statement failed. Check constraints or partition function of
source table 'GL_DAT.dbo.GL_Transactions_90_2' allows values that are not allowed by check
constraints or partition function on target table 'GL_DAT.dbo.GL_Transactions_Part1'.

would you please help me!!

Tuesday, April 23, 2013 - 10:10:54 AM - Greg Robidoux Back To Top (23518)

@arindam - no you cannot further define data within a partition using a WHERE clause.  When a paritition is setup the partition function and scheme determine the layout.

Tuesday, April 23, 2013 - 2:15:20 AM - Arindam Back To Top (23499)

Hi,

Can we switch a subset of data in a partition to a new non-partitioned table by using 'where clause' or something else? For example:

ALTER TABLE partTable SWITCH PARTITION 1 TO nonPartTable where col2='<some_value>'

Thursday, April 18, 2013 - 6:27:47 AM - iwanion Back To Top (23415)

 

Thanks a lot, this has helped a lot.  I wanted to remove data but not use delete command rather swap data out and just truncate it.

Wednesday, April 13, 2011 - 3:08:28 AM - Jason Back To Top (13603)

Nice article for new feature. In reality, you rarely have the relational data only in one table. You will have to switch all related data tables by the partition keys. If your partition design was not good (hey, typically done by a junior developer or a contract consultant), you can hardly move any records.

Tuesday, April 12, 2011 - 11:25:41 AM - Filipe Back To Top (13598)

One interesting thing to notice is the usually small remark that in order to make a SWITCH you'll need a very quick schema lock. That should not be an issue, but if the table is very frequently used you will not be able to get that lock and the SWITCH will not work. So if you think SWITCH, think maintenance window. If you do not have one, it won't be easy to make it work.

Friday, December 10, 2010 - 7:31:01 AM - Cletus Antony Raj Back To Top (10444)

Hi

Actually I have a requirement as below

1) To have a database in sql server 2008 with a set of tables (which will have the fields snapshotdate and frequency along with other data fields)
2) This set of tables should maintain only the latest 2 daily snapshot dates of data and latest 3 monthly snapshot dates of data and the older one should be removed everytime the new data comes in.

--> Please let me know the complete steps of how we can do it through partitions in SQL server 2008 which I require very urgently

Thanks,
Cletus

I














get free sql tips
agree to terms