SQL Server is Smarter Than You

I had a brief email discussion recently, which could be effectively be boiled down to:

• Them: “Are foreign key constraints more performant than referential integrity triggers?”
• Me: “Yes.” *stomp*stomp*stomp*stomp* “Yes, they are.”

Let’s start with a generic statement, and get more specific. Generally speaking: Don’t try to circumvent SQL Server’s primary features. This means that in 95-100% of cases, you shouldn’t try to get around foreign keys by using triggers, or a bad query plan by using index or lock hints, or cursors by using loops.  These kinds of strategies are ineffectual at best, and crippling at their worst.

Now let’s get back on the triggers-vs-foreign keys talk.

There is absolutely a performance gain in using foreign key constraints instead of triggers.  I’m going to beat you over the head with MSDN’s “Chapter 14 — Improving SQL Server Performance”, specifically the “Define All Primary Keys and Foreign Key Relationships” section, which says it outright: “Declarative referential integrity (DRI) performs better than triggers do…”

How? Let me count the ways…

1. Maintainability and readability: “Declarative referential integrity (DRI) … is easier to maintain and troubleshoot than triggers are.”  I can second that. And yes, I say that maintainability and readability can easily be counted toward “performing better” in this context, because performance isn’t limited to how fast a single query comes back. Performance can also be “how easily can this thing be fixed or improved when it needs it”.
2. Overhead: Triggers cause additional reads and writes, and (varying by what’s coded into it) additional overhead.  “DRI is checked by the server before the server performs the actual data modification request. When you use triggers, the data modification requests are inserted in the Inserted and Deleted temporary system tables, and the trigger code is run. Depending on the trigger code, the final modifications are then made or not made”.
3. Optimization: FK constraints allow the SQL Server optimizer to form better execution plans…joins on foreign key columns (the most common kind) are a signal to the optimizer to make use of appropriate indexes. Triggers won’t do that for you. “Primary keys and foreign key relationships that are correctly defined help ensure that you can write optimal queries. “
4. Bonus: There are additional costs to using triggers over FKs, e.g. “One common result of incorrect relationships is having to add DISTINCT clauses to eliminate redundant data from result sets.”

I entitled this blog “SQL Server is Smarter Than You”, and again, I belive that’s true most of the time. Every so often we’re smarter than SQL, but absolutely everyone – from the experienced SQL developer down to the first-day accidental DBA – take that idea as license to monkey around with objects, features, and settings without actually reading or testing for performance and scalability.

Use the pretty features that Microsoft hath provided you. If you’re going to monkey around, you’d better have the experience and education to back it up, or else your draggy code will be the bane of systems and users everywhere.

You have been warned.

-Jen McCown, http://www.MidnightDBA.com/Jen