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

SQL Guy 1 Reputation point
2023-11-22T20:13:15.98+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,314 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-11-23T06:11:22.7366667+00:00

    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

    0 comments No comments