Skip to content

Table Partitioning in SQL Server – Partition Switching

This post is part 2 of 2 in the series Table Partitioning in SQL Server
Partition Switching

Inserts, updates and deletes on large tables can be very slow and expensive, cause locking and blocking, and even fill up the transaction log. One of the main benefits of table partitioning is that you can speed up loading and archiving of data by using partition switching.

Partition switching moves entire partitions between tables almost instantly. It is extremely fast because it is a metadata-only operation that updates the location of the data, no data is physically moved. New data can be loaded to separate tables and then switched in, old data can be switched out to separate tables and then archived or purged. All data preparation and manipulation can be done in separate tables without affecting the partitioned table.

Partition Switching Requirements

There are always two tables involved in partition switching. Data is switched from a source table to a target table. The target table (or target partition) must always be empty.

(The first time I heard about partition switching, I thought it meant “partition swapping“. I thought it was possible to swap two partitions that both contained data. This is currently not possible, but I hope it will change in a future SQL Server version.)

Partition switching is easy – as long as the source and target tables meet all the requirements :) There are many requirements, but the most important to remember are:

  • The source and target tables (or partitions) must have identical columns, indexes and use the same partition column
  • The source and target tables (or partitions) must exist on the same filegroup
  • The target table (or partition) must be empty

If all the requirements are not met, SQL Server is happy to tell you exactly what went wrong and provides detailed and informative error messages. Some of the most common examples are listed near the end of this blog post.

Partition Switching Examples

Partitions are switched by using the ALTER TABLE SWITCH statement. You ALTER the source table (or partition) and SWITCH to the target table (or partition). There are four ways to use the ALTER TABLE SWITCH statement:

  1. Switch from a non-partitioned table to another non-partitioned table
  2. Load data by switching in: Switch from a non-partitioned table to a partition in a partitioned table
  3. Archive data by switching out: Switch from a partition in a partitioned table to a non-partitioned table
  4. Switch from a partition in a partitioned table to a partition in another partitioned table

The following examples use code from the previous Table Partitioning Basics blog post. It is important to notice that these examples are meant to demonstrate the different ways of switching partitions, they do not create any indexes and they map all partitions to the [PRIMARY] filegroup. These examples are not meant to be used in real-world projects.

1. Switch from Non-Partitioned to Non-Partitioned

The first way to use the ALTER TABLE SWITCH statement is to switch all the data from a non-partitioned table to an empty non-partitioned table:

ALTER TABLE Source SWITCH TO Target

Before switch:

Partition Switch: Non-Partitioned to Non-Partitioned (Before)

After switch:

Partition Switch: Non-Partitioned to Non-Partitioned (After)

This is probably not used a lot, but it is a great way to start learning the ALTER TABLE SWITCH statement without having to create partition functions and partition schemes:

-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesSource')
  DROP TABLE SalesSource;
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesTarget')
  DROP TABLE SalesTarget;
 
-- Create the Non-Partitioned Source Table (Heap) on the [PRIMARY] filegroup
CREATE TABLE SalesSource (
  SalesDate DATE,
  Quantity INT
) ON [PRIMARY];
 
-- Insert test data
INSERT INTO SalesSource(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2012-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2012-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;
 
-- Create the Non-Partitioned Target Table (Heap) on the [PRIMARY] filegroup
CREATE TABLE SalesTarget (
  SalesDate DATE,
  Quantity INT
) ON [PRIMARY];

-- Verify row count before switch
SELECT COUNT(*) FROM SalesSource; -- 1461000 rows
SELECT COUNT(*) FROM SalesTarget; -- 0 rows

-- Turn on statistics
SET STATISTICS TIME ON;

-- Is it really that fast...?
ALTER TABLE SalesSource SWITCH TO SalesTarget; 
-- YEP! SUPER FAST!

-- Turn off statistics
SET STATISTICS TIME OFF;

-- Verify row count after switch
SELECT COUNT(*) FROM SalesSource; -- 0 rows
SELECT COUNT(*) FROM SalesTarget; -- 1461000 rows

-- If we try to switch again we will get an error:
ALTER TABLE SalesSource SWITCH TO SalesTarget; 
-- Msg 4905, ALTER TABLE SWITCH statement failed. The target table 'SalesTarget' must be empty.

-- But if we try to switch back to the now empty Source table, it works:
ALTER TABLE SalesTarget SWITCH TO SalesSource; 
-- (...STILL SUPER FAST!)

2. Load data by switching in: Switch from Non-Partitioned to Partition

The second way to use the ALTER TABLE SWITCH statement is to switch all the data from a non-partitioned table to an empty specified partition in a partitioned table:

ALTER TABLE Source SWITCH TO Target PARTITION 1

Before switch:

Partition Switch: Non-Partitioned to Partition (Before)

After switch:

Partition Switch: Non-Partitioned to Partition (After)

This is usually referred to as switching in to load data into partitioned tables. The non-partitioned table must specify WITH CHECK constraints to ensure that the data can be switched into the specified partition:

-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesSource')
  DROP TABLE SalesSource;
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesTarget')
  DROP TABLE SalesTarget;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
  DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
  DROP PARTITION FUNCTION pfSales;
 
-- Create the Partition Function 
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE RIGHT FOR VALUES 
('2013-01-01', '2014-01-01', '2015-01-01');
 
-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales 
ALL TO ([Primary]);

-- Create the Non-Partitioned Source Table (Heap) on the [PRIMARY] filegroup
CREATE TABLE SalesSource (
  SalesDate DATE,
  Quantity INT
) ON [PRIMARY];
 
-- Insert test data
INSERT INTO SalesSource(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2012-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2012-01-01','2013-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;
 
-- Create the Partitioned Target Table (Heap) on the Partition Scheme
CREATE TABLE SalesTarget (
  SalesDate DATE,
  Quantity INT
) ON psSales(SalesDate);
 
-- Insert test data
INSERT INTO SalesTarget(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2013-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2013-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;

-- Verify row count before switch
SELECT COUNT(*) FROM SalesSource; -- 366000 rows
SELECT 
	pstats.partition_number AS PartitionNumber
	,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesTarget')
ORDER BY PartitionNumber; -- 0 rows in Partition 1, 365000 rows in Partitions 2-4

-- Turn on statistics
SET STATISTICS TIME ON;

-- Is it really that fast...?
ALTER TABLE SalesSource SWITCH TO SalesTarget PARTITION 1; 
-- NOPE! We get an error:
-- Msg 4982, ALTER TABLE SWITCH statement failed. Check constraints of source table 'SalesSource' 
-- allow values that are not allowed by range defined by partition 1 on target table 'Sales'.

-- Add constraints to the source table to ensure it only contains data with values 
-- that are allowed in partition 1 on the target table
ALTER TABLE SalesSource
WITH CHECK ADD CONSTRAINT ckMinSalesDate 
CHECK (SalesDate IS NOT NULL AND SalesDate >= '2012-01-01');

ALTER TABLE SalesSource
WITH CHECK ADD CONSTRAINT ckMaxSalesDate 
CHECK (SalesDate IS NOT NULL AND SalesDate < '2013-01-01');

-- Try again. Is it really that fast...?
ALTER TABLE SalesSource SWITCH TO SalesTarget PARTITION 1; 
-- YEP! SUPER FAST!

-- Turn off statistics
SET STATISTICS TIME OFF;

-- Verify row count after switch
SELECT COUNT(*) FROM SalesSource; -- 0 rows
SELECT 
	pstats.partition_number AS PartitionNumber
	,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesTarget')
ORDER BY PartitionNumber; -- 366000 rows in Partition 1, 365000 rows in Partitions 2-4

3. Archive data by switching out: Switch from Partition to Non-Partitioned

The third way to use the ALTER TABLE SWITCH statement is to switch all the data from a specified partition in a partitioned table to an empty non-partitioned table:

ALTER TABLE Source SWITCH PARTITION 1 TO Target

Before switch:

Partition Switch: Partition to Non-Partitioned (Before)

After switch:

Partition Switch: Partition to Non-Partitioned (After)

This is usually referred to as switching out to archive data from partitioned tables:

-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesSource')
  DROP TABLE SalesSource;
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesTarget')
  DROP TABLE SalesTarget;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
  DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
  DROP PARTITION FUNCTION pfSales;
 
-- Create the Partition Function 
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE RIGHT FOR VALUES 
('2013-01-01', '2014-01-01', '2015-01-01');
 
-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales 
ALL TO ([Primary]);
 
-- Create the Partitioned Source Table (Heap) on the Partition Scheme
CREATE TABLE SalesSource (
  SalesDate DATE,
  Quantity INT
) ON psSales(SalesDate);
 
-- Insert test data
INSERT INTO SalesSource(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2012-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2012-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;

-- Create the Non-Partitioned Target Table (Heap) on the [PRIMARY] filegroup
CREATE TABLE SalesTarget (
  SalesDate DATE,
  Quantity INT
) ON [PRIMARY];

-- Verify row count before switch
SELECT 
	pstats.partition_number AS PartitionNumber
	,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('Sales')
ORDER BY PartitionNumber; -- 366000 rows in Partition 1, 365000 rows in Partitions 2-4
SELECT COUNT(*) FROM SalesTarget; -- 0 rows

-- Turn on statistics
SET STATISTICS TIME ON;

-- Is it really that fast...?
ALTER TABLE SalesSource SWITCH PARTITION 1 TO SalesTarget; 
-- YEP! SUPER FAST!

-- Turn off statistics
SET STATISTICS TIME OFF;

-- Verify row count after switch
SELECT 
	pstats.partition_number AS PartitionNumber
	,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesSource')
ORDER BY PartitionNumber; -- 0 rows in Partition 1, 365000 rows in Partitions 2-4
SELECT COUNT(*) FROM SalesTarget; -- 366000 rows

4. Switch from Partition to Partition

The fourth way to use the ALTER TABLE SWITCH statement is to switch all the data from a specified partition in a partitioned table to an empty specified partition in another partitioned table:

ALTER TABLE Source SWITCH PARTITION 1 TO Target PARTITION 1

Before switch:

Partition Switch: Partition to Partition (Before)

After switch:

Partition Switch: Partition to Partition (After)

This can be used when data needs to be archived in another partitioned table:

-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesSource')
  DROP TABLE SalesSource;
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesTarget')
  DROP TABLE SalesTarget;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
  DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
  DROP PARTITION FUNCTION pfSales;
 
-- Create the Partition Function 
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE RIGHT FOR VALUES 
('2013-01-01', '2014-01-01', '2015-01-01');
 
-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales 
ALL TO ([Primary]);
 
-- Create the Partitioned Source Table (Heap) on the Partition Scheme
CREATE TABLE SalesSource (
  SalesDate DATE,
  Quantity INT
) ON psSales(SalesDate);
 
-- Insert test data
INSERT INTO SalesSource(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2012-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2012-01-01','2013-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;

-- Create the Partitioned Target Table (Heap) on the Partition Scheme
CREATE TABLE SalesTarget (
  SalesDate DATE,
  Quantity INT
) ON psSales(SalesDate);
 
-- Insert test data
INSERT INTO SalesTarget(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2013-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2013-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;

-- Verify row count before switch
SELECT 
	pstats.partition_number AS PartitionNumber
	,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesSource')
ORDER BY PartitionNumber; -- 366000 rows in Partition 1, 0 rows in Partitions 2-4
SELECT 
	pstats.partition_number AS PartitionNumber
	,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesTarget')
ORDER BY PartitionNumber; -- 0 rows in Partition 1, 365000 rows in Partitions 2-4

-- Turn on statistics
SET STATISTICS TIME ON;

-- Is it really that fast...?
ALTER TABLE SalesSource SWITCH PARTITION 1 TO SalesTarget PARTITION 1; 
-- YEP! SUPER FAST!

-- Turn off statistics
SET STATISTICS TIME OFF;

-- Verify row count after switch
SELECT 
	pstats.partition_number AS PartitionNumber
	,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesSource')
ORDER BY PartitionNumber; -- 0 rows in Partition 1-4
SELECT 
	pstats.partition_number AS PartitionNumber
	,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesTarget')
ORDER BY PartitionNumber; -- 366000 rows in Partition 1, 365000 rows in Partitions 2-4

Error messages

SQL Server provides detailed and informative error messages if not all requirements are met before switching partitions. You can see all messages related to ALTER TABLE SWITCH by executing the following query, it is also quite a handy requirements checklist:

SELECT message_id, text 
FROM sys.messages 
WHERE language_id = 1033
AND text LIKE '%ALTER TABLE SWITCH%';

Summary

Partition Switching

Partition switching moves entire partitions between tables almost instantly. New data can be loaded to separate tables and then switched in, old data can be switched out to separate tables and then archived or purged. There are many requirements for switching partitions. It is important to understand and test how partition switching works with filegroups, indexes and constraints.

This post is the second in a series of Table Partitioning in SQL Server blog posts. It covers the basics of partition switching. Future blog posts in this series will build upon this information and these examples to explain other and more advanced concepts.

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, BimlHero Certified Expert, Microsoft Certified Solutions Expert, international speaker, author, blogger, and chronic volunteer who loves teaching and sharing knowledge. She works as a Senior Business Intelligence Consultant at Inmeta, focusing on Azure Data and the Microsoft Data Platform. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)

Comments

Hi! This is Cathrine. Thank you so much for visiting my blog. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Be kind to each other. Thanks!

Pingback: SQL New Blogger Digest – Week 3 | The Rest is Just Code

HI catherine, such a clear, concise and well written post covering just the basics of table partition and sliding window technique. thank you for the post. are you planning on writing part 3 covering all the advanced concepts?

Yes, I am currently working on more posts in the table partitioning series, covering other and more advanced topics.

Hi Catherine
This is an excellent post on Table Partitioning in SQL Server – Partition Switching
Hoping to learn more from you.

Very well Explained thanks :)

Any excellent overview. Thanks for sharing!

Nice and simple .thanks

geat !

Great article and presented very well with details!! Thanks Cathrine!

Well Explained, Very Nice Article ..

For me the code is erroring out saying “Invalid object name ‘GetNums’.” Any idea why?

Yes, you will have to create the GetNums helper function from the first blog post in the series: Table Partitioning in SQL Server – The Basics

Thanks Cathrine :)

Pingback: Tuan Vo (dot) info | Table Partitioning in SQL Server – Partition Switching

Thanks, this and the webinar are both really useful and very well explained for the reader/viewer

Pingback: How To Drop Partition Function In Sql Server | Information

Great . Can’t be better than this :)

Hello,
Partitioning is Something I use everyday, there are many challenges that I could overcome, but I still have one. When the system is working on the partition, somehow the data is not available on higher level.
I thought about using views to always have a view on all data at any time as we have consumers worldwide and so the data needs to be available at any time. The problem is that SQL server then doesn’t use the partition elimination and so the queries are very slow as the number of row is growing…
Do you have any idea ?

Hi Oliver,

your question is fairly general, so it’s not clear what the specific nature of your problem is. Partitioning is generally used for large tables; the benefits are mostly around inserting new rows (or aging old data.) When it comes to range queries, you can get some performance benefits when a query only looks for results from within a single partition; but for that to work well, I’ve found you need to have a clustered index with the partitioning column being the first element in the index.

Clustered indices (or indexes) are a whole ‘nuther topic on their own, but there’s plenty of good information available about them.

I hope this helps.

Hello Russel, the thing is that I have big table that are filled in each hours by a lot of data. In order to be efficient, I do all the job of inserting/merging data in another table using the partition switching. So every process only works on “few” records at a time.
At that moment, the whole partition is out of the table and so if someone queries the table, then the data are not available.
I am just looking for a way to keep those data available when working.
The size of all makes the process long enough to have that need. I talk about billions of rows in the main table, 11k partitions, several million rows in each partitions…
I already needed to add incremental statistics, change all queries that acces the tables in order to cast the partition column , use option (optimize for unknown),…

What will be the fragmentation status if we switch the huge tables which contains 200 million records will it be taking the same amount of time. what if the page or index is corrupted on the source data will the corrupted page or index gets transferred on the target table.

When you switch a table partition, you just change the “owner” of that partition, you don’t transfer data, you change nothing else. That is why it is so fast, and of course, you can then understand that it doesn’t change the rest.
The advantage of that is that if you have bad data in one partition, then you can prepare correct data in a working table, switch bad data out of the table, swicth the correct data in and then clean the bad data.

Pingback: Indexing a HUGE Table – nate_the_dba

Thanks a lot :) Can you please write more in this series. For example, partitioning current unpartitioned tables, modifying partitions, dropping partitions, etc. ?

Excellent post,Thanks. Really helped me to figure out a problem quickly

Hi Catherine, I’ve got one question. How to set constraints for the edge partitions – first and last – where based on your check script Partition Range is (Period > Infinity and Period 202012 and Period <= Infinity) for the last one ? I tried to use constraints like (Period IS NOT NULL AND Period = '201001') or (Period IS NOT NULL AND Period <= '201001') but both doesn't work. I get an error message : ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'BI.dbo.FactPartitioning' allows values that are not allowed by check constraints or partition function on target table 'BI.dbo.FactPartitioning_Staging'. Partitioning function first element is Period '201001'.

PS: First edge partition: (Period > Infinity and Period 202012 and Period <= Infinity)

Something is calculating the text in here so again. First partition is Period > Infinity and Period <= 201001

Last partition is Period > 202012 and Period <= Infinity

Hello, what do you want to achive with this ?

For example Switched out the data from the PARTITION 1 to the staging table. Right now everything works well for every partition except the first and the last partition. So the question is how to specify the boundaries of the PARTITION 1 for the CHECK CONSTRAINT of the staging table to be able to SWITCHED OUT the PARTITION 1 there ?

The switch partition command needs to specify the partition number…
So if you switch from one table to another table that are mapped to the same partition, then it’s easy, you just need to do your command
alter table x switch partition 1 to table Y partition 1
Now if you are using different partition function, then you get the partition number by using the $Partition function….
Normally your table should contain one “date” in partition field of the partition 1 unless you missed something.
Then you get your first date and you use select $partition.partfunction(yourdate) to get the partition number of your second table.
Then you just switch the partition 1 to the partition x of the target table.
You can do the same for the max partition.
If you look the last filled date in your table, be aware that every date that is higher that your last partition will be in that partition and so you cannot just ask the system to give you the last date, but you can look the partition before the last one and add 1 to get the partition number…
Anyway, you should consider to let the first and last partition “empty” to avoid trouble. As when you let them filled with data, merging partition takes time.

I found out the solution. The problem was with the incorrect constraints for the staging table PARTITION 1 (Period 201001).

I’ve implemented following constraint for the staging table and now SWITCHING OUT and SWITCHING IN works well.

ALTER TABLE [dbo].[Staging]
WITH CHECK ADD CONSTRAINT ckPeriodRangeMin
CHECK (Period IS NOT NULL AND Period > ‘200912’ AND Period <= '201001');

It's strange a bit because Period (201001) is not a date but INT column and partitions in partitioning functions are like 201001, 201002 etc.
There is no information at all about '200912'. I'm wondering how SQL Server is able to assess that left boundary for the first partition is '200912' while it's not a date but INT data type ?

Thanks for the article :)

Great Article! I have been fighting with non-partitioned and partitioned for months. This helped greatly!

Thanks a lot! Very well explained all.

excellent article. this really helped me with my job understanding this concept that i had never paid much attention to before. thank you so much!

I thought every table in sql server has at least one partition. When you say non-partitioned tables, are you referring to single partition tables or something else?

You are correct. When I say non-partitioned, I’m referring to a table with a single partition, where the creator has not specifically chosen to implement partitioning. Perhaps single-partitioned table is more accurate, but I have chosen to use non-partitioned in this post as it’s an easier concept to grasp for those who are new to partitioning.

Thanks for sharing this article on partitioning. I have found it quite useful.
I have a table that is partitioned on a daily date in SQL Server 2016. During a normal day, I get 1 days data from source which, I first load it in a stage table and then I copy the same to the partitioned table. Each day’s data has 10 million rows and is growing.

Occasionally, I get some historical data from the source which has data going 3 months data (> 300 million). Some of this historical data contains updates and I need to replace the existing partitioned data to the new data.

I would like to implement partition switch strategy by applying same partition schema in the stage table and the target table. However, I am not sure how to retrieve the partition ID in SQL Server by passing the date value. Is there a function that does this?

Thanks Catherine. It was very clearly explained and the examples were so easy to follow.

Hi! This is Cathrine (again). Just a reminder. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Be kind to each other. Thanks!

Share Your Thoughts?