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] |
|
|
