SQL Server - Indexes

Relational databases like SQL Server use indexes to find data quickly when a query is processed. 
Creating and removing indexes from a database schema will rarely result in changes to an application's code; indexes operate 'behind the scenes' in support of the database engine. 
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 INSERT 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 INSERT 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 INSERT statements. This approach produces the same results as above, but with tremendous savings in time.

Syntax:
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 

How It 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.

Advantage of Indexes:

Searching For Records
The most obvious use for an index is in finding a record or set of records matching a WHERE clause. Indexes can aid queries looking for values inside of a range (as we demonstrated earlier), as well as queries looking for a specific value. By way of example, the following queries can all benefit from an index on UnitPrice:
DELETE FROM Products WHERE UnitPrice = 1 
UPDATE Products SET Discontinued = 1 WHERE UnitPrice > 15 
SELECT * FROM PRODUCTS WHERE UnitPrice BETWEEN 14 AND 16 
Indexes work just as well when searching for a record in DELETE and UPDATE commands as they do for SELECT statements.

Sorting Records
SELECT * FROM Products ORDER BY UnitPrice ASC 

Grouping Records
SELECT Count(*), UnitPrice FROM Products GROUP BY UnitPrice

Maintaining a Unique Column
CREATE UNIQUE INDEX IDX_ProductName On Products (ProductName)

Index Drawbacks

1. Indexes are stored on the disk, and the amount of space required will depend on the size of the table,
2. Another downside to using an index is the performance implication on data modification statements. Any time a query modifies the data in a table (INSERT, UPDATE, or DELETE), the database needs to update all of the indexes where data has changed.