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!