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