Add optional checks for more robust development

Add optional checks for more robust development by Erland Sommarskog

Active

149

Type:	Suggestion
ID:	260762
Opened:	3/1/2007 12:21:55 PM
Access Restriction:	Public
Duplicates:	622699

Workaround(s)

view

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:

SELECT ...
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.)

Details (expand)

English

SQL Engine

Adding these checks as mandatory would be breaking behaviour, and there
may be occasions where they are not desireable. Therefore there should
be an option SET STRICT_CHECKS ON. When this setting is in effect, processing
of CREATE PROCEDURE, CREATE TRIGGER etc would result in errors for the
conditions noted above. (In fact, it would be perfectly be OK to let it
suffice with warnings.) The warnings/errors would not be produced when
the module is compiled by the optimizer to construct a query plan.
There should be an option in Mgmt Studion to have this option on by
default.

I have seen the idea floated that WITH SCHEMABINDING would be added as an
option for CREATE PROCEDURE. While this can be worthwhile, it does *not*
solve the problems listed above. In practice, you do need the flexibiliy
of SQL Server: be able to drop and recreate a table. My aim here is not
to ensure that the database remains correct. My aim is to reduce
development time by finding stupid sloppy typiing errors early.

Faster Development

0 attachments

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
(https://connect.microsoft.com/SQLServer/feedback/details/551465/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.

Thanks.

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:
http://www.sommarskog.se/strict_checks.html

Posted by David Portas 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 Microsoft 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.

Razvan

Posted by andy912300 on 4/12/2007 at 8:17 AM

Use check execution plan. The only weakness is that you may need permissions to see the execution plan first