I have been aware of temporary stored procedures in Microsoft SQL Server for a long time but never really had cause to use them, however, recently a need arose.
I was testing what effect some index changes would have on a particular stored procedure. The testing was on a non-production server, and when I went to execute the stored procedure I found it was asking me for a parameter I was not aware of. This was because the stored procedure on the non-production server was a newer version that had not yet been deployed to production. I needed therefore to create the production stored procedure on the non-production server. Normally this is a case of scripting out the stored procedure and recreating with _test, _new etc at the end of the name; however, I have a habit of leaving these stored procedures lying around and forgetting to delete them so I thought I’d give the temporary stored procedure a shot and it did the job nicely.
One thing this got me thinking, however was, if this stored procedure is created in tempdb (because it is a temporary object) why don’t we need to prefix all the objects in the stored procedure with the database name? Why doesn’t SQL Server require a database prefix for the objects, and how does it know which database to look in?
Setting Up the Test
Let’s do some testing:
First, we’ll create two databases, one will have a single table with a single column and a single value:
USE [master];
CREATE DATABASE Jack;
GO
CREATE DATABASE Jill;
GO
USE Jack;
GO
CREATE TABLE dbo.JacksTable
(
Id INT
);
INSERT INTO dbo.JacksTable VALUES (1);
Now, we’ll switch to the context of the Jack database and create a temporary stored procedure:
USE Jack;
GO
CREATE PROCEDURE #Temp
AS
SELECT *
FROM dbo.JacksTable;
GO
And now let’s execute it:
USE Jack;
GO
EXEC #Temp;
How did SQL Server know that dbo.JacksTable was in the Jack database, given that the stored procedure is created in tempdb and we haven’t prefixed the objects? Was it because we ran it in the context of the Jack database?
Let’s run it in the Jill database (which does not have a table called dbo.JacksTable) and find out:
USE Jill;
GO
EXEC #Temp;
Same result, but why?
Let’s drop the procedure:
DROP PROCEDURE #Temp;
Now, we’ll recreate it, but this time, we’ll run the create in the Jill database context:
USE Jill;
GO
CREATE PROCEDURE #Temp
AS
SELECT *
FROM dbo.JacksTable;
GO
and now execute the stored procedure in the Jill database:
USE Jill;
GO
EXEC #Temp;
We get an error to tell us the table doesn’t exist, which means SQL Server is not resolving the object name against the Jack database:
Msg 208, Level 16, State 1, Procedure #Temp, Line 4 [Batch Start Line 2]
Invalid object name 'dbo.JacksTable'.
Let’s execute it against the Jack database where we know the table does exist:
USE Jack;
GO
EXEC #Temp;
We get the same error:
Msg 208, Level 16, State 1, Procedure #Temp, Line 4 [Batch Start Line 2]
Invalid object name 'dbo.JacksTable'.
So it appears that SQL Server resolves the object names against the database that was in context when the stored procedure was created…or does it?
Enter Deferred Name Resolution
This article states that when a stored procedure is created, it is checked for syntax and stored in the sys.sql_modules catalog view and that the names are not resolved until execution time.
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 and it allows us to reference objects in stored procedures that do not yet exist at creation time, such as temporary tables.
So is this what SQL Server was doing with our temporary stored procedure? Was it resolving the names to the database in context at execution time? Let’s find out:
We’ll drop the stored procedure again:
DROP PROCEDURE #Temp;
Then recreate it in the scope of the Jill database:
USE Jill;
GO
CREATE PROCEDURE #Temp
AS
SELECT *
FROM dbo.JacksTable;
GO
However, this time we’ll execute against the Jack database:
USE Jack;
GO
EXEC #Temp;
Msg 208, Level 16, State 1, Procedure #Temp, Line 4 [Batch Start Line 2]
Invalid object name 'dbo.JacksTable'.
We get the binding error again so it cannot be Deferred Name Resolution at play as that was our first execution and it was against the database where dbo.JacksTable does exist.
In my testing, at this point I was back to the theory that it is indeed the database in context that is used for object binding and I sought to prove that, though I couldn’t find anywhere in the relevant DMVs that stored this information. I asked a question on DBA StackExchange and found my answer with the help of a user called Martin Smith who I have read a lot of great answers from over the years.
Martin proved that this information is actually stored in a system base table that is only available for querying when connecting using the DAC (Dedicated Admin Connection) – sys.sysschobjs. As an aside, this DAC behaviour is described in this article which states:
To bind to a system base table, a user must connect to the instance of SQL Server by using the dedicated administrator connection (DAC). Trying to execute a SELECT query from a system base table without connecting by using DAC raises an error.
Martin’s query is below, slightly re-written in my own style:
USE tempdb;
SELECT p.id,
DB_NAME(r.indepid) AS DB,
p.name AS proc_name,
REVERSE(CAST(REVERSE(v.imageval) AS NVARCHAR(MAX))) AS proc_definition
FROM sys.sysschobjs p
JOIN sys.syssingleobjrefs r
ON p.id = r.depid
JOIN sys.sysobjvalues v
ON v.objid = p.id
WHERE p.name LIKE '#Temp%' AND
p.type = 'P' AND
r.class = 70 AND
r.depsubid = 0;
If we run the above in a separate session, connected via the DAC (don’t change the current session to DAC as that will end the old session and drop the temp procedure!) we get the output below. To connect to the DAC, open a new query window and connect to ADMIN:YourInstanceName (disable IntelliSense first, as that may steal the connection) Verify the DAC is enabled with:
SELECT [value] FROM sys.configurations WHERE [name] = 'remote admin connections';
A value of 1 will be returned if the DAC is enabled.
Et voilà! The DB column shows the database where the objects are bound. This column is derived from DB_NAME() wrapping sys.syssingleobjrefs.indepid though I can’t find any documentation on what the columns of this table mean.
Non-Temporary Stored Procedure
As it happens, I get the exact same behaviour on a non-temporary stored procedure. We’ll create the same stored procedure but this time in the Jack database itself:
USE Jack;
GO
CREATE PROCEDURE dbo.Temp
AS
SELECT *
FROM dbo.JacksTable;
GO
Then we’ll execute from the Jill database
USE Jill;
EXEC Jack.dbo.Temp;
We can see JacksTable:
Finally, for the sake of completeness, the other way round:
USE Jill;
GO
CREATE PROCEDURE dbo.Temp
AS
SELECT *
FROM dbo.JacksTable;
GO
USE Jack;
GO
EXEC Jill.dbo.Temp;
Once again, we get the binding error:
Msg 208, Level 16, State 1, Procedure Jill.dbo.Temp, Line 4 [Batch Start Line 11]
Invalid object name 'dbo.JacksTable'.
Conclusion
We have seen that the database context used for name resolution is fixed at the time the stored procedure is created, regardless of which database is current at execution time.
References / Further Reading
dba.stackexchange – How does name resolution work with temporary stored procedures?
Microsoft – Deferred Name Resolution and Compilation (SQL Server 2005)