When I first saw this question I thought to myself, “Self. Don’t you think that’s comparing apples to hammers? Yes, Self, I’m pretty sure it is. Good, I thought so too, self. Yeah, me too.” After rebooting because of the runaway iterations on that thought, I had another, “Well… hold on there self. Both types of objects, while pretty different, are taken into account by the query optimizer.” I then had to admit to myself that I had a point. So the question remains, are foreign keys better than indexes?
As my first self said, these are different objects and they fulfill different purposes within SQL Server. My second self wants to point out that when you’re dealing with functional objects within SQL Server, it’s a bad habit to start to equate one to the other, especially when they have such radically different functions. I, myself, know that an index is meant to be a mechanism to speed the retrieval of data. While a foreign key is meant to constrain data in a relational fashion between two tables enforcing that the data in one table can only contain data from the primary key (or a unique constraint) from the other table. But, my other self points out, indexes also work as constraints. As mentioned, you have a primary key, which is realized as an index on the table (clustered by default, but they don’t have to be). And, in addition to the primary key, you can have a unique constraint, also realized as an index on the table. So these constructs are similar to foreign keys. But, they’re very different. A foreign key simply validates, prior to allowing the data to get added to the table in question, that the values being added (or modified) match values in the key. That’s it. Indexes, unique or not, are a secondary storage mechanism within SQL Server. Clustered indexes actually are the table. They have the structure of an index, the balanced tree, or b-tree AND, at the leaf level, they have all the data that defines the table. Non-clustered indexes are sort of the same. They have a b-tree, just the same, and they have leaf levels that might have columns that are INCLUDEd in their storage.
Within the optimizer, assuming your foreign key is enforced, meaning it is created WITH CHECK or a check is run after it is created, then the optimizer can take advantage of the knowledge that data within the foreign key must match data within the parent table. This allows for better optimization of joins, even elimination of joins (see an example of this here). But, that’s not the same as what the optimizer does with indexes. Again, this is where the data is, so the optimizer is going to make a great deal of use of indexes in retrieving data in an optimal fashion, simply because that’s where the data is located (or, can be found if we’re talking key lookups and stuff like that).
So, while comparing apples & hammers, uh, I mean, foreign keys and indexes, you do hit the concept of constraints for both, the mechanisms associated with indexes are very different from those associated with the foreign key because they have storage while the foreign key does not.
I think it’s great to encounter a lucid explanation of this. I’ve wondered about it myself, and until now, found nothing helpful anywhere, just responses to questions in forums stating that they’re almost the same, rather than any explanations of the actual differences.
So, thank you once again, Grant. Current posts or older discoveries in archives, I always learn something when your name is in the masthead.
Thank you very much for the kind words. I’m glad this stuff is helpful. That’s the whole reason I put it out there.