A Guide to SQL Server Indexes

Introduction

Relational databases like SQL Server use indexes to find data quickly when a query is processed. However, creating the proper index can drastically increase the performance of an application.
The SQL Server engine uses an index in much the same way a reader uses a book index.
For example, One way to find out a particular word (e.g. ‘XYZ’) in a book would be to go through each and every page of the book. We could mark each time we find the word (‘XYZ’) 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 that particular word (‘XYZ’). 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 SQL engine needs to visit every row in a table. In database terminology we call this behavior a table scan, or just scan.

Clustered Index

If you create a primary key on a table, then SQL Server will create an index for this primary key, this index will be a clustered index on the primary key(s) column(s).

A common analogy for a clustered index is a phone book. A phone book still sorts entries into alphabetical order. Once we find a name in a phone book, we have immediate access to the rest of the data for the name, such as the phone number and address.

For a clustered index, the actual data that comprises a database table is always stored on disk in a certain order according to the column (or columns) specified by the index. The data is physically arranged on the disk in this order. A clustered index contains all of the data for a table in the index, sorted by the index key, just like a phone book is sorted by name and contains all of the information for the person inline.
There can only be one index that is clustered for any table.

Advantage - Very efficient to retrieve data from a table.
[It would be faster to select the records in order of the Primary Key than to get them in any other order. This is because each record that needs to be accessed from disk is right next to the previous one, making the disk access more efficient.]

Disadvantage If we update a record and change the value of an indexed column in a clustered index, the database might need to move the entire row into a new position to keep the rows in sorted order. This behavior essentially turns an update query into a DELETE followed by an INSERT, with an obvious decrease in performance. A table's clustered index can often be found on the primary key or a foreign key column, because key values generally do not change once a record is inserted into the database.

Non Clustered Index

A book index stores words in order with a reference to the page numbers where the word is located. This type of index for a database is a nonclustered index. A non-clustered index stores all the index Key, plus a link(reference) to the actual full record on disk to find the data, which is more like a book index.

Non-Clustered indexes are all the other indexes on a table that do not dictate the order that records are stored on disk.

Advantage

We can have many non-clustered indexes, as apposed to just one clustered index per table.
Creating the proper index can drastically increase the performance of an application. If we create indexes on those column which is mostly used in WHERE clause, GROUP By clause we can improve the performance of the sql query.

Disadvantage

The disadvantage of a non-clustered index is that it is slightly slower than a clustered index and they can take up quite a bit of space on the disk.

Another disadvantage is using too many indexes can actually slow your database down. Thinking of a book again, imagine if every "the", "and" or "at" was included in the index. That would stop the index being useful - the index becomes as big as the text! On top of that, each time a page or database row is updated or removed, the reference or index also has to be updated.

How to create an Index

In a database create a table that looks like this: CREATE TABLE Products ( ProductID INT PRIMARY KEY, SupplierID INT, ProductName VARCHAR(255), UnitPrice VARCHAR(255) ); Having a data connection in the Server Explorer view of Visual Studio.NET allows us to easily create new indexes:

• Now 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.
• This should bring you to the following tabbed dialog box.
  
  
  
  http://www.dotnetfunda.com/UserFiles/ArticlesFiles/633613656009810540_Deysomnath_indexes_fig2.jpg The dialog is currently displaying an existing index on the Products table: the PK_Products index. Since the 'ProductID' in the Products table is a primary key, it is already indexed(clustered Index).
• 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 (Products), and the column to index (UnitPrice). CREATE INDEX [IDX_UnitPrice] ON Products (UnitPrice) To verify that the index is created, use the following stored procedure to see a list of all indexes on the Products table: EXEC sp_helpindex Customers To create a clustered index, simply select the Create As CLUSTERED checkbox in the dialog box we used above.

The SQL syntax for a clustered index simply adds a new keyword to the CREATE INDEX command, as shown below: CREATE CLUSTERED INDEX IDX_SupplierID ON Products(SupplierID) Since we can only have one clustered index per table, and the Products table already has a clustered index (PK_Products) on the primary key (ProductId), the above command should generate the following error: Cannot create more than one clustered index on table 'Products'. Drop the existing clustered index 'PK_Products' before creating another.

Conclusion

As a general rule of thumb, every table should have a clustered index. If you create only one index for a table, use a clustered index. Not only is a clustered index more efficient than other indexes for retrieval operations, a clustered index also helps the database efficiently manage the space required to store the table.