Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why in Partition 'SWITCH' statement source & target must reside in the same filegroup?

I know that switching between partitions requires both the partition to reside in same filegroup.But am not able to find any proper place to know where/what could be the reasons behind that concept.

Source and target tables must share the same filegroup. The source and the target table of the ALTER TABLE...SWITCH statement must reside in the same filegroup, and their large-value columns must be stored in the same filegroup. Any corresponding indexes, index partitions, or indexed view partitions must also reside in the same filegroup. However, the filegroup can be different from that of the corresponding tables or other corresponding indexes. http://technet.microsoft.com/en-us/library/ms191160(v=sql.105).aspx

In one of my partition implementation:

I keep my archival table in same filegroup, perform SWITCH , then drop and recreate clustered index to move data to different filegroup.This is costing me much !

I want the old data moved to different table i.e archivaltable (for analysis purpose) residing in a different filegroup(different drive).But due to this restriction i have implemented as mentioned

I understand the concept followed (data is not physically moved). but why? Expecting answer such as "due to sql-server pagesize limitation or paging concept overlaps etc etc" like that.

Please help me find or understand this !

like image 378
Recursive Avatar asked Nov 10 '22 00:11

Recursive


1 Answers

The switch statement is that efficient because it's essentially just replacing addresses on-disk instead of moving the data around. Hence, both sets of data need to be in the same file-group in order to facilitate this "trick".

like image 116
Mureinik Avatar answered Nov 15 '22 07:11

Mureinik