Add optional checks for more robust development
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.)
Upvotes: 201<=-=Aug 23 2007 11:00PM=-=>
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<=-=Jan 30 2008 2:49PM=-=>
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<=-=Feb 5 2008 1:44PM=-=>
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;<=-=Jun 14 2008 1:46PM=-=>
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:
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<=-=Apr 15 2010 9:48AM=-=>
I raised a seperate issue around implicit coersion before I found this submission:
Deprecate implicit casting in WHERE predicates
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.
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.<=-=Feb 15 2016 12:57PM=-=>
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.
(From Elrand’s article) I don’t like the idea of forcing ordering for a “select top”. I would envision setting all my SSMS sessions to have strict checking as my personal default, but select top without an order by is commonly part of my workflow under two scenarios:
1) I legitimately don’t care about the order. I just want to see some arbitrary data to get a quick feel for how the table looks.
2) I am using select top 0 to pull schema information.
I admit that #2 could be resolved by removing this check when using top 0, but I hate the idea of adding arbitrary-feeling special cases to a strict checker.
Another example, where SQL implicitly rounds something, where I would prefer it to complain that it hasn't been given the correct type.
CREATE PROCEDURE MyTest (@MyParam DECIMAL(9,2)) AS
DECLARE @thing DECIMAL(20,6)=CONVERT(DECIMAL(20,6),65000)/12;
EXEC MyTest @MyParam=@thing;
DROP PROCEDURE MyTest
Just being able to disable Deferred Name Resolution would be a great first step. When I create an object, I want to know if it's bad right away-- not at run time.
Charles Roddie commented
+1 for setting to disable implicit conversions.