Add optional checks for more robust development - by Erland Sommarskog

Status : 


Sign in
to vote
ID 260762 Comments
Status Active Workarounds
Type Suggestion Repros 16
Opened 3/1/2007 12:21:55 PM
Duplicates 622699 Access Restriction Public


As programming environment SQL Server has shortcomings that are entirely
inappropriate for enterprise development. In a modern programming language,
the compile tries to catch as many errors as possible. The earlier errors
are found, the cheaper it is to fix them. And nothing is as cheap as a 
compiler slapping the error in your face. Errors normally not caught by
SQL Server when you create a T-SQL modules are:

o  If a query refers to a missing table, SQL Server is completely silent;
   you don't get the error until run-time. 
o  In fact, when query refers to a missing table, SQL Server fails to
   to report errors that are evident, even if we accept deferred name
   resolution: missing columns in other tables, use of aliases that are
   not declared etc.

o  When processing the CREATE PROCEDURE statement, SQL Server should consider
   (temp) tables created within the procedure as existing, and thus perform
   full checks with regards to existing columns.

o  These checks should also apply to indexes mentioned in index hints etc.

o  Calls to stored procedures are not checked for matching parameter profile.

o  T-SQL has too much of implcit conversions. There should be a mode where
   it is a compile-time error to mix two values of different classes of
   data types, particularly there should never be any implicit conversion
   in any direction between strings and numbers.

One can note that four of these points were actually implemented in SQL 6.5.

I also like to highlight an error of a different kind, that it would be
very nice if SQL Server could catch. Consider this query:

FROM   a
JOIN   b ON a.col = a.col

While syntactially legal, this query obviously suffers from a typo. It could
save several hours of testing and debugging to get a warning message
"Hey the ON clause that follows 'JOIN b' does not refer to that table,
are you sure you got it right?".

(Yes, I know I have brought it up before, but I could find it on Connect,
so I decided to submit a new entry.)
Sign in to post a comment.
Posted by R Herring on 2/15/2016 at 12:57 PM
First, I recommend the option have three levels, (ignore, warning, error).
Second, I recommend two separate options. The first option focused strictly on syntax related items. For example:
Prevent Select Into
Prevent implicit conversions, particularly in Join/Where clauses and parameter passing.
Require precision (i.e. Varchar(xx) versus Varchar)
Require ANSI syntax on joins
Prevent special characters in names (not even when quoted!)
The second option would focus on the trickier items such as deferred name resolution.
Posted by m60freeman on 5/20/2015 at 6:17 AM
The behavior should be more consistent with regard to missing objects. Currently, you can CREATE a PROCEDURE that contains a reference to a non-existent table, but get an error if it references a non-existent Linked Server. There should be an option for strict checking that would fail the create in both cases, and an option that will let both cases succeed.
Posted by Saffy on 4/29/2013 at 12:01 AM
Just been bitten by a bug where I had defined a stored procedure parameter as decimal where I meant to write money.
If I had been forced to specify a precision and scale for the decimal this would never have happened.
Posted by Jamie Thomson on 4/15/2010 at 9:48 AM
I raised a seperate issue around implicit coersion before I found this submission:

Deprecate implicit casting in WHERE predicates
Posted by DWalker on 8/5/2008 at 9:18 AM
It is terrible that sys.sql_dependencies pseudo-table does not get updated by any statements in stored procedures that reference temporary tables.

Because of this, I have a few stored procedures where SQL Server's Management Studio claims that each stored procedure has NO prerequisites (apparently, the stored procedure doesn't depend on anything else). This is hogwash, and very inconvenient. The fact that this failure of the dependency table and dependency GUI is not documented anywhere, is even worse.

If this suggestion (feedback item) were implemented, this problem could be resolved.


David Walker
Posted by Erland Sommarskog on 6/14/2008 at 1:46 PM
I developed this idea in an article on my web site. In this article I go into more depth how this feature should work with reagards to missing tables. I have also added more candidates for strict checks. The URL is:
Posted by navogel on 2/5/2008 at 1:44 PM
The option to disallow implicit conversions definitely gets my vote. I'd like to see a separate setting for that. Something like: ALTER DATABASE SET IMPLICT_CONVERSION OFF;
Posted by Sara [MSFT] on 1/30/2008 at 2:49 PM
Hello Erland

Thanks for sending in this feedback and for clearly listing the conditions for which detection at compile time would enhance the development experience. We are tracking these as potential enhancements to T-SQL in a future SQL Server release.

-- SQL Server Engine Team
Posted by Razvan Socol on 8/23/2007 at 11:00 PM
The last suggestion (regarding wrong join clauses) was also posted by me in Feedback ID 150032. You can close my suggestion as a duplicate of this one.