SQL Server INDEXES

INDEXES:

• Clustered indexes are usually faster than non-clustered indexes (one could come very close to saying always, but there are exceptions).
• Only place non-clustered indexes on columns where you are going to get a high level of selectivity(that is, 95 percent or more of the rows are unique)
• All DML statements (INSERT, DELETE, UPDATE) and SELECT statement can benefit from indexes, but insert, deletes, and updates (remember they use a delete and insert approach) are slowed by indexes. The lookup part of a query is helped by the index, but any thing modifies data will have extra work to do (to maintain the index in addition to the actual data).
• Indexes take up space.
• Indexes are used only if the first column in the index is relevant to your query.
• Indexes can hurt as they help – know why you’re building the index, and don’t build indexes you don’t need.
• Indexes can provide structured data performance to your unstructured XML data, but keep in mind that, like other indexes, there is overhead involved.
• When you’re thinking about indexes, ask yourself, these questions:

Questions	Response
Are there a lot of inserts or modifications to this table?	If yes, keep indexes to a minimum. This kind of table usually has modifications done through single record lookups of the primary key – usually, this is the only index you want on the table. If the inserts are non-sequential, think about not having a clustered index.
Is this a reporting table? That is, not many insert, but reports run lots of different ways?	More indexes are fine. Target the clustered index to frequently used information that is likely to be extracted in ranges. OLAP installations will often have many times the number of indexes seen in an OLTP environment.
Is there a high level of selectivity on the data?	If Yes, and it is frequently the target of a WHERE clause, then add that index.
Have I dropped the indexes I no longer need?	If not, why not?
Do I have a maintenance strategy established?	If not, why not?

References sites:

http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1239952,00.html

http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

http://tonesdotnetblog.wordpress.com/2008/05/26/twelve-tips-for-optimising-sql-server-2005-queries/

http://blog.sqlauthority.com/2009/02/07/sql-server-introduction-to-force-index-query-hints-index-hint/ -- Forced indexes.

http://blog.decaresystems.ie/index.php/2007/07/11/index-usage-in-sql-server-2005/ -- Index usage

http://databases.about.com/od/sqlserver/a/indextuning.htm

http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx

http://www.sqlteam.com/article/sql-server-indexes-the-basics

Disabling Indexes:

http://msdn.microsoft.com/en-us/library/ms177406.aspx