Steve and Jeff, thank you. Good to have other eyes on this. 🙂 What should I do with the earlier incorrect code? Wipe it out? Should I have kept updating it? Idk.
Scott, ok coding conventions lol. To the extent iif is more compact it represents a normalization of information and therefore it would be impermissible for me to not make use of it. Also, regarding the upper casing of key words -- the collation applied to Sql Server sql code is case insensitive. Therefore, in my (not so serious) opinion, it should be up to the Capitalizers to explain their fruitless pursuit. According to my coding convention anything that's optional should be left out. Besides capitalizing key words things you never see me do:
"insert into" -- INTO is optional and therefore should be left out
"inner join" -- INNER is optional and therefore should be left out
"left outer join/right outer join" OUTER is optional and therefore should be left out
"select col1 as colname" -- AS is optional and should be left out. Alternate syntax like "group =" is ridiculous and not necessary.
🙂 Everybody has their way.
I prefer to always use INNER and OUTER for clarity.
However, INNER should always be used for a different reason: if you ever need to add a hint to the query, the INNER must be present ... so maybe it's not "optional" after all. Try parsing:
SELECT * FROM dbo.table1 t1 INNER HASH JOIN dbo.table2 t2 ON t1.id = t2.id
SELECT * FROM dbo.table1 t1 HASH JOIN dbo.table2 t2 ON t1.id = t2.id
The WITH before NOLOCK used to be optional, now it's not. The parentheses around a number after TOP are optional, but may soon not be.
I deeply disagree with the idea of automatically leaving out everything that's optional ... well, currently optional.
SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."