Indexes are very important when it comes to design your databases for better performance.
In this article we will look into what are indexes types we have in SQL Server 2008.
In SQL Server you have two types of indexes.
- Clustered Index (CLI)
Clustered index is the actual table/view sorted in order of the cluster key. Because of this you can have one CLI per table/view. CLI analogies to an index of a typical telephone directory where you search by the name and there it self you find other details like address and telephone number in which you do not have to do a another lookup.
2. Non-Clustered Index (NCI)
A non-clustered index has the indexed columns and a pointer or bookmark pointing to the actual row. You can create 999 NCIs which is increased from 249 in SQL Server 2005. NCI analogies to index you have in the back side of a book. If you want find out something faster rather than reading the entire book, you will navigate to the index, then get the topic you need. There you have the page number (index pointer) from which you can do a lookup to the data table to find out necessary information you need.
In both indexes, there are two limitations.
- You cannot create indexes with more than 16 columns.
- You cannot have indexes with more than 900 bytes.
However, there is a workaround which came along with SQL Server 2005. That is you can Create an index with include statement. Above two limitation will not cover for the columns you are having in the include statement.
Let us say we have there columns like this.
If you create an index for this you will get an error message as following.
Warning! The maximum key length is 900 bytes. The index 'IX_Title_Name_Passport' has maximum length of 910 bytes. For some combination of large values, the insert/update operation will fail.
So you will not be able to insert/update data if the above three columns has value more than 900 bytes.
To avoid this you can create an index with include.
CREATE NONCLUSTERED INDEX [IX_Title_Passport_Include_Name]
Title ,Passport ASC
Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes. Filtered indexes are available only with SQL Server 2008. This is how you create a filtered index.
CREATE NONCLUSTERED INDEX NCI_Department
WHERE Title= 'Marketing Manager'
You can have any where clause for filtered index.
This is just an overview of indexes we have in SQL Server 2008. In future articles we will see what are the usage of indexes.