Partition Magic: Metadata and Partition Switching
There are many reasons for partitioning your data and indexes in SQL Server, and one of those is because moving data in and out of a partition can be more efficient than any other type of data movement. This is because the way SQL Server keeps track of the internal storage of partitioned data allows data to be moved as a metadata only operation. In this session we’ll look at the metadata for table, index and partition storage to explore exactly what happens when a partition is moved. Looking at the internals of partition storage will also allow us to understand the reasons for some of the restrictions on how and when partitions can be moved, and allow us to design our data movement processes most efficiently.