What is Index in SQL Server?
The index is an on-disk structure associated with a table or view that speeds the retrieval of rows from the table or view.
The index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-Tree) that enables the SQL servers to find the row or row associated with key values quickly and efficiently.
The following are the Type of indexes in SQL Server
- Clustered
- Nonclustered
- Unique
- Filtered
- Columnstore
- Hash
- Memory-Optimized Nonclustered
If we take a great example book, at the end of the book there is an index page that will be available to help to quickly locate information on which chapter we need to search. If we need to go page by page its will take
more time to reach the target place.
The index is set as a sorted list of keywords and next to each keyword is a set of page numbers pointing to the page where each keyword can be found.
Go through the below link to understand more about the Binary Tree
Tree Structure
What is Binary Tree
Representation of Binary Tree
SQL Server Index Architecture
This image is from the Microsoft design page.
Link
The root page is the single page of the tree structure used by SQL server Index; the Bottom of the node is called the Leaf node. The root note has pointers to connect with Leaf nodes via intermediate node. Leaf nodes connect with data pages
An index stores data logically organized as a table with rows and columns, and physically stored in a row-wise data format called rowstore 1, or stored in a column-wise data format called columnstore.
The bottom nodes in the index are called leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows.
Reference Links about Index from Microsoft
Link 1
