Don’t trust SQL Server 2005 “Deferred Name Resolution”
Take a look at the following stored procedure (as always, meant to be created against the AdventureWorks database):
CREATE PROCEDURE MyProcedure
AS
IF 1 = 0
SELECT TOP 10 * FROM Person.WrongTable
ELSE
SELECT TOP 10 * FROM Person.Address
As I’ve mentioned before, this stored procedure will be created by SQL Server 2005 without throwing any error message or warning. The Person.WrongTable table will be treated as a missing reference, and “Deferred Name Resolution and Compilation” will come into play. What will happen then? The names will be resolved upon execution:
When a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the sys.sql_modules catalog view and checks that the names of the objects used by the procedure are present. This process is called deferred name resolution because table objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed.
According to the Books Online, then, the stored procedure should fail upon execution on the resolution stage. But it doesn’t! - try it out yourself. It seems as if the query processor realizes the first statement is unreachable and doesn’t bother to resolve the names there.
Now, what happens when the condition in the IF statement depends on a parameter? Let’s see:
CREATE PROCEDURE MyProcedure2
@flag bit
AS
IF @flag = 1
SELECT TOP 10 * FROM Person.WrongTable
ELSE
SELECT TOP 10 * FROM Person.Address
This stored procedure only fails when executed with @flag equal to 1. But this behavior is not documented.
Let’s see another interesting case: instead of a parameter, the condition will depend of a value stored in a table. We need to create a test table first:
CREATE TABLE TestTable
(
id int IDENTITY(1,1), flag bit
)
INSERT INTO TestTable(flag) VALUES(0)
INSERT INTO TestTable(flag) VALUES(1)
Now we can create the following stored procedure:
CREATE PROCEDURE MyProcedure3
AS
DECLARE @flag bit
SELECT @flag = flag FROM TestTable WHERE id = 1
IF @flag = 1
SELECT TOP 10 * FROM Person.WrongTable
ELSE
SELECT TOP 10 * FROM Person.Address
We are setting @flag to a value stored on the first row of our TestTable table. The execution succeeds and returns the 10 first rows of the Person.Address table. Let’s change the procedure to read the flag from the second row instead:
SELECT @flag = flag FROM TestTable WHERE id = 2
Now the execution fails (finally!):
Msg 208, Level 16, State 1, Procedure MyProcedure3, Line 6
Invalid object name ‘Person.WrongTable’.
What’s going on here? Again, this behavior is not consistent to what the documentation says. I doubt SQL Server is fetching the data from the TestTable table in advance to determine how the execution will go, so it must be resolving the names on the fly on a per-statement basis. I’ll check my books to see if I find more information about this “feature”.
This surely looks like a valid and even desirable optimization in most cases, but again, it’s not consistent with the documentation. According to the Books Online, the optimization of the query and the creation of the execution plan occur after the resolution stage:
If procedure execution successfully passes the resolution stage, the Microsoft SQL Server query optimizer analyzes the Transact-SQL statements in the stored procedure and creates an execution plan.
The final conclusion here is that you can’t be sure your stored procedures (or functions or any executable object for that matter) are correct until you test them with values that exercise all the possible execution paths (this has been a pain in the neck at work the last couple of days). This makes a good argument for unit testing at the database level.
Happy coding!
Hello,
I have to the exact same conclusions as you did, recently in a project I am participating in - do you have any update (or official wording about that from MS) about that discrepancy from the documented behaviour?
Best Regards,