Table Partitions

Question 1

How is data partitioned in a table?

Answer 1

On a single column often called the Partition Key

Question 2

Does the Partition Key column have to be a single column or can it be multiple?

Answer 2

It has to be a single column, but you CAN use a Computed Column

Question 3

What is the best column to use when selecting the Partition Key?

Answer 3

A column that is almost always used as a filter in queries.

Question 4

Why is using the Partition Key in a filter beneficial?

Answer 4

SQL Server will access only the relevant partitions - called Partition Elimination and can greatly improve performance

Question 5

What are the two ranges that a partition function can be created with?

Answer 5

Range Left and Range Right

Question 6

What does Range Left mean?

Answer 6

The actual boundary value belongs to its left partition. It is the LAST VALUE IN THE LEFT PARTITION

Question 7

What does Range Right mean?

Answer 7

The actual boundary value belongs to its right partition, it is the FIRST VALUE IN THE RIGHT PARTITION

Question 8

Can you have both a range left AND a range right in a partition function?

Answer 8

No

Question 9

What is a partition scheme?

Answer 9

This is what maps the logical partitions to physical filegroups.

Question 10

What is a filegroup?

Answer 10

It’s a container of 1 or more files that can be stored on one or more disks

Question 11

What is a major benefit with filegroups with regard to data integrity?

Answer 11

You can set filegroups to READ ONLY

Question 12

What is a space benefit regarding a READ ONLY filegroup?

Answer 12

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.

Question 13

How can you ensure that updates on one partition do not cause blocking on other partitions?

Answer 13

ALTER TABLE [TABLENAME] SET LOCK_ESCALATION = auto

Question 14

What are the lock_escalation settings for a table?

Answer 14

Auto
Table
Disable

Question 15

What is Lock_Escalation = Auto?

Answer 15

If the table is partitioned, it will lock to the partition

If the table is not partitioned, it will lock to the Table

Question 16

What is Lock_Escalation = Table?

Answer 16

Lock escalation is done at table-level granularity whether the table is partitioned or not (Default)

Question 17

What is Lock_Escalation = Disable?

Answer 17

Prevents lock escalation in cases outside of SERIALIZABLE

Question 18

How can you quickly move data from one partitioned table to another table to another without locking records at all?

Answer 18

Using ALTER TABLE SWITCH TO TABLE2

Question 19

When you use the SWITCH command, what are all of the things you have to verify on the second table?

Answer 19

• • The source and target tables (or partitions) must have identical columns, indexes and use the same partition column
• • The source and target tables (or partitions) must exist on the same filegroup
• • The target table (or partition) must be empty