Term
| how do you split large tables across multiple storage structures? |
|
Definition
|
|
Term
| True or False, the DBA cannot specify which portion of the object will be stored in a specific file group. |
|
Definition
| false, the DBA can specify. |
|
|
Term
| what two ways can you partition a table? |
|
Definition
| vertical and horizontally |
|
|
Term
| Vertical partitioning spreads data across two or more ______, when they could be stored in a single table |
|
Definition
|
|
Term
| what versions of SQL Server does not support vertical partitioning? |
|
Definition
| SQL Server 2008 and below |
|
|
Term
| what type of partitioning should you use to separate more sensitive data for security purposes? |
|
Definition
|
|
Term
| what type of partitioning should you use to separate seldom-used data into a different table for a performance boost? |
|
Definition
|
|
Term
| Horizontal partitioning spreads data based on _______ |
|
Definition
|
|
Term
| horizontal partitioning is most commonly partitioned off ______ |
|
Definition
|
|
Term
| name 4 commonly used criteria for horizontally partitioning data? |
|
Definition
1. date ranges
2. price ranges
3. integer ranges
4. character-based values |
|
|
Term
| what defines a set of boundary points on which data will be partitioned? |
|
Definition
|
|
Term
| what does a partition function require? |
|
Definition
|
|
Term
| what data types are not allowed for partition functions? |
|
Definition
| text, ntext, image, varbinary(max), timestamp, xml, or varchar(max) |
|
|
Term
| RANGE _____: boundary point should be in the first partition |
|
Definition
|
|
Term
| RANGE ____: boundary point should be in the second partition |
|
Definition
|
|
Term
what does this partition function do?
CREATE PARTITION FUNCTION PF100 (int)
AS RANGE LEFT FOR VALUES9 (100); |
|
Definition
| First partition contains rows with values of 100 or less, second partition contains rows with values > 100 |
|
|
Term
what does this partition function do?
CREATE PARTITION FUNCTION PF100 (int)
AS RANGE RIGHT FOR VALUES (100); |
|
Definition
| first partition contains rows with values < 100; second partition contains rows with values of 100 or greater |
|
|
Term
| True or False, Partition functions don't map the entire range of data, there are gaps |
|
Definition
| False, they map the entire range of data - no gaps are present |
|
|
Term
| in partition functions, you cannot specify ______ boundary points |
|
Definition
|
|
Term
| Null values are always stored in the leftmost partition unless you explicitly set null as a boundary point with _______ |
|
Definition
|
|
Term
| if there are 9 boundary points, how many partitions are there? |
|
Definition
10 partitions,
partition function create one more partition than the number of boundary points you defined |
|
|
Term
| there is a max of _____ partitions and a max of _____ boundary points |
|
Definition
| 1,000 partitions, and 999 boundary points |
|
|
Term
CREATE PARTITION FUNCTION mypartfunction (int)
AS RANGE LEFT
FOR VALUES (10,20,30,40,50,60) |
|
Definition
|
|
Term
for tables, partition functions can partition an existing object after it has been populated with data |
|
Definition
| for tables: must drop the clustered index and recreate the clustered index on the partition scheme |
|
|
Term
| for indexes, partition functions can partition an existing object after it has been populated with data by _____ |
|
Definition
| dropping the index and recreating the index on the partition scheme |
|
|
Term
| true or false, partitioning an existing object is not I/O intensive |
|
Definition
| false, it is I/O intensive |
|
|
Term
| what associates a partition function with a set of filegroups? |
|
Definition
|
|
Term
| true or false, you must create the partition scheme before you create the filegroup |
|
Definition
| false, you must create the filegroup before you create the partition scheme |
|
|
Term
| when you place a table on the partition scheme, SQL Server uses the __________ to determine into which filegroup to place each row of data |
|
Definition
|
|
Term
| when you place a table on the partition scheme, SQL Server uses the __________ to determine into which filegroup to place each row of data |
|
Definition
|
|
Term
| when creating a normal table, what clause specifies the storage location |
|
Definition
|
|
Term
| when omitted the On clause is omitted, what file group is a table stored in? |
|
Definition
|
|
Term
| Partitioning key specified must match the _____, _____, and _____ of the partition function |
|
Definition
| data type, length, and precision |
|
|
Term
| what must you do to create a partitioned index? |
|
Definition
You must specify the partition scheme on the ON clause
example:
CREATE NONCLUSTERED INDEX idx_employeefirstname
ON dbo.Employee(FirstName)
ON mypartscheme(EmployeeID) |
|
|
Term
| when creating an index on a partitioned table, the ___________ is automatically included in the definition of each index |
|
Definition
|
|
Term
| true or false, you can add/remove boundary points in a partition function |
|
Definition
|
|
Term
| true or false, you can add filegroups to a partition scheme |
|
Definition
|
|
Term
| true or false, you can designate a filegroup to be used for the next partition created |
|
Definition
|
|
Term
| true or false, you cannot move partitions between tables |
|
Definition
| false, you CAN move partitions between tables |
|
|
Term
| syntax for for Split and Merge |
|
Definition
ALTER PARTITION FUNCTION partition_function()
{SPLIT RANGE (boundary_value) |
MERGE RANGE (boundary_value) } [ ; ] |
|
|
Term
| the split operator introduces a new __________ |
|
Definition
|
|
Term
| the split operator adds a ______ to the partition function |
|
Definition
|
|
Term
| in split and merge operations, what determines the range of a new partition? |
|
Definition
|
|
Term
| what does the merge operator do? |
|
Definition
|
|
Term
| true or false, to create more storage space, you can add filegroups to an existing partition scheme |
|
Definition
|
|
Term
| Filegroup specified as ________ will contain the next partition when a SPLIT operation is executed |
|
Definition
|
|
Term
| if a table and all its indexes are partitioned using the same partition function, they are said to be ______ |
|
Definition
|
|
Term
| if a table and all its indexes use the same partition function and the same partition scheme, the ________ is aligned as well |
|
Definition
|
|
Term
| what is the advantage of alignment |
|
Definition
| if a single partition is backed up or restored, the data and corresponding indexes are kept together as a single unit |
|
|
Term
| when a NEXT PAGE entry is ____, SQL Server does not read any further |
|
Definition
|
|
Term
| Data is stored in pages in a ________ list |
|
Definition
| doubly-linked list (linked by previous page and next page pointer entries) |
|
|
Term
| what is the purpose of the switch operator? |
|
Definition
if you could modify the next page pointer on the last page of a partition to have a value of (0:0), the next partition would "disappear" when the page chain was read.
>>>>If you put the missing partition in a new table, it would now be segregated from the original table, this is the purpose of the Switch operator |
|
|
Term
| what technique can be used to split off data and archive it |
|
Definition
|
|
Term
| True or False, to use the Switch Operator, data and index for source and target tables must be aligned |
|
Definition
|
|
Term
| True or False, to use the Switch Operator, the source and target tables must NOT have identical structure |
|
Definition
| False, they should have identical structures |
|
|
Term
| true or false, using the switch operator, data can be moved from one filegroup to another |
|
Definition
| false, data cannot be moved from one filegroup to another |
|
|
Term
| true or false, to use the switch operator the target partition must be empty |
|
Definition
|
|
Term
| Name four advantage to using partitioned tables and indexes |
|
Definition
1. Faster and easier data loading
2. Faster and easier data deletion or archival
3. Faster Queries
4. Sliding Windows |
|
|
Term
| what is the Sliding Window Effect? |
|
Definition
| slide in a window of new data into the current partitioned table, and then slide an old window of data out of the partitioned table |
|
|
Term
| true or false, during the Sliding Window Effect, the table goes offline & is unavailable throughout the process |
|
Definition
| false, the tables stays online & available throughout the process |
|
|
