5 Things to Know About Indexes for SQL Server

, 2014-06-25 (first published: )

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]
    PRIMARY KEY CLUSTERED
  ( [AddressID] ASC )
  ON [PRIMARY]
GO

 

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.

CREATE NONCLUSTERED INDEX
      [idxAddress_AddressLine12CistyStateZip]
    ON [Person].[Address]
( [AddressLine1] ASC, [AddressLine2] ASC, [City] ASC,
     [StateProvinceID] ASC, [PostalCode] ASC)
ON [PRIMARY]

 

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.

CREATE NONCLUSTERED INDEX
      [idxAddress_State_IncludeColumns]
    ON [Person].[Address]
( [StateProvinceID] ASC)
INCLUDE ([AddressLine1], [AddressLine2], [City], [PostalCode])
ON [PRIMARY]

 

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.

CREATE NONCLUSTERED INDEX
      [idxAddress_State_Filtered]
    ON [Person].[Address]
    (AddressLine1, [AddressLine2], [City], [PostalCode])
  WHERE [StateCode] = 'LA'
ON [PRIMARY]

 

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.

image

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads