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.