Deferred Name Resolution

Deferred name resolution is the process of checking the names of all objects that a stored procedure uses to make sure that they exist. Tables, stored procedures, or other objects referenced by the stored procedure don't have to exist when the procedure is first created. SQL Server doesn't resolve the names of the objects until the stored procedure is actually executed.

Select * from #temptest

Server: Msg 208, Level 16, State 1, Line 1

Invalid object name '#temptest'.

Creation of Stored Procedure:

Create proc #testproc

As

Begin

Select * from #temptest;

End

The command(s) completed successfully.

In this stage sql server doesn’t resolve the object names

Execution of Stored Procedure:

Exec #testproc

Server: Msg 208, Level 16, State 1, Procedure #testproc_____________________________000110D0, Line 4

Invalid object name '#temptest'.

The resolution process occurs whenever a stored procedure needs to be compiled, such as

When the server is first restarted or

After the query plan is aged out of memory.

During the resolution process,

Not only does SQL Server check the objects that are referenced in the stored procedure, but it also performs other validation, such as the following:

·        Checking that any variables and columns passed into SQL Server functions are compatible with the expected inputs for the functions

·        Making sure that any data being passed out of the stored procedure is compatible with any variables that it's being passed into.

·        Ensuring that the column data types in any referenced tables are compatible with any variables that reference those columns

Bullet Point 1:

Create proc #testproc @id int

As

Declare @var varchar (10)

Begin

Select @var=name from #temptest where id=@id;

Return @var

End

Declare

@res int

Exec @res=#testproc @id='ten'

Select @res

Server: Msg 8114, Level 16, State 1, Procedure #testproc_________________________________0001217C, Line 0

Error converting data type varchar to int.

           

-----------

NULL

(1 row(s) affected)

Bullet Point 2:

Create proc #testproc @id int

As

Declare @var Varchar (10)

Begin

Select @var=name from #temptest where id=@id;

Return @var

End

Declare

@res int

Exec @res=#testproc 10

Select @res

Server: Msg 245, Level 16, State 1, Procedure #testproc________________________________________0001217C, Line 6

Syntax error converting the varchar value 'ten' to a column of data type int.

Bullet Point 3:

Create table #temptest (id int, name varchar (30));

Insert into #temptest values (10,'ten')

Select * from #temptest

Result set:

Id    name

10   Ten

Alter proc #testproc @id int

As

Declare @var int

Begin

Select @var=id from #temptest;

End

The command(s) completed successfully.

Exec #testproc

Server: Msg 245, Level 16, State 1, Procedure #testproc__________________________________________000110D0, Line 5

Syntax error converting the varchar value 'ten' to a column of data type int.

If any object being referenced by the stored procedure is renamed or deleted, an error will be returned to the user when the stored procedure is executed. If an object referenced in the stored procedure is deleted and a different one is created with the same name, the stored procedure uses the new object, assuming that all the data types in the new object are compatible.

The stored procedure doesn't have to be recompiled for this to occur. This activity is extremely different from the way prior versions of SQL Server worked.

In previous versions of SQL Server, you had to recompile the stored procedure if you made any changes to the underlying objects.

This could lead to some very difficult-to-troubleshoot problems. For example, if you renamed an object referenced by a stored procedure and then created a new object with the old name, the stored procedure would continue to use the old object, even though the name had changed. This was because SQL Server used to pre compiles the stored procedure and resolve the names of any objects referenced in the stored procedure to their object IDs. When the old object was renamed, SQL Server didn't—and still doesn't— change the object ID. Because the stored procedure had been precompiled and all the names had already been resolved, everything continued to execute exactly as it had. If any errors are encountered during the name resolution process, an error is returned and execution halts. If the name resolution process is successful, the stored procedure is analyzed to create an execution plan.

  

Don’t trust SQL Server 2005 “Deferred Name Resolution” (From Coding Obsession)

Name resolution Issue1 in SQL Server

 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

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.

Name resolution Issue2 in SQL Server

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.

Name resolution Issue3 in SQL Server (From Michael Zilberstein Blog)

That name resolution works for temporary tables differs from the one for permanent tables.

While for permanent table more than one CREATE TABLE statement with the same table name can be issued inside the same batch or procedure, for temporary table it will fail compilation saying: "There is already an object named '#t1' in the database".

-- Succeeds

CREATE TABLE t1 (col1 int);
DROP TABLE t1(col1 int);
CREATE TABLE t1(col1 int);
GO

-- Fails

CREATE TABLE #t1 (col1 int);
DROP TABLE #t1 (col1 int);
CREATE TABLE #t1 (col1 int);
GO

Reasons for this differentiation are historical. Deferred name resolution had been introduced in SQL Server 7 but for permanent tables only. It doesn't work for temporary tables and doesn't work for variables even when defined in different scope:

-- Fails with "The variable name '@a' has already been declared" error despite the fact that @a variable is declared in 2 different scopes.

BEGIN 
    BEGIN 
       DECLARE @a INT 
    END
    BEGIN 
       DECLARE @a INT 
    END  
END
GO

Possible workaround for temporary tables (if for some reason you desperately need to create twice temporary table with the same name inside the batch) is to alter table schema instead of dropping and recreating from scratch.

CREATE TABLE #t1 (col1 int);
ALTER TABLE #t1 ADD col2 VARCHAR (32);
ALTER TABLE #t1 DROP COLUMN col1 
GO

Remember Point 1:

The only exception is when a stored procedure references another stored procedure that doesn't exist. In that case, a warning message will be issued, but the stored procedure will still be created

Create proc #p2

As

Exec #p3

Go

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object '#p3'. The stored procedure will still be created.

Thanks..