What is an INDEX,How an INDEX Works ?

What is an INDEX ?

The SQL Server engine uses an index in much the same way a reader uses a book index. For example, one way to find all references to CHERUKURI statements in a SQL book would be to begin on page one and scan each page of the book. We could mark each time we find the word CHERUKURI until we reach the end of the book. This approach is pretty time consuming and laborious. Alternately, we can also use the index in the back of the book to find a page number for each occurrence of the CHERUKURI statements. This approach produces the same results as above, but with tremendous savings in time.

When a SQL Server has no index to use for searching, the result is similar to the reader who looks at every page in a book to find a word: the SQL engine needs to visit every row in a table. In database terminology we call this behavior a table scan, or just scan.

A table scan is not always a problem, and is sometimes unavoidable. However, as a table grows to thousands of rows and then millions of rows and beyond, scans become correspondingly slower and more expensive.

Consider the following query on the Product table under Production of the AdventureWorks database. This query retrieves Product Information in a specific UnitPrice range.

SELECT ProductID,
             ProductNumber,
             UnitPrice
FROM    Product
WHERE (UnitPrice > 12.5) AND (UnitPrice < 14)

There is currently no index on the Product table to help this query, so the database engine performs a scan and examines each record to see if UnitPrice falls between 12.5 and 14. In the diagram below, the database search touches a total of 504 records to find just three matches.

Now imagine if we created an index, just like a book index, on the data in the UnitPrice column. Each index entry would contain a copy of the UnitPrice value for a row, and a reference (just like a page number) to the row where the value originated. SQL will sort these index entries into ascending order. The index will allow the database to quickly narrow in on the three rows to satisfy the query, and avoid scanning every row in the table.

How to create An Index ?

Having a data connection in the Server Explorer view of Visual Studio.NET allows us to easily create new indexes:

• Navigate to the Production.Product table of the AdventureWorks database.
• Right click the table and select Design Table from the context menu.
• With the design screen in focus, click the Indexes/Keys item on the View menu of the IDE.

The dialog is currently displaying an existing index on the Products table: the PK_Products index. Primary key fields are automatically indexed to enforce uniqueness in the key values.

• In the above dialog click on the New button, and in the Index name text box, replace the existing entry with IDX_UnitPrice.
• Beneath the text box is a control where we set the columns to index. Pull down the entry with ProductID and select the UnitPrice column instead.
• Leave all of the other options with default settings.
• Close the dialog and the table design view, making sure to save all of the changes when prompted to do so. The IDE will then issue the commands to create the new index.

We can create the same index using the following SQL. The command specifies the name of the index (IDX_UnitPrice), the table name (Product), and the column to index (UnitPrice).

CREATE INDEX [IDX_UnitPrice] ON Product (UnitPrice)

To verify that the index is created, use the following stored procedure to see a list of all indexes on the Product table:

EXEC sp_helpindex Product

How an INDEX Works ?

The database takes the columns specified in a CREATE INDEX command and sorts the values into a special data structure known as a B-tree. A B-tree structure supports fast searches with a minimum amount of disk reads, allowing the database engine to quickly find the starting and stopping points for the query we are using.

Conceptually, we may think of an index as shown in the diagram below. On the left, each index entry contains the index key (UnitPrice). Each entry also includes a reference (which points) to the table rows which share that particular value and from which we can retrieve the required information.

Much like the index in the back of a book helps us to find keywords quickly, so the database is able to quickly narrow the number of records it must examine to a minimum by using the sorted list of UnitPrice values stored in the index. We have avoided a table scan to fetch the query results. Given this sketch of how indexes work, lets examine some of the scenarios where indexes offer a benefit.