Term
What is the maximum # of columns and size allowed within an index key? |
|
Definition
|
|
Term
How does SQL arrange data in a clustered key? |
|
Definition
It sorts the data in the order defined in the clustered index |
|
|
Term
Is a clustered index a phsyical sort order? |
|
Definition
No.
Storing the data on disk in a sorted order creates a large amount of disk I/O for page split operations. Instead, a clustered index ensures the page chain of the index is sorted logically. |
|
|
Term
At what level in the B-tree is the clustered index stored? |
|
Definition
At the leaf level, which is the row of the data in the table |
|
|
Term
What is the name for a table without a clustered index? |
|
Definition
|
|
Term
What is the difference between a clustered and non clustered index? |
|
Definition
A non clustered index has a pointer at the leaf level to the row where the data exists in a table and requires a second read in order to get the data whereas a clustered index is the actual row of data at the leaf level.
Additionally, on page splits SQL server does not update the nonclustered index with an updated row. Instead it creates a forwarding pointer on the data page pointing to the new location of the row. |
|
|
Term
What is the table limit for clustered & nonclustered indexes |
|
Definition
1 clustered
1000 non clustered |
|
|
Term
What db actions can have performance degredation caused by indexes? |
|
Definition
INSERT
UPDATE
DELETE
BULK INSERT
BCP |
|
|
Term
An index that is constructed such that SQL Server can completely satisy queries by reading only the index is known as? |
|
Definition
|
|
Term
True or False?
SQL Server can use more than one index for a given query?
|
|
Definition
True...IF the two indexes share at least one column in common. They can be joined to satify the query on the common columns. |
|
|
Term
What is different with columns that are added to an index with the INCLUDE clause? |
|
Definition
They are only part of the index at the leaf level. They do not appear at the root or intermediate level and don't count against the size limits of an index. |
|
|
Term
What component is responsible for determingin whether an index should even be used to satisfy a query? |
|
Definition
|
|
Term
What is a filtered index? |
|
Definition
An index with a WHERE clause |
|
|
Term
What are the restrictions on a filtered index? |
|
Definition
- Must be a nonclucstered index
- Cannot be created on computed columns
- Cannot undergo implicit or explicit data conversion
|
|
|
Term
What option applies the FILLFACTOR to intermediate-level and root pages of an index? |
|
Definition
|
|
Term
What option specifies the work tables for sort operations be generated in tempdb |
|
Definition
|
|
Term
What index option locks the entire table, preventing any changes until the index is created? |
|
Definition
|
|
Term
WHEN ONLINE = ON allows for table changes during index creation. What version of SQL server are you using to use the command? |
|
Definition
SQL Server 2008 Enterprise |
|
|
Term
Can you have a secondary XML index without a primary XML Index? |
|
Definition
No.
A primary XML index is frist required, because secondary xml indexes are built against the data contained within the primary XML index |
|
|
Term
What type of index must you have before you can create a primary XML index |
|
Definition
Clustered index
The primary XML index is tied to the table by maintaining a link to the corresponding row in the clustered index. |
|
|
Term
An index on a table column that refers to a finite space, such a geometry, is known as? |
|
Definition
|
|
Term
How does the spatial index work? |
|
Definition
The index-creation process decomposes the space into a four-level grid hierarchy (Referred to as Level 1, Level 2, Level 3, and Level 4) creating a linear chain of data |
|
|
Term
What are the 3 different grid densities for spatial indexes? |
|
Definition
LOW (4x4)
MEDIUM (8x8)
HIGH (16x16) |
|
|
Term
What are the 3 rules that tesselation uses to limit the number of touched cells that are recorded for an object? |
|
Definition
Covering Rule
Cells-Per-Object Rule
Deepest-Cell Rule |
|
|
Term
The process of fitting objects into a grid hierachy by associating the object with a set of grid cells that is touches is known as? |
|
Definition
Tessellation
The output of the tessellation process is a set of touched cells that are recorded in the spatial index for the objects. By referring to these recorded cells, the spatial index can locate the object in space relative to other objects in the spatial column that are also stored in the index. |
|
|
Term
A bounding box is required to establish a finite space for decomposition of a spatial index. What is specified by the BOUNDING_BOX parameter |
|
Definition
The max and min X, Y coordinates
[image] |
|
|