SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

5 Things to Know About Indexes for SQL Server

One of the first questions I got while interviewing for a potential position as a DBA was “If there is performance problem, where is the first place you would look?”

Now at this time, I was trying to move from Software Developer to DBA, and I answered that I would look at IO, Network and errors in code. The interviewer said, “Indexes.” I always assumed that all database designers knew you needed good indexes. This changed my thinking about what I was learning to start reading more about being a DBA. SQLServerCentral.Com became my friend and I started reading one article a day and answering the Question of the Day every morning when I got to work.

1. Clustered Index

What is a clustered index? Or better yet, what do I need to know about a clustered index? The first thing to know is that you can have only one. The logical structure of the table becomes the order of the column(s) you use in a clustered index. You might hear someone say that the primary key is always the clustered index. This is not true. Any index can be the clustered index. By default, the primary key becomes the clustered index unless you change this in a script or the User Interface creating/modifying a table.

ALTER TABLE [Person].[Address]
    ADD  CONSTRAINT [PK_Address_AddressID]
  ( [AddressID] ASC )


2. Non-Clustered Index

You can have more than one non-clustered index. I would suggest creating a clustered index before creating any non-clustered indexes. The clustered index column(s) become part of the non-clustered index. A query plan might need to go to the clustered index to retrieve more columns not available in the non-clustered index after a scan or search of non-clustered index. The non-clustered index can be one or more columns, and the order is important. You want the most specific column (as far as values go) as the first column. It also important to have the first column(s) to be used in the join or where clause that you are trying to be used for a query.

    ON [Person].[Address]
( [AddressLine1] ASC, [AddressLine2] ASC, [City] ASC,
     [StateProvinceID] ASC, [PostalCode] ASC)


3. Include Columns (i.e. Covering Index)

This is where Microsoft started shining in my opinion. The include columns in a non-clustered index can be these columns that your query is trying to retrieve from the clustered index mentioned above. Instead of retrieving more columns from the clustered index, the include columns are the ones the query needs. The are stored in the Data Page of the non-clustered index and not in the index tree.  Be careful not to go overboard with include columns, because you can eventually create a whole new table (even though it is an index) by including all the columns not specified in the index itself. I have also seen a form of Deadlock with Include Column indexes.

    ON [Person].[Address]
( [StateProvinceID] ASC)
INCLUDE ([AddressLine1], [AddressLine2], [City], [PostalCode])


4. Filtered Indexes

Filtered Indexes should be used carefully because they could become more of a performance problem with Insert/Update/Delete statements. They are really nice for large data warehouses where some tables are not structure. Also, I have seen them used for a column that has one value other than a empty value. Filtered indexes are new to SQL Server, so you should see performance improvements in future versions.

    ON [Person].[Address]
    (AddressLine1, [AddressLine2], [City], [PostalCode])
  WHERE [StateCode] = 'LA'


5. Missing Index Feature

Now, this is really cool. With SQL Server 2008, the Query Plan now includes a Missing Index feature which basically gives you a hint on what index might be beneficial. Again, you should be cautious when it suggests include columns that look like all the columns in the table. See number 3 above.


In conclusion, indexes are a great help with query performance. There are some basics shown above to get you started with understanding indexes. Do not close your mind to more information about indexes after you start to understand what was mentioned in this article. I know today after doing DBA work for over 10 years, there is always more to learn.

The Smiling DBA

Thomas LeBlanc is a Business Intelligence Consultant/Data Warehouse Architect in Baton Rouge, Louisiana. He uses his 25+ years in IT to help develop OLTP systems with normalized databases for high-performing T-SQL and end-to-end dimensional data marts using SSIS, SSAS, PPS, and Excel. His SQL Server certifications include MCSA 12, MCITP 08 BI and DBA, MCITP 2005 DBA, and MCDBA 2000. As a PASS volunteer, he is current chair of the Excel BI virtual chapter, past chair of the Data Architecture virtual chapter, and past virtual chapter mentor. He has helped the Baton Rouge SQL Server User Group with SQLSaturdays and speaks at local IT meetings.


Leave a comment on the original post [thesmilingdba.blogspot.com, opens in a new window]

Loading comments...