Why should the source and target in the "SWITCH" section be in the same filegroup?

Question

I know that switching between partitions requires that the partition be in the same filegroup. But I can’t find a suitable place to know where / what could be the reason for this concept .. p>

Source and destination tables must share the same filegroup. The source and target table of the ALTER TABLE ... SWITCH statement must be in the same file group, and their columns with a larger value must be stored in the same file group. Any corresponding indexes, index sections, or indexed viewing sections must also be in the same file group. However, the filegroup may differ from the filegroup of the corresponding tables or other relevant indexes. http://technet.microsoft.com/en-us/library/ms191160(v=sql.105).aspx

In one of my section implementations:

I keep my archive table in the same filegroup, execute SWITCH, then drop and recreate the clustered index to move the data to another filegroup. It costs me dearly!

I want old data to be moved to another table. Archived (for analysis purposes) located in another filegroup (another disk). But because of this limitation, I implemented as mentioned

I understand the concept (data is not physically moving). but why? Expect an answer, for example, "due to page size limits for the sql server or overlapping swap concepts, etc."

Please help me find or understand this!

Answer 1

The switch effective because it essentially replaces disk addresses instead of moving data. Therefore, both datasets must be in the same filegroup to facilitate this “trick”.

Answer 2

Consider the following setup

  FG1 FG2 | | ------------ ------------ | | | | F1 F2 F3 F4 

If FG are groups of files, and F are separate files.

We have a section whose data, in all likelihood, currently exists in F1 . After we completed the switch, all its data will be within F1 . Although the restriction is limited only to files, the restriction is actually "data must remain inside the same file."

Why? Because we can do all this effectively. We cannot accept extents (or even individual data pages) within F1 and suddenly make them part of F2 (or F3 or F4 ), as these other files may be located on other disks. You cannot say that “this page of this disk is now part of this file located there on another disk” - this is not how the (most traditional) file systems work - certainly those that SQL Server runs on.

And if you need to move through groups of files, you cannot suddenly say that F1 (or part of it) is now part of FG2 , as well, and not belonging to FG1 . Files belong to only one group of files, because File Groups are a layer of control over several functions.

If we want to move a set of rows between two tables, but we want to write it in such a way that there are no restrictions on moving data, we already have tools for this - INSERT and DELETE (maybe write it as one nice combined operator using OUTPUT from DELETE as row source for INSERT ).

Someone from Microsoft could sit down and write an implementation of ALTER TABLE ... SWITCH , which allows you to move data, but they did not see the need to implement this. Instead, they fixed the current limit.

---

(I’ll notice that I still don’t get involved with “official sources” or really add a lot of new things here, which are impossible to understand from the fact that there are actually file groups, and I hope that someone will have at least some acquaintances before they encounter a situation where they are actually trying to move data between them)

Answer 3

One filegroup can store multiple tabular data. The statement refers to a table not to a filegroup. When a file group stores several table data and you try to move only one data table (switching only one table to another FG), the original file group must be partitioned, which requires a large amount of resources to complete the operation (to physically move the data). In this case, the operation is similar to the operation when you move the index or cluster key to another file group.

Edit In addition, the source and target FG and the associated partition scheme and function are not needed equally, and the data that you are trying to move does not necessarily correspond to the definition of the partition in the new file group.

From the comments below

Why would it not be possible to update metadata that stores file information, just like SQL Server updates metadata that is stored in the root of tree B? In this answer, I cannot find a reason why the switch cannot switch between filegroups.

This is not possible, but one physical file cannot be part of several FGs. If you change only metadata to another FG, and the original FG stores several tabular data (which means that the physical file stores several tabular data), file separation will be required or if the data is not moved, the file will be part of several FG.

Answer 4

The use case for SWITCH is not to move data to another storage, but to transfer data to another table as an operation only for metadata. This is by design. There are no technical reasons that prevent SQL Server from moving a partition to another filegroup, but then it will no longer be just metadata, and the operation can take a long time due to the extensive data movement. It will be basically the same expensive operation that you now perform manually.

This raises the question of why you are moving data to another table in general. I think you just want to move the partition to another filegroup, but save it in the original table.