Richard Swinbank is a rule-breaker:
When I started out writing T-SQL, I learned rules – things I should do, things to avoid – from other, more experienced developers. For example, when writing stored procedures:
– don’t use dynamic SQL
– don’t set variables wih
SELECT– never use
WITH RECOMPILEThere are often great reasons for these. Dynamic SQL can leave you prone to SQL injection,
SELECTing into variables can have unexpected effects, andRECOMPILEcan bring an OLTP system to its knees.
I think most rules of this sort tend to fit into something like four categories: convenience, take caution, observe in the breech, or shibboleths. Some rules, like formatting rules, are for convenience, whether that be for yourself or others. Not using dynamic SQL is a cautionary rule, for the reasons Richard mentions. The final two categories are variants on rules which exist to show that you are one of us, with observation in the breech being rules that we say we follow but only do when others are looking, and shibboleths being rules we follow regardless of whether people are looking. Formatting might fit into this as well, or having rules prohibiting (or enforcing) use of subqueries in the SELECT clause.
Very rarely are prohibitional rules 100% applicable, though as Richard notes, rules are a very useful shorthand.