Flashcards in Table Partitions Deck (19)
How is data partitioned in a table?
On a single column often called the Partition Key
Does the Partition Key column have to be a single column or can it be multiple?
It has to be a single column, but you CAN use a Computed Column
What is the best column to use when selecting the Partition Key?
A column that is almost always used as a filter in queries.
Why is using the Partition Key in a filter beneficial?
SQL Server will access only the relevant partitions - called Partition Elimination and can greatly improve performance
What are the two ranges that a partition function can be created with?
Range Left and Range Right
What does Range Left mean?
The actual boundary value belongs to its left partition. It is the LAST VALUE IN THE LEFT PARTITION
What does Range Right mean?
The actual boundary value belongs to its right partition, it is the FIRST VALUE IN THE RIGHT PARTITION
Can you have both a range left AND a range right in a partition function?
What is a partition scheme?
This is what maps the logical partitions to physical filegroups.
What is a filegroup?
It's a container of 1 or more files that can be stored on one or more disks
What is a major benefit with filegroups with regard to data integrity?
You can set filegroups to READ ONLY
What is a space benefit regarding a READ ONLY filegroup?
You can backup and restore filegroups separately, so you could take a single backup of the READ ONLY filegroup and store it away, saving space on your backups.
How can you ensure that updates on one partition do not cause blocking on other partitions?
ALTER TABLE [TABLENAME] SET LOCK_ESCALATION = auto
What are the lock_escalation settings for a table?
What is Lock_Escalation = Auto?
If the table is partitioned, it will lock to the partition
If the table is not partitioned, it will lock to the Table
What is Lock_Escalation = Table?
Lock escalation is done at table-level granularity whether the table is partitioned or not (Default)
What is Lock_Escalation = Disable?
Prevents lock escalation in cases outside of SERIALIZABLE
How can you quickly move data from one partitioned table to another table to another without locking records at all?
Using ALTER TABLE SWITCH TO TABLE2