When Michael J. Swart (Blog | Twitter) announced this week’s T-SQL Tuesday topic, Indexes, I thought,  “Hey, maybe I could write about that!”  I’ve been reading other folks’ T-SQL Tuesday entries, just sitting on the sidelines and watching everybody else have all the fun.  Not this time!

So, in no particular order, are my top ten worst indexing practices:

1. Not considering the best column for the clustered index.

Too often, when building a new table, the developer will simply make the primary key the clustered index. But there are many good reasons to make another column(s) the clustered index. For example, if the table has a datetime column and that select queries will be looking for a range of dates, then that column should be part of the clustered index. Additionally, there are other columns you want to avoid putting in your clustered index, such as columns that are constantly updated or columns that are very wide. Since you can create only one clustered index per table, this decision deserves some thought before you decide on which column(s) to set as your clustered index. If you can’t think of a good reason to make another column(s) the clustered index, by all means keep the primary key the clustered index, but make sure you have a clustered index, which leads to the next worst practice…

2. No clustered index

A table without a clustered index is called a heap, which is just an unordered list of data. So, why is this bad? Well, as rows increase in size they are moved to new rows, creating what are called forward pointers. Kalen Delaney explains this very well in her blog post: Fragmentation and Forwarded Records in a Heap. So what about very small tables with static data, such as lookup tables? Well, yes, they don’t really need a clustered index, but I’d rather just put one on every table, so there is no chance of any table having this problem.

3. Incorrect fill factor for the index.

The default fill factor is 0, which means that the leaf page for the index will be full.  If your data is static, this is good, but inserts will cause page splits, which in a database with high write activity can degrade performance.  Choose a fill factor based on minimizing page splits.

4. Forgetting that foreign key constraints do not create indexes by default.

When you create a Foreign Key constraint, SQL Server does not include an index. If you need the index, you must create it. Review all Foreign Keys to determine if they will benefit from an Index.

5. Forgetting to show indexes some love.

Ok, DBA’s this one is for you. After all that coding and testing to find the best indexes, you forget to schedule the proper maintenance for them. Indexes won’t give the best performance if they are allowed to become excessively fragmented. And keep those statistics updated too, will ya! I’ll make a deal with you, Mr(s). DBA, you keep my indexes nice and tidy and I won’t be asking for SA rights on production. Deal?

6. Creating lots of indexes without a real reason why.

Once you see the performance benefits of indexes, it’s easy to fall in love with them and think the more the better. No, this is not the case. Too many indexes can drag down insert, update and delete operations and led to wasted resources and time during maintenance operations. And your DBA will be cursing you…oh yes they will.

7. Low selectivity of an Index.

If there are only a few unique values in a column, a nonclustered index will not be used. So, just how low is too low? Tibor Karaszi explains all in his post How Selective Do We Need to be for an Index to be used?

8. Use of index hints.

Yea, they do work. For a while. For an indeterminate amount of time. And the time they stop working, will be so, so inconvenient. You will be buried in work and users will be hollowing for you to fix it. Immediately. Trust me, I’ve been there, it’s ugly. Please, find some other way to fix your troubled query.

9. Non-sargable where clauses.

If you create a WHERE clause that includes search conditions such as <>, !=, NOT EXISTS, NOT IN, or math operations or functions, it will not use an index. For an deeper explanation, see Sargable Functions in SQL Server by Rob Farley or Chapter 11 in the book SQL Server 2008 Query Performance Tuning Distilled by Grant Fritchey and Sajal Dam.

10. No naming standards for your indexes.

What does “IDX_1”, “Index5, or “IX_Customers” mean? Please, put just a little bit of thought into your index naming. I like to see the table name and column(s) names in the in index name. Then I don’t have to script out the index just to see what’s in it. The next time you are tempted to put a useless name on an index, ask yourself, “Will I remember what columns are in this index 6 months from now?”

Got any other indexing worst practices you would like to share?  Leave ‘em in the comments!

P.S.:  Michael, I am team Indexes!