[1] Implementing Indexes Flashcards
What is a Page in TSQL?
A page is a 8 KB unit and belongs to a single object (e.g. table).
How many pages make an Extent?
8 (64KB).
What are the two types of extents and how do they differ?
- Mixed extent: when the underlying pages belong to multiple objects.
- Uniform extent: when the underlying pages belong to only one object.
How does SQL Server store pages in extents?
The first 8 in mixed extents and the subsequent in uniform extents.
What are the two other names for a Heap?
Balanced Tree and Clustered Table.
Is a Heap ordered in any way?
No.
What is an IAM and what purpose does it serve?
Index Allocation Mapping organises pages/extents with objects.
What is an IAM Page and what purpose does it serve?
An Index Allocation Mapping Page organises pages/extents with objects.
How many IAM can an object have?
At least one.
What is a Doubly Linked List and how does it work?
It is the structure SQL Server uses to organise large objects that have multiple IAM pages. Each page has a pointer to its decendent and antecendent.
How does SQL Server store data in a Heap?
SQL Server stores new rows anywhere in a heap: i.e. in an existing page if there is space, or a new page/extent.
What are the explicit and implicit ways a user can avoid data being stored in a Heap?
Explicit: creating a clustered index,
Implicit: primary key/ unique constraints.
What is the name given to the situation in which not all the pages in a heap are full?
Internal Fragmentation
How does Internal Fragmentation affect memory usage?
Because new rows are added to existing pages that are not full, SQL Server has less pages to read when retrieving information.
Is a Clustered Index the same as a Balanced Tree?
Yes. It begins with a single IAM and multiple pages thereafter.
What does SQL server do when a root page in a clustered index cannot point to all first leaf level pages?
It creates an intermediate level of pages which can themselves point to the first leaf level pages.
What is a Uniquifier?
A Uniquifier is the name given to the process by which SQL server adds its own unique sequentially-ordered values to the repeating values of a composite key that contains columns that allows duplicates.
What are the 3 ways a clustered index finds data?
- logical order (when required),
- physical order,
- according to order of columns in clustering key
What is fragmentation in indexing?
Fragmentation occurs when SQL adds rows to leafs that are not already full. It results in a splitting of the data from its physical ordering.
What are the two types of fragmentation possible when SQL stores data?
Internal fragmentation occurs when there is still space available in a page for a new row. External fragmentation occurs when, forced to maintain a logical order, SQL server forces a row between two others in a page, forcing the page to split, changing the physical order of the records.
What are the two clauses to combat fragmentation in indexing?
For Internal: WITH FILLFACTOR
For External: ALTER INDEX..REORGANIZE(REBUILD)
