October 23, 2017 at 7:53 am
I came across something interesting while troubleshooting a developer's code recently. We use temporary tables which are populated by executing dynamic SQL. For example:
-- Create the temporary holding space by calling an existing view:
SELECT fldA, fldB, fldC INTO #temp FROM myView WHERE myCriteria
-- Build and execute SQL
DECLARE @sql NVARCHAR (500)
SET @sql = 'INSERT INTO #tmp fldA, fldB, fldC Select ........'
The developer was stumped because the temp table kept coming up empty even though when you print and run the @sql it returns results. There was an error in the code as seen here:
-- Build and execute SQL
DECLARE @sql NVARCHAR (500)
SET @sql = 'SELECT fldA, fldB, fldC INTO #tmp FROM ........'
I assume that in the first instance SQL knows that there is a temp table in scope and populates it. In the second instance SQL sees that you are trying to create a temp table and creates it in the special session belonging to the SP_executeSQL function. I was surprised that there was not a name collision.
Interesting. ST
October 23, 2017 at 9:49 am
Have you heard of "global" temp tables? The names all start with ##. Google that and you'll likely find the scope information you seek.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 23, 2017 at 11:07 am
Actually, I don't need the global temp tables because I want them to be specific to a connection/session. I just found it odd that there was no error thrown when the temp table already exits for this connection but the executeSQL tried and successfully created a temp table with the same name.
October 23, 2017 at 1:02 pm
You can get information about temp tables' scope in here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql#temporary-tables
October 23, 2017 at 3:24 pm
souLTower - Monday, October 23, 2017 11:07 AMActually, I don't need the global temp tables because I want them to be specific to a connection/session. I just found it odd that there was no error thrown when the temp table already exits for this connection but the executeSQL tried and successfully created a temp table with the same name.
There was no error. The scope was different and a different temp table with a similar name was creater. What you perceive as the name of a Temp Table is only a part of the name. Temp Table names actually are followed by a herd of underscores followed by a unique hex identifier so that people running multiple copies of the code don't step on each other.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2017 at 5:21 am
OK, to more clearly show what I'm seeing I wrote the following code. I just find it odd that in the first example you can see that the table already exists in this context. I would expect the expression to throw an exception but it doesn't. In looking at the system output you can see that a new temp table is created and populated.
As Luis noted earlier, this is explained in BOL (see his link) in the context of 2 stored procedures creating temp tables with the same name.
IF (SELECT OBJECT_ID('tempdb..#tmp')) IS NOT NULL
DROP TABLE #tmp
-- Create an empty table holding ints
SELECT TOP 0 ID INTO #tmp from sysobjects
-- Print the actual name and ID of this object
declare @nm varchar (500)
SELECT @nm = 'ID-' + convert(varchar, OBJECT_ID('tempdb..#tmp')) +
' ' + name from tempdb.dbo.sysobjects WHERE ID = OBJECT_ID('tempdb..#tmp')
print(@nm)
print('Test One')
-- Populate the table and show that in the executed SQL we're pointing to the same table
-- I would expect this to throw an exception since the table already exists
exec sp_executesql N'
declare @nm varchar (500)
SELECT @nm = ''ID-'' + convert(varchar, OBJECT_ID(''tempdb..#tmp'')) +
'' '' + name from tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(''tempdb..#tmp'')
print(@nm)
SELECT TOP 10 ID INTO #tmp from sysobjects
print(''After insterting records'')
SELECT @nm = ''ID-'' + convert(varchar, OBJECT_ID(''tempdb..#tmp'')) +
'' '' + name from tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(''tempdb..#tmp'')
print(@nm)
'
-- Note that the sys out shows 10 rows were affected but the table is empty
SELECT * FROM #tmp
print('Test Two')
-- Populate the table and show that in the executed SQL we're pointing to the same table
-- This method actually updates the temp table
exec sp_executesql N'
declare @nm varchar (500)
SELECT @nm = ''ID-'' + convert(varchar, OBJECT_ID(''tempdb..#tmp'')) +
'' '' + name from tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(''tempdb..#tmp'')
print(@nm)
INSERT #tmp (ID) SELECT TOP 10 ID from sysobjects
'
-- Note that the sys out shows 10 rows were affected and the table is not empty
SELECT * FROM #tmp
October 24, 2017 at 9:16 am
The below line in the dynamic SQL of 'Test One' is used to create a table from the result of a SELECT query,
SELECT TOP 10 ID INTO #tmp from sysobjects
The #tmp table have already been created in the begining of the sample code with
-- Create an empty table holding ints
SELECT TOP 0 ID INTO #tmp from sysobjects
Hence ideally, SQL Server should throw an error saying a table with same name exists. Instead it does nothing - no errors nor populate the table.
This is what I find very strange. The only possible explanation is that, these two #tmp are in different scope.
You can verify it by including a SELECT statement at the end of the dynamic SQL.
exec sp_executesql N'
declare @nm varchar (500)
SELECT @nm = ''ID-'' + convert(varchar, OBJECT_ID(''tempdb..#tmp'')) +
'' '' + name from tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(''tempdb..#tmp'')
print(@nm)
SELECT TOP 10 ID INTO #tmp from sysobjects
print(''After insterting records'')
SELECT @nm = ''ID-'' + convert(varchar, OBJECT_ID(''tempdb..#tmp'')) +
'' '' + name from tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(''tempdb..#tmp'')
print(@nm)
SELECT * FROM #tmp --- this will return the values inserted in the #tmp table in this scope
'
However, the below line populates the #tmp table because it is an INSERT statement to populate an existing table from the result of a SELECT statement.
INSERT #tmp (ID) SELECT TOP 10 ID from sysobjects
October 24, 2017 at 10:01 am
Except #tmp is unique if you were to look in tempdb. The local #tmp table is uniquely named in tempdb. This has to be true for local temporary tables or you could not write a stored procedure that used a local temporary table and have it called by to separate processes at the same time. The local temporary tables would conflict if they weren't unique in each of the sessions.
If you create a #tmp table inside the dynamic SQL it is created with a unique name and since it is created within the scope of the dynamic SQL it hides the #tmp create prior to the running of the dynamic SQL.
October 24, 2017 at 11:34 am
Is it basically the same behavior shown here?
https://www.brentozar.com/archive/2017/09/ghosts-temp-tables-past/
October 24, 2017 at 1:50 pm
Luis Cazares - Tuesday, October 24, 2017 11:34 AMIs it basically the same behavior shown here?
https://www.brentozar.com/archive/2017/09/ghosts-temp-tables-past/
So it doesn't hide the table from the outer scope. Interesting. Of course when I relied on this i only create the temporary table(s) if they didn't already exist. I wanted to be able to debug my procedures independently from the procedure that would call the child procedures.
October 24, 2017 at 5:12 pm
Lynn Pettis - Tuesday, October 24, 2017 1:50 PMLuis Cazares - Tuesday, October 24, 2017 11:34 AMIs it basically the same behavior shown here?
https://www.brentozar.com/archive/2017/09/ghosts-temp-tables-past/So it doesn't hide the table from the outer scope. Interesting. Of course when I relied on this i only create the temporary table(s) if they didn't already exist. I wanted to be able to debug my procedures independently from the procedure that would call the child procedures.
It's not the same thing. There's only one scope in the example in that link. Despite the table drop, there are two definitions for the same table in the same scope and that's going to cause an error.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2017 at 5:33 pm
Jeff Moden - Tuesday, October 24, 2017 5:12 PMLynn Pettis - Tuesday, October 24, 2017 1:50 PMLuis Cazares - Tuesday, October 24, 2017 11:34 AMIs it basically the same behavior shown here?
https://www.brentozar.com/archive/2017/09/ghosts-temp-tables-past/So it doesn't hide the table from the outer scope. Interesting. Of course when I relied on this i only create the temporary table(s) if they didn't already exist. I wanted to be able to debug my procedures independently from the procedure that would call the child procedures.
Okay, tested the second one (#dumb table) and found this.
This errors:
IF OBJECT_ID('tempdb..#dumb') IS NOT NULL
DROP TABLE #dumb
CREATE TABLE #dumb (Id INT)
INSERT #dumb ( Id )
VALUES ( 0 )
IF OBJECT_ID('tempdb..#dumb') IS NOT NULL
DROP TABLE #dumb
CREATE TABLE #dumb (Id INT)
INSERT #dumb (Id)
VALUES ( 0 )
This works:
IF OBJECT_ID('tempdb..#dumb') IS NOT NULL
DROP TABLE #dumb
CREATE TABLE #dumb (Id INT)
INSERT #dumb ( Id )
VALUES ( 0 )
GO
IF OBJECT_ID('tempdb..#dumb') IS NOT NULL
DROP TABLE #dumb
CREATE TABLE #dumb (Id INT)
INSERT #dumb (Id)
VALUES ( 0 )
GO
And I am sure it is a parsing issue as you are trying to create the same table name in a single batch.
October 24, 2017 at 7:39 pm
Correct. The GO has changed the scope to another batch.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply