Home Dashboard Directory Help

Add optional checks for more robust development by Erland Sommarskog


Status: 

Active


168
0
Sign in
to vote
Type: Suggestion
ID: 260762
Opened: 3/1/2007 12:21:55 PM
Access Restriction: Public
Duplicates: 622699
1
Workaround(s)
view

Description

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
Sign in to post a comment.
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 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 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
Sign in to post a workaround.
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