Term
| What is the structure that SQL Server uses to build and maintain indexes called? |
|
Definition
| a balanced tree (or B-tree). |
|
|
Term
| True or False: A B-tree is always symmetrical. |
|
Definition
| True. (Hence the name, "balanced" tree.) A B-tree always has the same number of data pages on both the left and the right halves at each level in the tree. |
|
|
Term
| What are the three types of pages within an index? |
|
Definition
| Root, Intermediate, and Leaf level pages |
|
|
Term
| How many bytes of data can pages in SQL Server store? |
|
Definition
|
|
Term
| How is a value located using a B-tree index? (Explain the steps.) |
|
Definition
- A query scans the root page until it finds a page that contains the value being searched on.
- It then uses the page pointer to hop to the next level and scan the rows in that page until it finds a page that contains the data being searched for.
- It then repeats the process with subsequent levels until it reaches the leaf level of the index.
At this point, the query has located the required data. |
|
|
Term
| What happens when a data page at the root level exceeds storage space? |
|
Definition
Page Splitting.
The existing root page is pushed down the structure to become one of two leaf-level pages, keeping half of the data and allocating the other half to another leaf-level page. The first entry on each of the leaf pages is then written to a new root look-up page. |
|
|
