Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
parody
Posting Yak Master
111 Posts |
 Posted - 2011-09-13 : 08:57:34
|
| maybe someone can help me with my understanding. In all the documentation I read about the benefits of partitioning, one of the main ones is the SWITCH command allowing a metadata only change to move data from one location to another. Also, amoungst other best practices, is the recommendation to manage partitioning with multiple file groups.Does this not contradict the benefit of SWITCH? If you were to switch from one partion to another, each on its own filegroup, surely SQL will need to physically relocate the data else it will not be on the filegroup associated with that partition? Hence in this scenario SWITCH would not be a metadata only action?Another benefit advocated is that of switching staging data into a target table. But to do this the staging data needs to reside in the same DB as the target. Who uses a stage table in the same DB, perhaps even the same server, as the target?And lastly in order to use switch all indexing must be aligned and contain the partioning key. Does this mean a primary key must be voilated, or dropped and recreated, in order to use switch?Hoping someone can tell me I am missing something... |
|
|
parody
Posting Yak Master
111 Posts |
Posted - 2011-09-13 : 10:32:25
|
| Well no responses yet! Some more googling has found the answer that moving data across file groups does indeed enforce physical relocation as posted here, although in relation to updating the partitioning key rather than switch, I assume this would still be the case [url]http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/70dc69a7-f62a-4705-988f-9c4828700b34/[/url]. Is this therfore not contradictory recommendation (read in a MS whitepaper)?Does anyone have any advice on what partitioning strategy is best to use when not all of the source partition needs to be switched, merged or split? In the typical sliding window scenario it is always the entire partition that is moved. If you were to partition a large dimension table using, for example an end date or "current" flag, then not all of a partiton will migrate. |
 |
|
|
|
|
|
|
|
Partitioning and multiple file groups