An SQL text by Erland Sommarskog, SQL Server MVP Latest revision 2008-09-28.
In this article I discuss various checks that SQL Server could perform when you create an SQL module (that is, a stored procedure, trigger etc) that would help you to find stupid errors early. By finding the errors earlier you would be more productive, and your testing colleagues will never have to deal with your typos.
Since many of these checks would break existing code if they were introduced right off, the idea is that these checks are only active if a certain setting is in force; tentatively the command would be SET STRICT_CHECKS ON. I originally submitted a suggestion for this on an early incarnation of Ladybug during the Yukon beta, but it seems to have gone lost. In March 2007, I submitted a new suggestion for this feature on Connect as https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=260762.The purpose of this article is to develop this idea further with the hope that it will serve as inspiration for the SQL Server team, as they start working on the next version of SQL Server.
SET STRICT_CHECKS ON would be a compile-time setting. When a batch is entered and parsed, a number of things that goes unnoticed today would yield a compilation error or possibly a warning. This applies to CREATE PROCEDURE, CREATE FUNCTION etc as well as ad-hoc batches.
The first section is devoted to a discussion on general principles, but the main body of this article is devoted to the possible checks that could be performed when SET STRICT_CHECKS ON is in force. Here is a introductory list of the checks that I discuss:
At first glance, the whole idea with optional checks may seem corny, but there are precursors. Two
other languages that I work with have similar features. Visual Basic has
Option Explicit and Perl has use strict. And people with
experience of C from older days may recall the lint program.
The purpose with these checks is to help the programmer to find silly typos and goofs early, so he don't have to spend his time to proof-read the code for mistakes that the machine easily can detect. Or avoid egg on his face when his typo halts testing or even worse production.
In this document I assume that the command to enable this feature would be SET STRICT_CHECKS ON, and I refer to it "strict checks in force" etc. The SQL Server team may prefer something else, like SET STRONG_CHECKING ON and I trust their good judgement in that regard. I have gone for strict checks consistently through this document, as I also used it the Connect item.
My list of possible checks is quite long, and it is not inconceivable that
some programmers appreciate some of them, but feel that other checks conflict
with their style of programming. This could be addressed with separate commands
for different set of checks. This is not without a precursor: in Perl, use
strict has three options, so that you can say use strict qw(vars
subs) if you only want those two kind of checks. Thus, in SQL Server we could
have SET STRICT_CHECKS OBJECTS ON, SET STRICT_CHECKS IMPLICIT_CONVERSION ON etc,
and of course also a SET STRICT_CHECKS ALL ON.
However, I know that Microsoft is not overly fond of SET commands, and I don't see this as a compelling issue. In the rest of this document I assume that there is a single all-or-nothing knob for strict checks.
My list of possible checks is tentative, and I more or less expect the SQL Server team to discard some of them. There is one important thing to note though: Often when you introduce a new feature, some whistles and bells are deferred to a later version. Usually, this is a good idea, but for this feature this could be problematic. Many of the new checks will break existing code, and for the first version that is alright, because you need to say SET STRICT_CHECKS ON for it to happen. But say that strict checking for implicit conversions are deferred to SQL 2014. This means that code that compiled with strict checks in force on SQL 2011, could stop to compile in SQL 2014. To avoid this, Microsoft would have to add SET STRICT_CHECKS LEVEL_2014 ON. But then we are almost back to SET STRICT_CHECKS OBJECTS ON etc that I discarded above.
One possibility is that Microsoft decides that for strict checks added in later versions, the compatibility level will determine whether the new checks are in force or not. Which depending how hard the new checks strike they will get away with or not.
Should the issues that strict checking reveals be raised as errors or as warnings?
The advantage of letting them be only warnings is quite obvious: there would not be any real compatibility issues. Taken to the extreme, there would not have to be any SET command at all, but the checks would always be in force.
However, there are also a number of disadvantages. One is how errors and warnings work with the tools today. With errors, SQL Server reports a line number, and this line number is displayed in the query tool, and the programmer can double-click on the line to quickly find the error.
For warnings on the other hand, SQL Server does not seem report a correct line number, and the query tools do not display them. This means that while the programmer is informed of the problem, it will take him longer time to find where the problem is.
This particular issue could be addressed though: SQL Server could report all strict-check issues as level 9, and make sure to report the correct line number for the issues. The tools would then be changed to display procedure and line number for level-9 messages. Alternatively, the messages could be reported as level-0 messages as warnings are today, but all strict-check messages would start with "Line nnn:". The tools would need to be adapted so that you can double-click on such a message to find where it origins from.
But there is more to it. I am quite sure that once these checks are in place more than one DBA would say "I don't accept any strict-check messages in my databases, and he want to have them all reported as errors to prevent the objects to be created. This could be met by having SET STRICT_CHECKS WARNINGS, SET STRICT_CHECKS ERRORS and SET STRICT_CHECKS NONE. But again, my preference is for a simple on/off switch.
What is a little more realistic is that only some of the issues found by strict checks are reported as errors, whereas others are reported as warnings. And that is alright, as long as line numbers are reported for these warnings.
Nevertheless, to simplify this text, I assume that all issues found by strict checks are reported as errors and I don't discuss the possibility of reporting them as mere warnings any further.
One more thing: all errors detected by strict checks should make it clear that they come from strict checks and possibly inform the user how to turn off these checks.
The purpose of SET STRICT_CHECKS ON is to control compile-time behaviour. Therefore, it raises some questions would it mean if SET STRICT_CHECKS ON (or OFF) would appear in the middle of a stored procedure. Consider:
IF @x >= 0 SET STRICT_CHECKS OFF ELSE SET STRICT_CHECKS ON
Since SET STRICT_CHECKS is a compile directive, what would this mean?
To avoid all such ugly problems, my suggestion is that the command is only permitted in the top-level scope, and it must either be in a batch of its own, or the first command in a batch (in the latter case, it would serve as a compile-time directive.) And when I say the first command in a batch, I mean it. That is:
CREATE PROCEDURE some_sp @var int = 99 AS SET STRICT_CHECKS ON
will not fly. Some of the checks that I propose would affect the procedure header, so above the raises the question, is the check in force for the header or not?
I think there should also be a database option, so that the DBA could set that strict checks should be on by default in a database.
Strict checks are there to help the programmer to catch typos and goofs. But sometimes the strict checks will get his way, because he actually wants to do what he types. Of course, the programmer could turn off strict checking entirely, but then he would throw out the baby with the bathtub, if he has one exceptional thing in a 1000-line procedure. So there has to be something more fine-grained.
As you understand from the previous section I have already discarded SET STRICT_CHECKS OFF as possibility. A more workable idea would to have BEGIN NOSTRICT and END NOSTRICT. These BEGIN END would have to align with other BEGIN END pair, so that this would not be permitted:
BEGIN
BEGIN NOSTRICT
...
END
END NOSTRICT
This could be extended to have BEGIN/END STRICT as well, so that you could have strict section even if strict checks are off on session level. However, I still see a problem. This means that I would have to turn off strict checks for an entire statement. Maybe all I need is to refer to a table that does not exist now, but which will exist later.
So I'm thinking that it should be possible to turn this off on line level.
This has been done before: with lint you could suppress messages that normally
would appear by adding comments that stated that the code was actually your
intention. I have not settled on what is the
best way to express this, but in this document I assume that the comment
/* NOSTRICT */
Since SET STRICT_CHECKS ON is a compile-time setting, should it be saved with the SQL module? I don't see this as compulsory, but I think it could be helpful. If you scripted such a procedure, query tools would be expected to generate a SET STRICT_CHECKS command in the query window, like they generate commands for ANSI_NULLS and QUOTED_IDENTIFIER. Another advantage is that the DBA could query the system catalogue for objects entered with strict checking off.
Strict checks is intended to help the developer, but SQL Server does not know
if there is a developer or an application on the other end. If an application
issues SET STRICT_CHECKS ON, and then runs ad-hoc batches, they would be
checked. I don't see any major problems with this. Stupid errors like JOIN
tbl a ON a.col = a.col would result in an error, which is probably better
than returning an incorrect result.
A similar case is when SQL Server compiles an existing stored procedure to build a query plan. Would strict checks apply in this case? I can see some advantages with this. Today, if a procedure refers to a non-existing table, it bombs when you reach that statement, in which case the procedure is terminated, even if there is a local catch handler. This can cause some mess if the procedure started a transaction. With strict checks in force, the compilation would fail directly and the behaviour would be cleaner. But I don't see this as mandatory. If Microsoft finds it easier to implement run-time compiles with unstrict checking that is alright.
Since strict checks is a engine feature, the impact on the tools is small. There are a few points, though:
->->->->I'm a long-timer with SQL Server and I first came in touch with the product in 1991. Back in those days, if you said something like:
CREATE PROCEDURE bad_sp AS PRINT 'This prints' SELECT col FROM nonexisting
You would get the error message:
Server: Msg 208, Level 16, State 1, Procedure bad_sp, Line 3 Invalid object name 'nonexisting'.
This was the state of affairs up to SQL 6.5. With SQL 7, Microsoft introduced what they call Deferred Name Resolution. You no longer get an error about the missing table, but the procedure creates just fine. It is not until the SELECT statement actually executes that you get the error message. When I first heard about this, I could not just believe it! I was using SQL Server to develop a large enterprise system, and Microsoft changes the behaviour as if SQL Server was only for toys. Imagine an important function grinding to a standstill just because of a small typo that SQL Server could have caught up front! You may object that such typos should be caught in testing, and in most cases they do, but: 1) the typo may be in an odd code path that was not covered by the testers, 2) if the typo stops the tests, the testers will have to wait for the next build, and the company loses time and money.
Beside that you don't get the actual error until run-time, there are also some nasty side effects. Let's say that you have an outer stored procedure that calls an inner stored procedure that first starts a transaction, and then later runs into an error with a missing table. The inner procedure is aborted, but execution continues in the outer procedure – with the transaction still active! How many programmers are prepared for that? In SQL 2005, TRY-CATCH makes this is a little easier to handle, but if the inner procedure has a TRY-CATCH, that CATCH will not fire, only the TRY-CATCH in the outer procedure.
Another problem with deferred name resolution is that the missing table can mask other errors. Consider this procedure:
CREATE PROCEDURE some_sp @productid int AS
CREATE TABLE #temp (orderid int NOT NULL,
orderdate datetime NOT NULL)
INSERT #temp (orderid, orderdate)
SELECT o.OrderID, o.OrderDate
FROM Orders
WHERE EXISTS (SELECT *
FROM [Order Details] od
WHERE od.OrderID = o.OrderID
AND od.ProductID = @productid)
SQL 7 and SQL 2000 will gladly agree to create this procedure, as long as
#temp does not exist when you submit your CREATE PROCEDURE. Since #temp does not
exist, they defer the compilation of the entire query until later. And then at
run-time, the statement blows up because you had forgotten to add the column alias. (It
should read FROM Orders o). Thankfully, this problem was addressed
in SQL 2005, and SQL 2005 and SQL 2008 flag the missing alias directly and disagree to create the procedure. But say instead, that in the procedure that you
have:
SELECT o.OrderID, o.OrderDate
FROM Orders
JOIN #temp t ON o.OrderID = t.orderid
WHERE EXISTS (SELECT *
FROM [Order Details] od
WHERE od.OrderID = o.OrderID
AND od.ProductID = @productid)
In this case, you will only get an error about the missing alias until run-time even with SQL 2008. In this particular query, there is a theoretical possibility that #temp at run-time could have a CLR column o with type methods named OrderID and OrderDate, why the query could be legal. So is SQL Server giving you the benefit of the doubt? Not really. Look at this query:
SELECT o.OrderID, o.OrderDate
FROM Orders
WHERE EXISTS (SELECT *
FROM #temp od
WHERE od.orderid = o.OrderID)
Here it is painfully obvious that o.OrderID and o.OrderDate can't but be errors, but SQL 2008 will still not tell you until run-time, because it defers the compilation until #temp actually exists. So while SQL 2005 brought some improvement, it did not go the full way.
In these examples, I used a left-out alias definition as an example, just to emphasise how bizarre this is. You get exactly the same result with the alias in place, but instead misspell OrdateDate as OrderDte. That is, common sloppy errors you want the computer to find for you go unnoticed.
My own take on all this madness, is that when we moved from SQL 6.5 to SQL 2000, I changed our load tool that all our developers use, so that it performs checks for missing tables. To deal with temp tables, it extracts the temp-table definitions from the code and creates the temp table before submitting the stored procedure, so that SQL Server will perform full checks of all queries. This solution nullifies the effects of deferred name resolution for us, but in the long run this is not a tenable solution. For instance, I could initially not use CTEs in SQL 2005, because my load tool complained about these as missing tables until I had added code to my load tool to recognise CTE definitions. It is nothing but crazy that I should have to work with such things at all, when it should be in the engine.
When SET STRICT_CHECKS ON is in effect, a statement must only refer to known objects, and to known columns in those objects. Plain and simple.
The reader may object that strict checks is no full guarantee that code will not blow up at run-time because of missing tables, since a table could be dropped after the procedure has been created. This is true, but the intention of strict checks is not to make SQL Server fool-proof; it is to help the programmer to catch silly errors early. (A solution to avoid this would be to add WITH SCHEMABINDING as an option for stored procedures and triggers. That could certainly be worth considering, but it would be very painful if you need to change a table by means of drop and recreate, and you would have to drop and reload 200 stored procedures.) In any case, my experience that it is not common that tables are accidently dropped. Silly typos are far more common. At least if I am the one who does the typing.
What about dynamic SQL? Of course, if your stored procedure creates dynamic SQL, strict checks are not going to help you to catch those errors before run-time. But in an enterprise system, most of the code should be in stored procedures with static SQL. At least that is my not-so humble opinion.
In the examples above, the deferred name resolution was due to the temp table created within the procedure. Most certainly, the reason Microsoft added this misfeature was due to temp tables. And it cannot be denied that there is a certain logic here. CREATE TABLE is a run-time statement, so the table does not exist when the procedure is created, and the smallest warning about it, would be irritating noise. But consider this procedure:
CREATE PROCEDURE another_bad_sp AS CREATE TABLE #tmp(a int NOT NULL) SELECT b FROM #tmp
I still have access to SQL 6.5, and when I try to create it, I am told:
Server: Msg 207, Level 16, State 2, Procedure another_bad_sp, Line 3 Invalid column name 'b'.
If SQL 4.2 and 6.5 could do it, why not later versions? It will have to be admitted that SQL 6.5 did not always get it fully right, although I don't recall any examples all these years later. In any case, it worked flawlessly 99% of the time.
The basic idea is simple: when strict checks are in effect, SQL Server would find all temp table definitions in the procedure, and create these tables (either entering them into the system catalogue, or just keeping some in-memory definition), before checking all queries against the system catalogue. (I don't know if you've noticed, but SQL Server first parses batches for syntax and variables, and in a second pass it performs checks against the metadata.)
Now, since CREATE TABLE is a run-time statement, this raises the question about what should happen if the CREATE TABLE statement is within an IF statement or you have something as stupid as:
CREATE PROCEDURE another_bad2_sp AS SELECT a FROM #tmp CREATE TABLE #tmp(a int NOT NULL)
My answer is simple: don't bother with trying to be smart. SQL 6.5 gladly accepts the above stupidity. I trust most programmers to create their temp tables early in the procedure. Again, the purpose of strict checks is to help programmers to find their stupid and silly errors. (I was considering suggesting that with strict checks in force, the creation of temp tables must come before any queries or control-of-flow statements, but I dropped the idea, because I don't see this as a problem big enough. There may be situations where you only want to create a temp table in a certain IF branch in a procedure, so such a check could be more of a hindrance.)
What if someone wants to have different definitions of his temp table? Well, in such case he should not use strict checks. But as a matter of fact, you cannot do that. Watch this:
CREATE PROCEDURE doubletmp @i int AS IF @i = 0 CREATE TABLE #tmp(a int NOT NULL) ELSE CREATE TABLE #tmp(b int NOT NULL)
For once, this a procedure for which all versions of SQL Server from 6.5 to SQL 2008 produce the same error message when you try to create it:
Msg 2714, Level 16, State 1, Procedure doubletmp, Line 5 There is already an object named '#tmp' in the database.
So, SQL 7 and later do notice that there is a temp table being created in the procedure. They just don't care using that knowledge when it checks other queries.
What it the temp table exists when procedure is created? That is, you have:
CREATE TABLE #tmp(col_a int NOT NULL) INSERT #h (col_a) values (12) go CREATE PROCEDURE another_sp AS CREATE TABLE #tmp(col_a int NOT NULL) SELECT col_a FROM #tmp
In SQL 6.5 you get an error when you try to create this procedure, but I don't think this is the right behaviour. The CREATE TABLE command in the procedure should take precedence. The temp table that exists now is not that likely to exist at run-time.
I have here confined the discussions to temp tables, as in practice, this issue is about temp tables in 99% of the time. But if the procedure creates a static table, it should be handled in the same way. That is, SQL Server should extract the definition, and use the definition when checking the queries. One exception though: if the table already exists, this should be considered an error.
I should add that a workaround to the problems that temp tables causes with deferred name resolution is to use table variables instead. These are declared entities, and SQL Server will consider these when checking the queries. Unfortunately, this is not always a feasible solution. Table variables do not have statistics, which can lead to less optimal query plans, so it is highly desired that queries with temp tables are fully checked at create time.
Some times you want to do:
CREATE PROCEDURE outer_sp AS
CREATE TABLE #tmp(...)
EXEC inner_sp
SELECT ... FROM #tmp WHERE ...
go
CREATE PROCEDURE inner_sp AS
INSERT #tmp (...)
SELECT ....
go
That is, in a stored procedure you want to access a temp table created by the caller. This could be because you pass output to the caller as in the example above, but it could also be that the temp table holds input data to the procedure. Or it could be both; the inner procedure could update some columns in temp table.
This scenario certainly presents a greater challenge for strict checks, and I don't have a firm opinion on what is the best solution, but I will discuss a couple of alternatives.
This is how SQL 6.5 handled the situation. The file that held the definition for inner_sp, simply had to read:
CREATE TABLE #tmp(...)
go
CREATE PROCEDURE inner_sp AS
INSERT #tmp (...)
SELECT ...
Obviously, this means you need to have the definition of #tmp in two places which is not satisfactory, particularly if the temp table has many columns. If you have access to a preprocessor, you can easily deal with the situation, but most people use SQL Server without a preprocessor.
The alternative would be to use
CREATE PROCEDURE inner_sp AS
INSERT #tmp /* NOSTRICT */
(...)
SELECT ...
Or even go as far as create the entire procedure with strict checks off. This could be deemed acceptable, since this type of procedures is not that common. Then again, they are by no means rare, either.
If table-variable parameters are made read-write (which I argue for in a separate article), the need for using temp tables to pass data would be less. But since table parameters have no statistics beyond the cardinatility, there will still be a need for sharing temp tables. So it would still be interesting to look for a better solution.
The idea is here that you somewhere state which temp tables you expect to exist when your stored procedure is called. Strict checking would then not flag the temp tables you have declared this way as missing. But the column definition would not be known, so any misspellings of columns in the temp table would not be reported until run-time.
There are two places where this declaration could appear: 1) In the procedure header. 2) a separate declaration in the body. That is, one of:
CREATE PROCEDURE inner_sp WITH TABLE #tmp AS ... CREATE PROCEDURE inner_sp AS EXTERNAL TABLE #tmp
As for the syntax, WITH is the obvious choice for the procedure header, but it seems funny in the body. So I looked at the list of reserved keywords in Books Online, and picked the first match I found, EXTERNAL. Do not take that as a final proposal, but a working name.
What would happen at run-time if inner_sp is invoked and no table called #tmp is to be found? The compilation would fail, and the procedure would not start executing. (And since EXTERNAL TABLE would be a new feature, there is no reason to let this depend on whether strict checks are in force or not.)
It would not be permitted to have a local CREATE TABLE for a temp table declared as external.
A good question is: would EXTERNAL TABLE be restricted to temp tables only? There can be some advantages to use it for "real" tables as well. Maybe the outer procedure creates a staging table (although that begs the question why you don't simply use TRUNCATE TABLE). If the table is in a different database that may be missing, you get the error directly when the procedure is invoked. While interesting, I don't see this as compelling enough. If it's easier to implement EXTERNAL TABLE to be for temp tables only, go for that.
SQL 2008 added table types, and in SQL 2008 you can only use table types with table variables and table parameters. If the capability was extended so that you could use table types with CREATE TABLE, the problem with the multiple table declaration could be reduced to:
CREATE PROCEDURE outer_sp AS
CREATE TABLE #tmp(...) FROM some_table_type
EXEC inner_sp
SELECT ... FROM #tmp WHERE ...
go
CREATE PROCEDURE inner_sp AS
EXTERNAL TABLE #tmp AS some_table_type
INSERT #tmp (...)
SELECT ...
go
In this solution, you would get full checks so that misspellings of columns in #tmp would be caught when you try to create the procedure. (And again, since EXTERNAL TABLE is a new feature, this would happen even if strict checks are off.)
There would be a run-time check when inner_sp is invoked whether #tmp exists, and whether it conforms to some_table_type.
Obviously, this is the best solution, but also the one that requires most development effort.
Say that a procedure has a query that includes an index hint for a non-existing index:
CREATE PROCEDURE bad_hint AS
SELECT col FROM mytbl WITH (INDEX = no_such_index)
SQL Server creates the procedure without any mention of the missing index. The query blows up at run-time. This behaviour is clearly not acceptable. Now, exactly what would happen with strict checks in force depends a little. With the current behaviour, there should be an error and the procedure should not be created.
However, there is a suggestion on Connect that there should not be any run-time error when the index in a index hint is absent. If this suggestion is honoured, this may affect how strict checks consider missing hints. Aaron and Greg's idea, that I entirely agree with, is that a stored procedure should not fail entirely just because you have a hint an index that you have dropped. The index could have been dropped by mistake, for instance because sys.dm_db_index_usage_stats indicated that the index was never used, but it was in fact in use in an important end-of-quarter report. (sys.dm_db_index_usage_stats is flushed on SQL Server restarts, so it's a bit dangerous to rely on it.)
The situation when you create the procedure is a little different. It's a little fishier to create a procedure that refers to a missing index. But it could be that you only intend to have this index only in some databases. For consistency, I think that if Aaron's and Greg's Connect item is honoured, creating a procedure that refers to a missing index, should at yield a warning, but not an error.
This far I have only discussed tables and other table sources. I will now look at other types of objects. User-defined scalar functions are also victims to deferred named resolution. For instance, SQL Server 2008 gladly creates:
CREATE PROCEDURE bad_udf @OrderID int AS
SELECT dbo.no_such_udf(OrderID) FROM Orders WHERE OrderID = @OrderID
and then it bombs when you run it. This procedure should of course not be accepted when strict checks are in force.
Invocations of type methods look syntactically very similar to calls to user-defined functions. (In fact so similar, so that you can get errors for ambiguity, but that is another story.) For instance:
CREATE PROCEDURE type_method @OrderID int AS
SELECT clr_column.type_method(OrderID) FROM Orders WHERE OrderID = @OrderID
As far as I can tell, SQL Server will give you an error if you refer to a non-existing type method, be that a CLR UDT column or an xml column. Just keep on doing that. Strict checks or not.
Stored procedures are a special case, in so far that you have always gotten a warning if you call a non-existing stored procedure:
CREATE PROCEDURE bad_call AS EXEC no_such_sp
The message for this condition has varied over the versions, but it has been there since SQL Server 4.x at least. SQL 2008 says
The module 'bad_call' depends on the missing object 'no_such_sp'. The module will still be created; however, it cannot run successfully until the object exists.
Originally the purpose of the warning was to inform the user that SQL Server was not able to add any rows to sysdepends, later sys.sql_dependencies. That particular piece of information is not that interesting, but what is interesting is of course that the procedure does not exist. SQL 2008 adds a new structure for dependencies where the dependencies are stored by name, so technically there is no longer any reason for the message. And indeed in some CTP of SQL 2008, the message was gone. As you may imagine, that made me very angry. Microsoft took reason and the message is back.
With strict checks in force the warning should be promoted to an error (because as I discussed above this makes it easier to find where this bad call is). Should the warning still be there when strict checks are off? I suggest that it should, but I promise not to make a fuzz if Microsoft removes it.
Occasionally, you may have a cross-dependency: stored procedure A calls B,
and B in its turn includes a call to A. In this case, you should probably use
to get around it. (And in this case, you probably don't
want the warning at all.)
Service Broker objects are also subject to deferred name resolution. For instance, this
CREATE PROCEDURE sb1 AS DECLARE @dialog_handle UNIQUEIDENTIFIER; BEGIN DIALOG CONVERSATION @dialog_handle FROM SERVICE no_such_service TO SERVICE 'the_to_service' ON CONTRACT no_such_contract; SEND ON CONVERSATION @dialog_handle MESSAGE TYPE no_such_type RECEIVE * FROM no_such_queue
is accepted in SQL Server today. With strict checks, there would be errors all over the place. The only thing to note is that the TO SERVICE should not be checked. This is a string literal, and this can be a service in a remote database in a remote server and can be difficult to validate.
Would you believe it, if you say
BULK INSERT tbl FROM 'C:\temp\myfile.bcp'
the file name is actually subject to deferred name resolution. Or else, how can you explain this.
BEGIN TRY
BULK INSERT tbl FROM 'C:\temp\nosuchfile.bcp'
END TRY
BEGIN CATCH
PRINT 'This does not print'
END CATCH
That is, if the file does not exist, the CATCH handler is not invoked, because the procedure is terminated on the spot (an outer CATCH handler can catch the error). Normally, this happens only with compilation errors, so I will have to assume that when SQL Server originally creates the procedure, it checks to see whether the data file is there, and if it's not it defers compilation until later, and if the file is still missing at run-time, this is the same as a missing table.
Now, why it would look at the data file at all when creating the procedure is beyond me. In any case, I don't think it would be a good idea if you would get an error message for a missing file even when strict checks are in force. A missing data file should be handled as a pure run-time error, be that a data file or a format file, so that a local CATCH handler can work. That would agree with programmer expectations. (If Microsoft makes this a pure run-time check, there is also no reason that the file could not be specified through a variable, but that's another story.)
It's a little different with OPENROWSET(BULK...), since this feature permits you to select columns and filter rows from the data file. In this case there has to be compilation somewhere. The column names for OPENROWSET(BULK...) can be specified in two ways:
FROM OPENROWSET(BULK...) AS table_alias FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
In the first case, the column names are taken from the format file. Now, with strict checks in force, should SQL Server require the format file to be in place? I'm not sure that I see the point with that. But it does not seem a good practice to me to rely on a source outside the database to provide column names, so one idea is that with strict checks in effect, only the form with column aliases would be permitted. Note that with the column aliases in place, there is no need to look for any format file or data file at compile time. This could be deferred to run-time, so that a missing file can be a true run-time error which can be caught by a CATCH handler in the same scope as the statement with OPENROWSET. And the files can be specified through variables...
What about:
CREATE SYNONYM mybadsyno FOR nosuchobject
should this be permitted when strict checks are enabled? I see no harm if so. As long as the synonym is not used, there is no problem. Of course, if you have
CREATE PROCEDURE bad_synonym AS SELECT col FROM mybadsyno
that stored procedure should not be created with strict checks on.
Now, this is a funny case. Consider this procedure:
CREATE PROCEDURE linkaccess AS SELECT OrderID FROM SERVER1.Northwind.dbo.Orderss
What do you think if you try to create this procedure in SQL Server today? Depending on whether you have a linked server SERVER1 set up or not, you get one of these messages:
Msg 7202, Level 11, State 2, Procedure linkaccess, Line 2 Could not find server 'SERVER1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Msg 7314, Level 16, State 1, Procedure linkaccess, Line 2 The OLE DB provider "SQLNCLI10" for linked server "SERVER1" does not contain the table ""Northwind"."dbo"."Orderss"". The table either does not exist or the current user does not have permissions on that table.
So when a stored procedure accesses a remote object, there is suddenly no longer any deferred name resolution! I find this ironic. Just like bulk-copy objects, this is a situation where I may prefer to not be alarmed about missing tables, or at least not missing servers. For instance, assume that as a DBA you have to apply a change script with a couple of stored procedure to your production database during a maintenance window. But you find that you cannot do that, because the stored procedures refer to a linked server which also is down for maintenance.
Thus, an idea here would be keep the current behaviour when strict checks are on, but change the behaviour when strict checks are off.
By the way, things are not any better with OPENQUERY:
CREATE PROCEDURE linkaccess2 AS SELECT * FROM OPENQUERY(SERVER1, 'SELECT OrderID FROM Northwind.dbo.Orderss')
fails with:
OLE DB provider "SQLNCLI10" for linked server "SERVER1" returned message "Deferred prepare could not be completed.". Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 208, Level 16, State 1, Line 1 Invalid object name 'Northwind.dbo.Orderss'.
There may be other types of objects or language constructs that I have overlooked and for which deferred name resolution could be an issue in one direction or another. I trust Microsoft to have good judgement to sort this out. Wait, what did I say? After the exposé above, how could I trust them with anything in this regard? :-) Anyway, if Microsoft wants my opinion for some feature I've overlooked, they are welcome to contact me.
We know leave the topic of deferred name resolution behind, and turn to other areas where strict checks would make a difference. Consider this:
CREATE PROCEDURE get_order @OrderID varchar(5) AS SELECT OrderDate, CustomerID FROM Orders WHERE OrderID = @OrderID go EXEC get_order 110000
On SQL 7 and later the procedure is created, but the execution fails with:
Msg 245, Level 16, State 1, Procedure get_order, Line 2 Conversion failed when converting the varchar value '*' to data type int.
If this looks academic to you, I can tell you that this is from a real-world case where a programmer for some reason had
declared a parameter as
Time for some nostalgia and SQL 6.5. Here is what it has to say about the procedure above:
Server: Msg 260, Level 16, State 1, Procedure get_order, Line 2 Disallowed implicit conversion from datatype 'varchar' to datatype 'int' Table: 'tempdb.dbo.Orders', Column: 'OrderID' Use the CONVERT function to run this query.
And again, SQL 6.5 has it right. It's not that SQL 6.5 does not permit
implicit conversions at all, but it does not have implicit conversion between
numbers and strings. And I will have to confess that I just don't see the point
with that sort of implicit conversion. Sure, it permits me to say
CREATE TABLE somedata(datakey varchar(10) NOT NULL PRIMARY KEY,
whitenoise float NOT NULL DEFAULT rand(),
filler char(4000) NOT NULL DEFAULT ' ')
go
INSERT somedata (datakey) VALUES ('123456')
INSERT somedata (datakey) VALUES ('234567')
INSERT somedata (datakey) VALUES ('9875222')
That is, in this table the key is a string, but the key values are mainly numeric. This query seems to run fine:
SELECT whitenoise FROM somedata WHERE datakey = 123456
Now add one more row and run the query again:
INSERT somedata (datakey) VALUES ('123456A')
SELECT whitenoise FROM somedata WHERE datakey = 123456
The message is:
Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '123456A' to data type int.
The type conversion rules established in SQL 2000 say that when two types meet, the type with lowest precedence is converted to the other, if an implicit conversion exists. This has two nasty consequences in this case. As we have seen, as soon there is a non-numeric value in datakey, the query fails. But even if all data is numeric, all is not good. Since the varchar value is converted to int, the index on datakey is useless, and SQL Server must scan the table or the index. Try the query and look at the query plan.
By now, the reader understands what all this leads to: with strict checking on, there will be less implicit conversion permitted. However, it would be impractical to outlaw everything, since not all implicit conversions are harmful. For instance, converting from smallint to int is unproblematic in all contexts as far as I know. I will have to admit that I have not considered each and every case, rather I will cover four generic cases.
SQL Server 2008 has 33 basic types listed in sys.types. These can be divided into eight classes:
The basic idea is that when strict checking is in force, implicit conversion is not permitted from one class to another. However, the following implicit conversions are still permitted:
That is, it is permitted to implicitly convert from string to several other data
types. The rationale here is that you need to be able to work with literals, and
there is for instance no date literal in
It is also permitted to implicitly convert to sql_variant; that's kind of the essence of that data type. (XML and CLR types are not included, since they cannot be stored in sql_variant.)
This means that quite a few implicit conversions permitted today would be disallowed
with strict checks on; even
some that were permitted in SQL 6.5, for instance binary to numeric. The most
controversial ban may be integer to date/time (this was not permitted in
6.5 either). That would mean that you can no longer do SELECT @date + 1.
Then again, neither can you do SELECT @date = 2008-05-30, which is
not an uncommon mistake.
This is where it gets more difficult, and I will be very loose here. One
possibility would be that any conversion that could incur loss of information
would require explicit conversion with strict checks: from nvarchar to
varchar, from float to int, from
What I've said above, applies to any situation: assignment, expressions, comparisons etc where implicit conversion could occur. I would like to add a special rule that applies to comparison operators in queries: With strict checks, implicit conversions that would convert a column so that any index on the column could not be seeked in the most efficient way should not be permitted. It does not matter whether such an index exists, the conversion would always be disallowed.
I'll try to clarify. This should always be permitted:
SELECT @nvarchar = 'somevarcharstring' UPDATE tbl SET nvarcharcol = varcharcol SELECT col FROM tbl WHERE nvarcharcol = @varcharval
In these three examples a varchar value is implicitly converted to nvarchar. This should always be permitted, as there is no loss of information and there is no performance issue. But this is bad:
SELECT col FROM tbl WHERE varcharcol = @nvarcharval SELECT col FROM tbl WHERE varcharcol = N'nvarchar' SELECT a.col FROM tbl1 a, tbl2 b WHERE a.varcharcol = b.nvarcharcol
In all these queries, the varchar column gets converted to nvarchar. If there is an ANSI collation, the index will be still seeked, but in a less efficient way (that is at least my experience). With an SQL collation it's useless. The rules should not be dependent on the collation, why the worst case should be considered.
Here is a again a good query:
SELECT col FROM tbl WHERE tinyintcol = @intvar SELECT col FROM tbl WHERE intcol = 11000.0
According to the conversion rules, tinyint should be converted to int, but this is not how it looks in the query plan. The same is true for the second query. (From SQL 2005 and on. In SQL 2000, the second query causes an implicit conversion of intcol, and any index is dead. But that's history.)
This query is less clear-cut:
SELECT a.col FROM tbl1 a, tbl2 b WHERE a.tinyintcol = b.floatcol
When I fooled around with a query like this, I got an implicit conversion on tinyintcol, if tbl1 was the table that was scanned, and thus the implicit conversion was harmless. But the rules should of course be independent of actual the query plan. This case needs further investigation.
It may be worth pointing out that the error message in this case should not say Implicit conversion ... is not allowed. Use the CONVERT function to run this query, but rather encourage the programmer to avoid the type clash altogether.
Consider this batch:
EXEC sp_addtype thistype, 'varchar(10)'
EXEC sp_addtype thattype, 'varchar(10)'
go
CREATE TABLE domaintest (a thistype NOT NULL,
b thattype NOT NULL)
go
SELECT * FROM domaintest WHERE a = b
From all versions from SQL 7 and on, this passes. But SQL 6.5 objects:
Server: Msg 305, Level 16, State 1, Line 1 The column 'a' (user type:thistype) is joined with 'b' (user type:thattype). The user types are not compatible: user types must be identical in order to join.
If you have used a very strongly typed language like Ada, this is not illogical. Now, SQL 6.5 was quite inconsistent. This restriction applied to joins only. It was OK to mix user-defined types in assignments, when comparing variables or even in foreign-key constraints. So I can understand why Microsoft dropped this rule in SQL 7.
I am not going to call for a change with regards to user-defined types. This feature is probably not used widely enough to warrant that. But hopefully one day (already in the next version of SQL Server???), Microsoft will add real domains from ANSI SQL. I don't know what conversion rules that ANSI mandates, but my suggestion is that with strict checks on, there would be no implicit conversion whatseover between values from different domains. A customer id and an order id may both be integer, but if you are joining them you are likely to be doing something wrong.
If your stored procedure includes calls to other stored procedures, the sole check at compile time is that the procedures exist, and as discussed above, you only get a warning if a procedure is missing, not an error. If you have left out any mandatory parameters, or specified a non-existing parameter, you will not be told until run-time. The same goes if you specify OUTPUT for a parameter that is not an output parameter. And if you leave out OUTPUT in the EXEC command for an output parameter, you don't even get an error at run-time!
In contrast, if your stored procedure calls a user-defined function, you get errors for missing or superfluous parameters already at compile-time. With one exception: if you call a scalar UDF through EXEC, the behaviour is the same as when you call a stored procedure.
Obviously there is room for improvements. With strict checks in force the following would apply to the EXEC command when you submit an SQL batch:
In all these cases, when I say "error", I do of course mean "compile-time error".
These checks can obviously not be performed when the procedure name is
specified through a variable, for instance EXEC @spname. Special
procedures like sp_executesql will require some extra consideration.
Should these checks be performed for calls to procedures in linked servers? I think so. At least, it should be consistent with how references to tables in linked servers are handled. And, as we have seen, they are checked even today.
Consider this:
CREATE PROCEDURE print_this @this varchar(20) AS PRINT @this go DECLARE @that varchar(20) SELECT @that = 'what?' EXEC print_this @this = that
What would you expect this to result in? The actual output is
that
The reason for this is that in EXEC statements there is no requirement to put a string literal in quotes, as long as the literal conform to the rules for regular identifiers. This rule that permits us to write:
sp_helpdb somedb
without putting the database name in quotes. And while this may be handy in an ad-hoc session, it is only a source for error in a programming environment. In the example above, the intention was presumably to pass the parameter @that to the stored procedure.
Thus, with strict checks in force, it would be an error to have a string literal without quotes in an EXEC statement.
Consider:
DECLARE @str varchar,
@dec decimal
SELECT @str = 'My string',
@dec = 12.2234
What value does @str and @dec have now? That's right, M and 12. But it is not uncommon to see questions on the SQL Server newsgroups from people who had different expectations. And one can hardly blame them. A default of 1 for a variable-length string is just plain silly. And while maybe not silly, the defaults of 18 and 0 for decimal are not obvious. And more importantly, they may be accustomed from other environments where you don't specify precision and scale for decimal at all, for instance .Net.
So with strict checks in force, there would be no default length for char, nchar, varchar, nvarchar, binary and varbinary. And there would be no default precision or scale for decimal and numeric. And for consistency, scale should also be mandatory for time, datetime2 and datetimeoffset.
One possible exception:
SELECT cast(intcol AS varchar)
In this marvellous consistent language known as Transact-SQL, the default
here is
With the old ANSI syntax for the FROM clause, it was a bit too easy to write things like:
SELECT a.col1, b.col2 FROM a, b, c WHERE a.keycol = b.keycol AND b.somecol = @value
That is, you could accidently have an extra table in your FROM clause that formed a cartesian product with the rest of the query. With the newer syntax with JOIN and ON, the risk for this debacle is removed since you have to explicitly specify CROSS JOIN if you want it. Unfortunately, you can still do this mistake:
SELECT a.col1, b.col2 FROM a JOIN b ON a.keycol = a.keycol
I don't know about you, but I do it often enough to want SQL Server to tell me about it.
The basic idea is that in strict mode, the ON clause must refer to the table source that the ON clause is attached to, and at least one of the preceding table sources. But we need to consider two complications: nested joins and multi-column joins before we can make a firm rule out of this.
It's legal to nest joins. Most often this is done with outer joins. Here is an example:
SELECT ... FROM a LEFT JOIN (b JOIN c ON b.col1 = c.col1) ON a.col2 = b.col2
How would this work with strict checks? Let's look at a list of cases:
LEFT JOIN (b JOIN c ON b.col1 = c.col1) ON a.col2 = b.col2 LEFT JOIN (b JOIN c ON b.col1 = c.col1) ON a.col2 = c.col2 LEFT JOIN (b JOIN c ON b.col1 = c.col1) ON b.col2 = c.col2 LEFT JOIN (b JOIN c ON b.col1 = b.col1) ON a.col2 = b.col2 LEFT JOIN (b JOIN c ON a.col1 = c.col1) ON a.col2 = b.col2
The first two both makes perfect sense, and should not result in any error. The third on the other hand looks spooky. Somehow the table a is left free-floating. The fourth is a breach against the basic idea, as only one table is included in the condition. And the fifth? That is not only an error under strict checks, it's an error in SQL Server today, and I assume in ANSI. In a nested JOIN condition, table sources outside the nested join are not visible; similar to a derived table.
Thus, for the innermost join in a set of nested joins we don't need any modification of the basic rule. But for the joins further out, "the table source the ON clause is attached to" refers to the nested join, that is b JOIN c in the example above.
The next problem to consider is multi-column conditions. This a likely to be a goof:
SELECT a.col1, b.col2
FROM a
JOIN b ON a.keycol1 = b.keycol1
AND b.keycol2 = b.keycol2
It passes the basic rule, so we need to extend it a bit. ON clauses typically consist of a number of AND factors. So the rule could be extended to all AND factors? Not so fast! This is perfectly OK:
SELECT a.col1, b.col2
FROM a
LEFT JOIN b ON a.keycol = b.keycol
AND b.startdate = @startdate
So the rule needs to be modified to: each AND factor must include a column from the table source the ON clause is attached to, and either a preceding table source or a variable/constant. And at least one AND factor must refer to a preceding table source.
This still renders some legit queries as incorrect in strict mode, for instance:
SELECT a.col1, b.col2
FROM a
LEFT JOIN b ON a.keycol = b.keycol
AND b.startdate = b.enddate
I have never seen this, but who knows, maybe someone would like to do:
SELECT a.col1, b.col2
FROM a
LEFT JOIN b ON a.keycol = b.keycol
AND @include_b = 1
I contemplated these two cases for a while, and considered variations to avoid that they
would be flagged as errors in strict mode, but I arrived at the conclusion that
these sorts of conditions are rare enough. If the programmer wants to this, he
needs to add
Here all conditions were plain = and I only talked about AND. There is not really any difference to other operators. This condition is alright with strict checks:
SELECT a.col1 FROM a JOIN b ON datediff(DAY, a.somedate, b.somedate) <= 7
What this means can be disputed, but it fulfils the rule we've set up here, and I see no reason to change it. The same apply to more complex conditions that includes CASE expressions. As for subqueries, if we have:
SELECT a.col1, b.col2 FROM a JOIN b ON b.col = (SELECT ...)
The subquery must refer to a column from a to be accepted in strict mode.
The reader may think that OR terms should be handled similar to AND factors, but OR is so much less common, that I don't think it's worth making any it more complex by adding rules for OR. So this will pass, even if it's likely to be a goof:
SELECT a.col1, b.col2 FROM a JOIN b ON a.keycol1 = a.keycol1 OR a.altkey = b.altkey
I don't suggest any particular checks for WHERE clauses. Well, maybe really
obvious things like WHERE a.col = a.col should raise an error, but
as soon as you involve expressions, it get's more difficult to tell the goofs
from the real intentions. For instance, this makes perfect sense on a case-insensitive
collation:
UPDATE tbl SET col = upper(col) WHERE col != upper(col)
Consider this INSERT statement:
INSERT sometbl(Albert, Boris, Cesar, David,
Eric, Fiona, Greta, Heinrich)
SELECT Albert, Boris, Cesar, David
Eric, Fiona, Greta, Heinrich
FROM othertable
WHERE ...
This results in:
Msg 120, Level 15, State 1, Line 1 The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
The error may or may not be obvious to the reader, but I faced this error message a late Friday afternoon after a hard working week, and I had some 47 columns in my INSERT statement. I tried to match up the columns in the INSERT and SELECT lists, and then I tried to match them again. And again. I could not but see that I had all of them. Finally, I observed that there was a comma that was missing in my SELECT list.
In the INSERT statement above, there is no comma after David. This is not an error in SQL, but according to the SQL syntax Eric is a alias for David.
The suggestion is simple: with strict checks the form "column alias" is not permitted, but column alias has to be defined with one of "column AS alias" or "alias = column". I realise that this is controversial, as the syntax that I want to ban is right from the ANSI standard. But it is a really bad and error-prone syntax, so I have no mercy with it.
I should add that this concerns column aliases only. Table aliases would not be affected. Thus this would still be OK:
SELECT a.col1, b.col2 FROM tbl1 a JOIN tbl2 b ON a.keycol = b.keycol
Consider:
CREATE TABLE Orders(ordOrderID int NOT NULL PRIMARY KEY,
ordCustomerID int NOT NULL,
...)
CREATE TABLE OrderDetails (detOrderID int NOT NULL,
detRowNo smallint NOT NULL,
...)
SELECT ...
FROM Orders
WHERE ordOrderID IN (SELECT ordOrderID FROM OrderDetails WHERE detProductID = 76)
The error here is that the subquery has a column from the outer table in the SELECT list. Perfectly legal, but not that meaningful. More than one who have been bitten by this error has posted to the newsgroups, thinking that they have hit a bug in SQL Server. They haven't, but with strict checks we could help them to detect their mistake earlier. I have two suggestions:
Both protects against the mishap above in the SELECT list on their own, but you can be coding half asleep and use the wrong alias, in which case the second rule saves you. The first rule on the other hand picks up possibly unintended use of columns from the outer tables elsewhere in the subquery.
Despite that the statement reads DECLARE CURSOR, it's an executable statement, and as a consequence of this, there is no compile-time check whatsoever of cursors. If you misspell the cursor name in the OPEN, FETCH, CLOSE or DEALLOCATE statements it will not bite you until run-time. The same is true if your FETCH statement does not match the cursor declaration.
And of course, in the general case, such checks are impossible. It's
perfectly legal to declare a cursor in one scope and access it from another. But
how common is that? In my experience, a cursor is almost always created and used
in one single stored procedure. The most common exception is probably when you set up the cursor
in dynamic SQL, because your conditions for the cursor are dynamic. Thus, it
seems to be a good trade-off to add checks for cursors in strict mode, and pay
the price of having to add
With strict checks in force, SQL Server would extract cursor declarations and compare these with other cursor statements:
If you use cursor variables, the cursor is created with SET and not DECLARE CURSOR. The same checks could be implemented here. Then again, I have never used cursor variables myself, so I don't see this as equally compelling. And since cursor variables can be passed between stored procedures, they may more commonly have a wider scope.
One more small thing with cursors, although unrelated to the above: it could be worth considering whether it should be permitted to leave the cursor type unspecified in strict mode. That is, strict mode, you must specify the cursor as INSENSITIVE, STATIC, KEYSET, FAST_FORWARD or DYNAMIC. The default cursor type today is apparently DYNAMIC, which is a very poor choice. Most of the time, people probably think in the mindset of a static cursor.
A potential problem with the proprietary FROM syntax with UPDATE is that you may inadvertently update the same row more than once, in which case the result is undefined. The same problem exists with MERGE, except that MERGE will give you a run-time error if a row is updated or deleted many times.
It could be worth investigating, if there should be a compile-time check for this. That is, if SQL Server can deduce from keys and indexes that one row in the target table cannot join to multiple rows in any of the other tables in the FROM/USING clause, the statement passes, else it is not accepted in strict checks.
Consider this example: for all orders that have not been assigned an explicit address, we need to update the orders with a default address:
UPDATE orders
SET address1 = adr.address1
address2 = adr.address2,
...
FROM prders ord
JOIN customeraddresses cad ON ord.customerid = cad.customerid
AND cad.isdefaultaddress = 1
JOIN addresses adr ON cad.adrid = adr.adrid
WHERE ord.address1 IS NULL
This query would pass under strict checks only if there is a unique filtered index on
customeraddress (customerid) WHERE isdefaultaddress = 1
since in this case we can decuce that the UPDATE is unambiguous.
I like to stress that this is something that I raise for investigation, rather than actively suggesting it. I think that a strict check for this would result in quite a few error message in many parts of the world. In some cases unknown ambiguity problems would be revealed, but in many cases it would be false alarm as there is a business rule somewhere that makes the operation unambiguous, but it has not been spelt out in a constraint or an index. Not the least when temp tables and table variables are involved as many programmers ignore to define keys for them. Then again, if they did, it would probably do them good.
Consider this query:
UPDATE target
SET col = (SELECT s.col
FROM source s
JOIN target t ON s.keycol = t.keycol)
This is quite common beginner's error. The correlated subquery for UPDATE SET is difficult to understand and get right.
I don't have any proposal for how the rules to trap this under strict checks would look like. In fact, I don't even see it as particularly important to catch this. Strict checks are there to help programmers catch silly goofs, and the above is not something you write, once you have some experience of SQL programming. And beginners may not understand that they should enable strict checks anyway. But I brought it up, in case someone likes to bite.
Here is one that SQL Server MVP Razvan Socol ran into. He happened to write:
DECLARE @Something datetime ... UPDATE SingleRowTable SET @Something=NULL
Which is perfectly legal, but of course wasn't what he intended. The @ was a slip on his part. And something that SQL Server could have alerted him about. Generally, while UPDATE permits you to assign variables, it could only make sense if you assign it an expression that includes at least one column in the target table for the UPDATE. (Else you could just well use a SELECT.) And even then it's a bit fishy; there are a few cases when you roll your own id columns where it comes in handy, but they are not that common. So I could even go as far as arguing that variable assignment in UPDATE should not at all be permitted in strict mode. The few cases where it's useful has to be weighed against the many more cases it's a programming error.
By adding a new feature, in this text called "strict checks"; Microsoft can help programmers to find silly stupid errors early, and thereby help them to be more productive and produce a work of higher quality. In this text I've discussed a number of things that could be covered by strict checks. I noted initially, that adding strict checks for some things in SQL 11, and adding further checks in a later release can cause compatibility problems. Therefore, there is reason for a big bang. Nevertheless, some priorities may be in order.
Essential:
Very important:
Important:
To be investigated further:
I suspect that Microsoft feels that I have given them more than a mouthful with this list. Nevertheless, if you have further suggestions, please feel free to drop me a line at esquel@sommarskog.se. If I agree with you, I may add the suggestion to the article. If not, you can always file your own suggestion on Connect, http://connect.microsoft.com/SqlServer/Feedback.
2008-09-28 – Added the section No Naked Strings in Stored Procedure Calls.
2008-09-20 – Added the section Variables in UPDATE.
2008-06-14 – Original version.