What is the use of indexes and what are the types of indexes available in SQL Server?

Indexes are used to find data quickly when a query is processed in any relational database. Indexes improve performance by 10 to 500 times.
Index can improve the performance in following operations:

• Find the records matching with WHERE clause

UPDATE Books SET Availability = 1 WHERE SubjectId =12
DELETE FROM Books WHERE Price <10
SELECT * FROM Books WHERE Price BETWEEN 50 AND 80

• Sorting the result with ORDER BY

SELECT * FROM Books ORDER BY Price DESC

• Grouping records and aggregate values

SELECT Count(*) as Units, Price FROM Books GROUP BY Price

There are two types of indexes available in SQL Server: clustered and non-clustered
Clustered index
Clustered index physically reorders the records of a table. Therefore a table can have only one clustered index. Usually a clustered index will be created on the primary key of a table.
Non – Clustered Index
Non – Clustered index are stored in the order of the index key values, but the information in the table is stored in a different order. Means logical sorting of data not Physical. In SQl Server 2005 a table can have 249 non-clustered indexes.
Composite Indexes
A composite index is an index on two or more columns. Both clustered and non-clustered indexes can be composite indexes. If you have composite index on Price and BookName then take can take advantage of it like this:
SELECT BookName, Price FROM Products ORDER BY UnitPrice BookName, Price DESC