One of the most important routes to high performance in a SQL Server database is the index.
Indexes speed up the querying process.
Indexes are created on columns in tables or views.
The index provides a fast way to look up data based on the values within those columns.
For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.
You can create indexes on most columns in a table or a view.
An index is made up of a set of pages (index nodes) that are organized in a B-tree structure.
When a query is issued against an indexed column, the query engine starts at the root node and navigates down through the intermediate nodes, with each layer of the intermediate level more granular than the one above. The query engine continues down through the index nodes until it reaches the leaf node.
Clustered Indexes
A clustered index stores the actual data rows at the leaf level of the index.
Returning to the example above, that would mean that the entire row of data associated with the primary key value of 123 would be stored in that leaf node.
Unlike a clustered indexed, the leaf nodes of a nonclustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data.
Indexes speed up the querying process.
Indexes are created on columns in tables or views.
The index provides a fast way to look up data based on the values within those columns.
For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.
You can create indexes on most columns in a table or a view.
An index is made up of a set of pages (index nodes) that are organized in a B-tree structure.
When a query is issued against an indexed column, the query engine starts at the root node and navigates down through the intermediate nodes, with each layer of the intermediate level more granular than the one above. The query engine continues down through the index nodes until it reaches the leaf node.
Clustered Indexes
A clustered index stores the actual data rows at the leaf level of the index.
Returning to the example above, that would mean that the entire row of data associated with the primary key value of 123 would be stored in that leaf node.
Unlike a clustered indexed, the leaf nodes of a nonclustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data.
