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

Worst Practices - Not Using Primary Keys and Clustered Indexes

By Andy Warren, (first published: 2003/11/10)

This is the third in my series of articles on Worst Practices (see Worst Practices - Part 1 of a Very Long Series! and  Worst Practices - Objects Not Owned by DBO) that so far has generated quite a bit of response from readers. Not everyone agrees with me so far, but it looks like I'm not the only one that sees a lot of "bad" practices in production environments.

So, let's talk about another worst practice - not putting a primary key on EVERY table and failing to use a clustered key on EVERY table.

I think 9 times out of 10, this just happens by mistake. If you build your tables in Enterprise Manager and click the primary key icon on the toolbar, it makes the column both a primary key and the clustered index. This is not necessarily the best use of a clustered index, but it's better than nothing. If you build your tables in Query Analyzer, it's up to you to either execute the TSQL to create the primary key and clustered index (or switch to EM to do that part).

It's up to you, the DBA or senior developer, to catch those mistakes and get them fixed before they make it into production. It's a great opportunity to make sure that your developers understand how important these issues are - I think sometimes we fall into the trap of thinking that everyone KNOWS why it's important. Don't assume, ask. If they knew better and just forgot to do it, a gentle reminder to stay sharp is worthwhile. If they didn't know better - isn't it time they learned?

Where it gets interesting (to me anyway!) is when someone tries to convince me they don't need a primary key. Come on - having a primary key is such a core concept, why would you not have one? I've heard a couple arguments, let's see if they have any merit.

The first is that because the table is so small, the index will not be used in the query plan. Quite possibly true. So what? One, you're making the assumption the table will always be small, what happens when it grows? Will the developer (or DBA) remember "Hey, I need to add an index to that table"? More likely it will get fixed only at the point that a performance problem arises. To the extent that I can, I like to build it right the first time. Why have to fix a problem later that I can avoid now? How much time does it take to create a primary key and a clustered index compared to figuring out later why you have performance issues? More important than that though - it's not just about query plans, it's about data integrity. Not having a primary key can provide a fatal failure point. Sure, you know not to add a duplicate to the lookup table, but what would happen if someone did? How long would it take to figure it out?

Another is that it's a temp table, so the rules don't really apply. More than the first argument, I think there is some validity to this position. Not a lot, but some! I think the most compelling reason I have in favor of this argument is that in many places where you use a temp table now you could (and probably should) use a variable of type table (available beginning in SQL 2000) which only allows you to define the structure, not indexes. Just remember this - you'll never wrong go by creating a primary key on a table, temp or not.

Creating a primary key (and/or clustered index) is unnecessary overhead for many situations such as temp tables, lookup tables, even history/audit tables. Bull. Yes, every index, every constraint, every default, every trigger, adds some overhead. Should we seek to minimize that overhead in our design? Always. But never at the expense of data integrity. If your system is loaded to the point that you're worried about the overhead of having a primary key on a table you're in deep trouble. The nice thing about primary keys and clustered indexes is you can only have one of each per table - pretty hard to over use them!

Finally, there is the "I don't need a clustered index". A good example of this might be a lookup table that contains state name and state abbreviation. You're going to be doing single record lookups, never a range query which is where a clustered index excels. You're also not going to have a lot of indexes, probably one on the name and one on the abbreviation where the index will provide the data, no need to do a seek through the table to find other bits of information associated with that row. For this example, is it a fair point? I think so. On the other hand, how much overhead would adding the clustered index impose? It's not getting updated, the data IS the index, so the cost is very very low.

My recommendation to junior developers is pretty simple. Every table gets both a primary key and a clustered index. It may not be a "best" practice, but a damn good starting point is to just add a identity column called rowid and make it the primary key and the clustered key. As your skills increase you can start to look at more advanced alternatives. 

I'd like to thank the readers who took the time to add a comment about my first two articles. Those comments, both the ones who agree as well as disagree, provide a valuable extension to all the articles posted here on SQLServerCentral.com. Especially for new developers and DBA's, there are a lot of differing opinions on issues like these and it's hard to know which is right, so the chance to see what other people think about the content is really helpful. Whether you agree or disagree, how about telling everyone why? Click the 'Your Opinion' tab below and tell us what you think!

Total article views: 66037 | Views in the last 30 days: 18
Related Articles

Primary key without cluster index

can i create primary key on table without cluster index?


Cluster That Index!

Quite a while back Andy wrote an article about why not using primary keys and clustered indexes was ...


Creating a Primary Key over an existing Unique, Clustered Index

Primary Key, Clustered Index, Modification


clustered index

clustered index


Primary Key vs Clustered Index - Very newbie question

I have a primary key - is it also a clustered index?

database design    
sql server 7