THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Temp table name resolution

I was reading Michael Coles' post on Dynamic SQL and Late Binding, and it reminded me of an issue I struggled with a while back. The issue started out as a local temp table name resolution issue. But it was also a compilation time vs. run time issue, or a late binding issue.

 

Since this is a slightly different take on the same issue that Michael’s post deals with, I’m posting the scenario in the chronological order as I originally went through it.

 

So it started out with this question: does T-SQL allow the same name be given to different temp tables in the same session? If yes, how does it resolve a name reference in such a case?

 

Obviously, you would get an error with the following script since you can’t have duplicate table names in the same scope:

 

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

   DROP TABLE #tmp

go  

CREATE TABLE #tmp(i int)

go

CREATE TABLE #tmp(j int)

go

 

But yes, you can create a new local temp table with the same name that is already used by an existing local temp table in the same session. Here is an example:

 

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

   DROP TABLE #tmp

go  

CREATE TABLE #tmp(i int)

EXECUTE ('create table #tmp(j int)')

go

 

Both temp tables are local and belong to the same session. Sure, instead of an EXECUTE() statement, you could also create the same named local temp table inside a stored procedure.

 

But how does SQL Server resolve a local temp table name when there are multiple local temp tables with the same name?

 

We know that a local temp table created in a child batch inside EXECUTE() or inside a stored procedure is not visible in the calling batch after EXECUTE() or the called stored procedure finishes executing.

 

1. IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

2.    DROP TABLE #tmp

3. go  

4. CREATE TABLE #tmp(i int)

5. EXECUTE ('CREATE TABLE #tmp(j int)')

6. SELECT i FROM #tmp

7. SELECT j FROM #tmp

 

In the above case, the #tmp on line 6 is obviously not the one created on line 5. Line 7 would give you an error message, saying that j is an invalid column.

 

This is not all that interesting. A more interesting question is, if we use #tmp inside EXECUTE() after the CREATE TABLE statement, what does #tmp actually refer to? Does it reference #tmp(i int), or does it reference #tmp(j int)?

 

Intuitively, in the following script, you'd expect #tmp in the SELECT statement inside EXECUTE() to reference the local temp table created immediately before it in the same EXECUTE():

 

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

   DROP TABLE #tmp

go  

CREATE TABLE #tmp(i int)

EXECUTE ('CREATE TABLE #tmp(j int); SELECT * FROM #tmp')

go

 

Well, yes and no.

 

Note that there are two steps in executing the T-SQL code inside EXECUTE(). First, the code needs to be compiled, and then the code is executed. Apparently, during the compilation, #tmp(j int) has not been created yet, and SQL Server is aware of the existence of #tmp(i int), and #tmp(i int) is visible inside EXECUTE(). But that reference is not binding (i.e. not persisted). At the execution time, however, it references #tmp(j int). This seems to be a bit confusing. Let's try some more tests.

 

The following script (Script A) gives an error saying that i is an invalid column name.

 

--Script A.

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

   DROP TABLE #tmp

go  

CREATE TABLE #tmp(i int)

EXECUTE ('CREATE TABLE #tmp(j int); SELECT i FROM #tmp')

go

 

That is fine. But what about the following script (Script B)? Now that it uses the correct column name, it should work, right?

 

-- Script B

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

   DROP TABLE #tmp

CREATE TABLE #tmp(i int)

go

EXECUTE ('CREATE TABLE #tmp(j int); SELECT j FROM #tmp')

go

 

Wrong! The script produces the same invalid column name error message (Msg 207). On the surface, the behavior seems to be inconsistent in how the names are resolved. But in fact the Msg 207 produced by Script B is not exactly the same as the Msg 207 error produced by Script A. The difference is that the error from Script A is produced when the bacth inside EXECUTE() is being executed, whereas the error from Script B is produced when the batch inside EXECUTE() is being compiled.

 

So when the code inside EXECUTE() in Script A is being compiled, #tmp(i int) already exists, and the code compiles just fine. But when the code is being executed, it references #tmp(j int) inside EXECUTE(), and the column names do not match, thus the error. Note that the following works just fine:

 

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

   DROP TABLE #tmp

--create table #tmp(i int)

go

EXECUTE ('create table #tmp(j int); select j from #tmp')

go

 

And for the same reason, the following script will give you the same invalid column name error message:

 

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

   DROP TABLE #tmp

--create table #tmp(i int)

go

EXECUTE ('create table #tmp(j int); select i from #tmp')

go

 

So it's worth remembering that, for a given batch of T-SQL code, there is a compilation phase and there is an execution phase. Some errors may come from the compilation phase, and other errors may come from the execution phase. It would be nice if SQL Server could clearly mark an error message on which phase (compilation or execution) it comes from, and that can help avoid much confusion.

 

Published Thursday, July 15, 2010 6:33 PM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

KevinC. said:

Awesome post.  Thanks for explaining that so well.

July 16, 2010 11:21 PM
 

Manoj Pandey said:

Table variable also gets stored in tempdb. To prove this I executed following code:

CREATE TABLE #tempTab (j INT)

SELECT TABLE_NAME, *

FROM tempdb.INFORMATION_SCHEMA.TABLES

GO

DECLARE @t TABLE (i INT)

SELECT TABLE_NAME,*

FROM tempdb.INFORMATION_SCHEMA.TABLES

GO

1st batch of stmt gives me 12 rows with following table names: ##0171552

##0610305

##1010109

##1324600

##1332257

##REPLMSTR

##REPLMSTR2

#tempTab____________________________________________________________________________________________________________000000019D39

DEX_LOCK

DEX_SESSION

sysconstraints

syssegments

... and the 2nd batch gives me 13 rows: ##0171552

##0171552

##0610305

##1010109

##1324600

##1332257

##REPLMSTR

##REPLMSTR2

#0C4F413A

#tempTab____________________________________________________________________________________________________________000000019D39

DEX_LOCK

DEX_SESSION

sysconstraints

syssegments

Does this means that the new table #0C4F413A is the table variable created in tempdb?

July 20, 2010 4:42 AM
 

Gianluca Sartori said:

This works instead:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

  DROP TABLE #tmp

go  

CREATE TABLE #tmp(i int)

EXECUTE ('CREATE TABLE #tmp(k int); EXEC(''SELECT k FROM #tmp'')')

go

Nice post, well done!

August 12, 2010 3:27 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement