Shared Flashcard Set

Details

Chapter 6 Flashcards
study for the 70-432 exam
17
Computer Science
Professional
08/03/2010

Additional Computer Science Flashcards

 


 

Cards

Term
What SQL Server feature allows one to divide a table or index into multiple filegroups?
Definition
Partitioning
Term
What type of function defines the boundary points that will be used to split data across a partition scheme?
Definition
A partition function
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
Exactly one.
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

SPLIT.

 

Term
Which operator is used to remove boundary points from a partition function?
Definition
MERGE.
Term
Which operator is used to move partitions between tables?
Definition
SWITCH.
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
$PARTITION.
Supporting users have an ad free experience!