Term
|
Definition
associates a partition function with a set of
filegroups |
|
|
Term
| Must create the _________ before you can create the partition scheme |
|
Definition
|
|
Term
RANGE LEFT:
[1000-2999][3000-6999][7000-9999] |
|
Definition
boundary point should be in the first partition
specifies the highest number in the partition (2999,6999) |
|
|
Term
RANGE RIGHT:
[1000-2999][3000-6999][7000-9999] |
|
Definition
boundary point should be in the second partition
lowest number on the partition (3000,7000) |
|
|
Term
| Creating a Partitioned Table, Partitioning key specified must match what? |
|
Definition
the data type, length,
and precision of the partition function |
|
|
Term
| Creating a Partitioned Table, Assign the table to a partition scheme |
|
Definition
|
|
Term
| Creating a Partitioned Table, On Clause, |
|
Definition
specifies the
storage location |
|
|
Term
| Split or merge operations |
|
Definition
alter a partition function by splitting
or merging its boundary values |
|
|
Term
|
Definition
Introduces a new boundary point
Adds a partition to the partition function |
|
|
Term
|
Definition
Drops a partition
Merges any values that exist in the partition into one of the remaining partitions |
|
|
Term
|
Definition
| If a table and all its indexes are partitioned using the same partition function, they are said to be aligned |
|
|
Term
| Index Alignment, If a table and all its indexes use the same partition function and the same partition scheme, the _______ is aligned as well |
|
Definition
|
|
Term
|
Definition
if a single partition is backed up or
restored, the data and corresponding indexes are kept together as a single unit |
|
|
Term
|
Definition
| Purpose of partitions is to split a table and its associated indexes into multiple storage structures |
|
|
Term
| purpose of switch operator |
|
Definition
used to split off data and archive it
Switch allows you to exchange partitions between tables with no locking, blocking, or deadlocking |
|
|
Term
| Requirements: of Switch Operator |
|
Definition
Data and index for source and target tables must be aligned
Source and target tables must have identical structure
Data cannot be moved from one filegroup to another
Two partitions with data cannot be exchanged
Target partition must be empty
Source or target table cannot participate in replication
Source or target tables cannot have full text indexes or a
FILESTREAM data type defined |
|
|
Term
| Why Use Partitioned Tables & Indexes? |
|
Definition
Faster & easier data loading:
Faster & easier data deletion or archival:
Faster queries: |
|
|
Term
|
Definition
the ability to add a new table and switch it
into the live data, then switch out an existing partition for archival or deletion, using only metadata changes |
|
|
Term
| sliding windows, how it's done |
|
Definition
Chapter 6, 31-34
Step 1: For loading new data, a new empty partition is created and a staging table loaded
Step 2: The staging table data is switched or exchanged with the new partition
Step 3: For aging out the old data in partition 2, an empty staging table S is created on the same filegroup
Step 4: The data in partition 2 has been switched with staging table S, and the data is now in S |
|
|
