The 'IF'
ALWAYS include BEGIN and END with an IF statement, no matter how simple
IF 1=1 BEGIN
PRINT 'hello'
END
You may be tempted to take the lazy way out... but at some point someone might add a line below the PRINT statement and that will break the logic. Can you spot the bug here?
DECLARE @important INT = 1
IF 1=2
PRINT 'THIS WAS ADDED LATER'
SELECT @important = 2
SELECT @important AS IMPORTANT
What is the value of @important? What did the developer intend? If you always add BEGIN and END, you avoid this.
Also, it's harder to read if you don't. I have to spend hours formatting code, just to make sense of what it says. It may make sense to YOU, but I've got to read it later. This is clearer.
Formatting a SELECT statement
SELECT
m.FIRSTFIELD AS FIRSTFIELD,
SUM(m.NUMERICFIELD) AS TOTAL
INTO #TMP
FROM MYTABLE m
JOIN OTHERTABLE o ON o.FIRSTFIELD = m.FIRSTFIELD
LEFT JOIN THIRDTABLE t ON t.FIRSTFIELD = m.FIRSTFIELD
WHERE 1=1
GROUP BY m.FIRSTFIELD
HAVING SUM(m.NUMERICFIELD) > 0
ORDER BY 1
1. Only one KEYWORD touches the left margin. This way, you can more easily scan code and pick out statements.
2. The second indent is for the other 6 major clauses (INTO, FROM, WHERE, GROUP BY, HAVING, ORDER BY). This is important because if I'm reading through code to try and fix an issue or add an enhancement, I'm looking for 1) the right statement and 2) the correct position in that statement. This makes finding your elusive WHERE clause a little easier. And besides... clarity is its own reward.
3. The third indent is for the select fields, and the joins.
4. ONLY. EVER. Use JOIN and LEFT JOIN. I know, all you SQL geniuses have CROSS APPLYs and exceptions, but for your first 5 years coding you'll never do that. JOIN and LEFT JOIN. Only. I have to spend so much time simplifying code and retraining it gets to be tiring. If you follow this rule, your code will be simpler. Simpler is better.
<long exhale> I feel so much better now.