Table Partitions Flashcards Preview

70-464 Certification > Table Partitions > Flashcards

Flashcards in Table Partitions Deck (19)
Loading flashcards...
1

How is data partitioned in a table?

On a single column often called the Partition Key

2

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

3

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

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

4

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

5

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

Range Left and Range Right

6

What does Range Left mean?

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

7

What does Range Right mean?

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

8

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

No

9

What is a partition scheme?

This is what maps the logical partitions to physical filegroups.

10

What is a filegroup?

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

11

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

You can set filegroups to READ ONLY

12

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.

13

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

ALTER TABLE [TABLENAME] SET LOCK_ESCALATION = auto

14

What are the lock_escalation settings for a table?

Auto
Table
Disable

15

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

16

What is Lock_Escalation = Table?

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

17

What is Lock_Escalation = Disable?

Prevents lock escalation in cases outside of SERIALIZABLE

18

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

19

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

** 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