Indexes Flashcards
Learn Indexes
What is a page?
- Page has a size of 8k
- 8 consecutive pages make one extent
- Page can only belong to a single object. Table, index…etc.
What is an extent?
- An extent is made up of 8 pages
- Uniform extent - All pages belong to single object
- Mixed extent - Pages belong to multiple objects
What happens when an object exceeds 8 pages?
SQL server allocates additional uniform extents for this object.
What is a heap?
A heap is just a bunch of pages and extents.
How does SQL server track where pages and extent belong?
Using a special page called Index Allocation Map to trace which page and extent belong to an object.
How many levels does a clustered index (TREE) has?
- Root - contains pointers
- Intermediate (when needed) - contains pointers
- Leaf - contains data
What is internal fragmentation?
- Clustered index only
- Caused by page split and when pages are not filled.
- Can be controlled by FILLFACTOR or PAD_INDEX
What is external fragmentation?
Clustered index only
When pages are physical out of order.
Non clustered index levels?
Similar to clustered index
- Root and intermediate level are pointers
- leaf level does not hold data instead it contains index keys and the RID (row identifier)
How is row identifier used?
It depends if the underlying table is a heap or a balanced tree.
- Balance tree - RID is the clustered index. Points to a logical structure
- Heap - RID has the dbs file ID, page ID, target row ID…etc. Points to a physical structure.
What is a filtered index?
Filter index is a nonclustered index optimized to cover queries that select from a well defined subset of data.
What is columnstore index?
- When a query references a single column that is a part of the columnstore index, SQL server fetches only that column from disk. The rows are reconstructed.
What are the steps to create indexed view?
- Verify that all the SET options are correct
- Verify that the view is deterministic
- create using WITH SCHEMABINDING
- First index create on the view must be clustered index
- If there is GROUP BY, don’t use HAVING. Use COUNT_BIG(*) instead.
Waht does SQL server use to aggregate data?
- hash
- Stream aggregate operator - faster but requries a sorted input
Why use INCLUDE with CREATE INDEX?
Add nonkey column to the non clustered index so that the queries are covered.