Term
| What SQL Server feature allows one to divide a table or index into multiple filegroups? |
|
Definition
|
|
Term
| What type of function defines the boundary points that will be used to split data across a partition scheme? |
|
Definition
|
|
Term
| The data type for a partition function can be any native SQL Server data type, except which data types? |
|
Definition
- text
- ntext
- image
- varbinary(max)
- timestamp
- xml
- varchar(max)
- user-defined data types
- CLR data types
columns must be deterministic |
|
|
Term
| Write an example partition function. |
|
Definition
create partition function
mypartfunction(int) --requires a name and data type
as range left --tells if boundary point is included in the left or right partition
for values(10,20,30,40,50,60) --sets specified values as boundaries |
|
|
Term
| Null values are always stored in which partition? |
|
Definition
| The leftmost partition, until you explicitly specify NULL as a boundary point and use the range right syntax, in which case NULLs are stored in the rightmost partition. |
|
|
Term
| What is the maximum number of partitions you can have for an object? |
|
Definition
| 1,000. Therefore, you're allowed to specify a maximum of 999 boundary points. |
|
|
Term
| How do you partition an existing table, index, or indexed view? |
|
Definition
Drop the index and re-create it on the partition scheme.
Be careful when partitioning existing objects already containing data, because implementing the partition will cause a significant amount of disk input/output. |
|
|
Term
| What is a partition scheme? |
|
Definition
A partition scheme is a storage definition containing a collection of filegroups.
It can be defined to encompass one filegroup to contain all partitions created by the partition function it maps to, OR to specify separate filegroups for each individual partition created and mapped to. |
|
|
Term
True or False:
You can create a new filegroup at the same time you are creating a partition scheme. |
|
Definition
False.
Any filegroups that you specify in the create partition scheme statement must already exist in the database. |
|
|
Term
| How many filegroups can you specify if you use the ALL keyword when degining a partition scheme? |
|
Definition
|
|
Term
| To create a partitioned table, what line of code must be added to the create table table definition? |
|
Definition
ON mypartscheme(EmployeeID);
...an ON clause specifying that SQL Server should store the object on a partition scheme. In this case, that would be "mypartscheme," and the partition function this scheme maps to is applied to EmployeeID column. |
|
|
Term
True or False:
Any column in a table may be used as a partitioning key that is passed to a partitioning function. |
|
Definition
False.
The partitioning key that is specified must match the data type, length, and precision of the partition function.
If the partitioning key is a computed column, the computed column must be persisted. |
|
|
Term
| Which operator is used to add boundary points into a partition function? |
|
Definition
|
|
Term
| Which operator is used to remove boundary points from a partition function? |
|
Definition
|
|
Term
| Which operator is used to move partitions between tables? |
|
Definition
|
|
Term
| How are indexes and tables organized in order to be considered "aligned"? |
|
Definition
If a table and all its indexes are partitioned using the same partition function, they are said to be "aligned."
If they are using the same partition function AND the same partition scheme, the storage is aligned as well.
This is beneficial because if a single partition is backed up or restored, the data and its corresponding indexes are kept together as a single unit since they're all stored in the same filegroups. |
|
|
Term
| Which function allows you to limit queries to a specific partition? |
|
Definition
|
|
