What is Index and how it works in SQL Server
What is HEAP
A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap.
Data is stored in the heap without specifying an order. Usually data is initially stored in the order in which is the rows are inserted into the table, but the Database Engine can move data around in the heap to store the rows efficiently; so the data order cannot be predicted.
To guarantee the order of rows returned from a heap, you must use the ORDER BY clause.
To specify the order for storage of the rows, create a clustered index on the table, so that the table is not a heap.
How heap works
When we do a search in a heap, which is a table without clustered index, a full table scan will be performed.
When use Heap
When the table is very small.
When not use Heap
Do not use a heap when the data is frequently returned in a sorted order. A clustered index on the sorting column could avoid the sorting operation.
Do not use a heap when the data is frequently grouped together. Data must be sorted before it is grouped, and a clustered index on the sorting column could avoid the sorting operation.
Do not use a heap when ranges of data are frequently queried from the table. A clustered index on the range column will avoid sorting the entire heap.
Do not use a heap when there are no nonclustered indexes and the table is large. In a heap, all rows of the heap must be read to find any row.
What is Index
An index is an on-disk structure associated with a table or view that speeds retrieva l of rows from the table or view.
An index is made up of a set of pages (index nodes).
An 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 SQL Server to find the row or rows associated with the key values quickly and efficiently.
You can create indexes on most columns in a table or a view.
The exceptions are primarily those columns configured with large object (LOB) data types, such as image, text, and varchar(max).
How it works
If you search for a value in a indexed column, the query engine will start from Root node -> intermediate nodes -> leaf nodes.
The leaf node will contain either the entire row of data or a pointer to that row, depending on whether the index is clustered or nonclustered.
How index is used by query optimizer
When this query is executed, the query optimizer eva luates each available method for retrieving the data and selects the most efficient method. The method may be a table scan, or may be scanning one or more indexes if they exist.
When performing a table scan, the query optimizer reads all the rows in the table, and extracts the rows that meet the criteria of the query. A table scan generates many disk I/O operations and can be resource intensive. However, a table scan could be the most efficient method if, for example, the result set of the query is a high percentage of rows from the table.
When the query optimizer uses an index, it searches the index key columns, finds the storage location of the rows needed by the query and extracts the matching rows from that location. Generally, searching the index is much faster than searching the table because unlike a table, an index frequently contains very few columns per row and the rows are in sorted order.
What's the benefit of index
Wel