What are the requirements for using SWITCH statement to switch partitions in SQL Server 2019

Question

Hello,

There are a number of limitations and requirements when performing partition SWITCH operation in SQL Sever. I found this old documentation, which applies to SQL Server 2008R2: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191160(v=sql.105)

However, this article is very old and it looks like at least some of the requirements listed there no longer apply. For example, I am using SQL Server 2019 and I am able to SWITCH partitions between tables, which don't have the same primary keys defined. Despite this, the SWTICH works fine.

For the life of me, I can't find anything more recent than the link I shared above. Is there updated documentation for this functionality?

Answer 1

Hi @SQL Guy

I also tried searching and did not find any official documentation that covered the SQL Server 2019 partition switching feature.

This is the official document related.

https://learn.microsoft.com/en-us/sql/relational-databases/partitions/manage-partition-wizard-f1-help?view=sql-server-ver15

There is also a post.

https://www.cathrinewilhelmsen.net/table-partitioning-in-sql-server-partition-switching/

Best regards,

Percy Tang