Getting up to Speed with Indexes in SQL Server
by Saghir Taj
Getting a query to work is one object, but getting a query to work rapidly when the clients are piling up is another. An Index is a way that any DBMS uses to access data quickly, think about a book which consists of more then 5000 pages and some one asks you to find some particular topic or word, why you would turn to index? The index in the book is very similar to what a DBMS Index does. In a book you find the page number of that particular topic and in case of database you find a pointer (a physical storage address) of that particular record(s) which contains that data.
Poor use of indexes is doubtless the single most important cause of performance problem. An index is a small sorted list that points to the main data. Finding a particular record is then quicker because only the small index list has to be searched.
In technical terms, a table without an index is called a “heap”.
INDEXES TYPES
Clustered: A clustered index is one in which the index entries are actually data records. So when the database searches for an entry, it will end up with the actual data a lot faster. This means that the index/data entries have to be physically stored on the disk in a manner that aids retrieval -- in most cases, this is in sequential order. Only one clustered index can be created per table.
Non-Clustered: Accept cluster-index all other index types are called Non-clustered
Full Text: Full-text indexes differ from regular SQL indexes in a number of ways. Full-Text Index provides efficient support for sophisticated word searches in character string data. The full-text index stores information about significant words and their location within a given column. This information is used to quickly complete full-text queries that search for rows with particular words or combinations of words.
Unique Index: Unique constraint is independent of Indexation but however some DBMS referred it as Unique Index, although index only help or give support to the process of keeping attributes unique.
WHERE ARE INDEXES USED?
The most common use of an index is to retrieve rows that match a condition in WHERE clause, indexes also support other aggregate functions e.g. when we search for MIN() or MAX() DBMS only need to look at the first or last values in the stored index, Another case where indexes are useful is where you ORDER BY a field.
WHAT IS COLLATION?
A collation is a set of rules for comparing character-string (CHAR/VARCHAR/CLOB) values. Other terms for the same thing are sort order and linguistic comparison, but "collation" is the official SQL: 1999 word. Collations are especially important in search conditions or sorts. For more detailed description of collation visit http://www.dbnest.com/contents.php?cid=54
BEST PRACTICES:
1.Every index increases the time for INSERTS, UPDATES and DELETES, so the number of indexes should carefully considered so as not to have unnecessary indexes.
2.Try to create indexes on columns that have integer values rather than character values.
3.If you create a composite (multi-column) index, the orders of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.
4.If your application will be performing the same query over and over on the same table, consider creating a covering (Clustered) index on the table.
5.Consider creating index on column(s) frequently used in the WHERE, ORDER BY, and GROUP BY clauses. These column(s) are best candidates for index creating. You should analyze your queries very attentively to avoid creating not useful indexes.
RECOMMENDED LINKS
http://www.mssqlcity.com/Tips/tipInd.htm
http://www.windowsitpro.com/SQLServer/Article/ArticleID/43939/43939.html
http://infohost.nmt.edu/tcc/help/db/sybase/nonclustered.html
