How many types of indexes in sql server 2005,2008:

Why do we  use  indexes:

 To get the high performence index are used in sql server database.Indexes can use to get the information quickly,Like we used indexes in books to get information quickly.Indexes are created on columns in tables or view.The index provides a fast way to look up data in table and database.Index creation in table helps us to query optimization and faster access of data.

Types of Indexes:

There are different  types of indexes you can create in sql server database.The indexes are given below:

1:Clustered

2:Nonclustered

3:Unique

4:Index with included columns

5:Indexed views

 

1:Clustered Index:

A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key.We can have only 1 clustered index in a table.Clustered index is applied on primary key.In case of clustered index , indexed values are sorted in either ascending or descending order.

2: Nonclustered Index:

A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator.We can have max 249 Nonclustered index in a table.

3:Unique Index:

A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique.

Both clustered and nonclustered indexes can be unique.

4:Index with included columns:

A nonclustered index that is extended to include nonkey columns in addition to the key columns.

5:Indexed views:

A index in view is called Indexed view.We can add index on view.The view and result set is permanently stored in a unique clustered index in the same way a table with a clustered index is stored. Nonclustered indexes on the view can be added after the clustered index is created.