Index optimization is always interesting subject to me. Every time I receive requests to help optimize query or query on any specific table. I always ask Jr.DBA to go over following list first before I take a look at it. Most of the time the Query Speed is optimized just following basic rules mentioned below. Once following checklist applied interesting optimization part begins which only experiment and experience can resolve.
SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table
SQL SERVER – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index
Clustered Index Only 1 allowed per table Physically rearranges the data in the table to conform to the index constraints.
Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers.