There has been some chatter in other threads about keywords that are "optional" or "not enforced" in the T-SQL syntax (and the absence of a STRICT mode).
I've got a few - I hate the fact that CONVERT(varchar, @MyGUID) defaults to a length which is too short, and I'd like to enforce "AS" in Select clause Alias assignments to avoid the accident of
SELECT Col1 -- I forgot the comma here
Col2 -- which makes this an Alias Name,
-- not a Column to include
I went off wandering the Internet with Mr Google and found an article
Ideas for SET STRICT_CHECKS ON by Erland Sommarskog, Its a massive article and a great piece of work.
My conclusion was that I wanted some, but not all, of the checks Erland listed and it reminded me that many decades ago I used to rely heavily on Lint, when programming in C, to find potential problems in my code.
So I am on the lookout for a version of Lint for SQL - do you use one? or know of one?
I found something that looks interesting (but I expect there are others, maybe better ones?) SQL Enlight by Ubitsoft Ltd which has code formatting / refactoring tools, but also Lint-style tools to "to detect and correct defects in managed code". There is an online cut&paste trial box, I stuck the contents of the SProc I was working on into it and got:
Message (Line & Column No.s removed for brevity)
1 SA0002 : Variable @strParameters declared but never used.
2 SA0004 : Variable @intXXXNo is re-assigned at line 152 without its previous value being used.
3 SA0011 : SELECT * in stored procedures, views and table-valued functions.
4 SA0029 : The input parameter @intSilent never used.
5 SA0035 : TODO,HACK or UNDONE phrase found in a comment.
6 SA0054 : Parameter @XXX_ID modification prior to use in a query, may negatively affect performance.
So I'm not quite the Rock Star coder that I thought I was and some dumb-ass machine is smarter than me - that always smarts!
#1 is definitely a bug, I'm grateful for that alone being spotted.
#5 is handy as I do indeed have a TODO comment in the code, and they tend to get left there forever whereas I ought to have occasional days where I "Fix a load of ToDos".
I think the rest are fine, (the SELECT * is in an Error Catch where I want to get all columns regardless of what shape the table might become, AND I want to keep the error-handling code as simple as possible)
List of the their Analysis Rules available is here: http://www.ubitsoft.com/products/sqlenlight-for-ssms/analysis-rules.php