Explain Indexes in SQL Server

Indexes
Indexes in databases are very similar to indexes in libraries. Indexes allow locating information within a database fast, much like they do in libraries. If all books in a library are indexed alphabetically then you don’t need to browse the whole library to find particular book. Instead you’ll simply get the first letter from the book title and you’ll find this letter’s section in the library starting your search from there, which will narrow down your search significantly.

An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order. The table index has pointers to the values stored in specified column or combination of columns of the table. These pointers are ordered depending on the sort order specified in the index.

Here is how to use CREATE INDEX SQL statement to create an index on column Model in the Product table, called idxModel:

CREATE INDEX idxModel
ON Product (Model)

The syntax for creating indexes varies greatly amongst different RDBMS, that’s why we will not discuss this matter further.

There are some general rules which describe when to use indexes. When dealing with relatively small tables, indexes do not improve performance. In general indexes improve performance when they are created on fields used in table joins. Use indexes when most of your database queries retrieve relatively small datasets, because if your queries retrieve most of the data most of the time, the indexes will actually slow the data retrieval. Use indexes for columns that have many different values (there are not many repeated values within the column). Although indexes improve search performance, they slow the updates, and this might be something worth considering.

Types of Index
1. Cluster Index
2. Non Cluster Index

Cluster 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; only the index key and a reference are stored. In contrast, a common analogy for a clustered index is a phone book. A phone book still sorts entries into alphabetical order. The difference is, 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 database will sort the table's records according to the column (or columns) specified by the index. 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. The nonclustered indexes created earlier in the chapter contain only the index key and a reference to find the data, which is more like a book index. You can only create one clustered index on each table.
The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. If a table has no clustered index, its data rows are stored in a heap.

Example:
CREATE CLUSTERED INDEX IDX_SupplierID ON Products(SupplierID)

Non Cluster Index
A non-clustered index has the indexed columns and a pointer or bookmark pointing to the actual row. In the case of our example it contains a page number. Another example could be a search done on Google or another of the search engines. The results on the page contain links to the original web pages. The thing to remember about non-clustered indexes is that you may have to retrieve part of the required information from the rows in the table. When using a book index, you will probably have to turn to the page of the book. When searching on Google, you will probably have to click the link to view the original page. If all of the information you need is included in the index, you have no need to visit the actual data.

Nonclustered indexes have a structure completely separate from the data rows. The lowest rows of a nonclustered index contain the nonclustered index key values and each key value entry has pointers to the data rows containing the key value. The data rows are not stored in order based on the nonclustered key.

The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or are clustered. For a heap, a row locator is a pointer to the row. For a table with a clustered index, the row locator is the clustered index key.

Example:
CREATE NONCLUSTERED INDEX sample_nonclust ON emp_sample(emp_name)

Advantages of Indexes
1. Searching For Records
2. Sorting Records
3. Grouping Records
4. Maintaining a Unique Column

Disadvantages of Indexes
1. Disk Space
2. Data Modification

Few Examples:
EXEC sp_helpindex EMPNAME

CREATE CLUSTERED INDEX IDX_SupplierID ON Products(SupplierID)

CREATE UNIQUE CLUSTERED INDEX PK_Order_Details
ON [Order Details] (OrderID, ProductID)

CREATE INDEX IX_UnitPrice_ProductName ON Products(UnitPrice, ProductName)
EXEC sp_rename 'Products.IX_UnitPrice', 'IX_Price'

DROP Index Products.IX_Price