True story
You may find it hard to believe, but I recently had to fix a small bug in sp_BlitzCache and sp_BlitzQueryStore.
Since both stored procedures have similar functions, they also share some temp table names (mainly the ones for parsing down XML nodes to more manageable chunks).
In the window where I was making adjustments to sp_BlitzCache, I had created one of those temp tables. When I went to run sp_BlitzQueryStore, I was flooded with invalid column name errors. Same temp table name, different column names.
Now, I hadn’t made any changes to BlitzQueryStore yet, and it had been running without error as of the last FRK release.
When I ran sp_BlitzQueryStore from a different SSMS window, it ran fine.
Even though the proc explicitly checks for and drops my temp tables, it was reading from temp tables my session created with different column names.
So what gives?
Demonstrate My Syntax
You’ll need a couple SSMS windows for this.
Run this in one of them. Note that the column name in #t1 is t, here.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
USE master GO CREATE OR ALTER PROCEDURE dbo.temp_maker AS BEGIN DROP TABLE IF EXISTS #t1; CREATE TABLE #t1 ( t INT ); INSERT #t1 ( t ) SELECT TOP 10000 x.r FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS r FROM sys.messages AS m) AS x SELECT t.t FROM #t1 AS t; END; GO |
In window #2, run this. Note that the column name in #t1 is c, here.
|
1 2 3 4 5 6 7 8 9 |
USE master CREATE TABLE #t1 (c INT) INSERT #t1 ( c ) SELECT TOP 10000 x.r FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS r FROM sys.messages AS m) AS x EXEC dbo.temp_maker |
You should get this error (sometimes you have to run the create table statement a couple times in a row, then the proc — I can’t nail it down exactly).
Msg 207, Level 16, State 1, Procedure temp_maker, Line 13 [Batch Start Line 9] Invalid column name ‘t’.
Msg 207, Level 16, State 1, Procedure temp_maker, Line 17 [Batch Start Line 9] Invalid column name ‘t’.
Weird! FWIW, it also throws the same error if I check for the OBJECT_ID of #t1 instead.
Another dumb creation
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DROP TABLE IF EXISTS #dumb CREATE TABLE #dumb (Id INT) INSERT #dumb ( Id ) VALUES ( 0 ) DROP TABLE IF EXISTS #dumb CREATE TABLE #dumb (Id INT) INSERT #dumb (Id) VALUES ( 0 ) |
Chaos Chaos
I’m sure there’s a fun explanation for this. Perhaps it’s something to do with deferred compilation, or the magic of temp tables in stored procedures.
This isn’t the most practically helpful post. It’s mostly so I remember to avoid doing this again in the future.
And who knows, maybe it’ll save you some time, too.
Thanks for reading!
Reference posts:
Linchi Shea
Sebastian Meine
18 Comments. Leave new
I’ve always thought that temporary tables starting with # were unique by SP, aond only those starting with ## had visibility outside the SPs it was created.
But anyway, I have started using TABLE variables instead of traditional temp tables. So i think this wont happen to me.
Thanks for the hint.
Maybe, maybe not, but you can bet lots of other terrible things will happen to you. Do yourself a favor and read up about table variables before making the switch. Heck, check out our session for this year’s 24HOP event.
I´ve learned SQL all by myself, so, i have a lot yet to learn, planning to study to become a DBA someday.
I´ll search for table variables in the blog to understand your alert about it.
Thanks again.
If I truly understand what you are doing, it looks like a parse error, as though the procedure isn’t truly compiled but parses the text for obvious errors. Since the temp table #t exists in the outer scope with c as its only column, the procedures can see it before running and see that the column name in the select statement should be c but it’s not it’s so you get the error. I don’t have the latest version of SQL (government is stuck on 2012) but could this be related to the fact that you can’t create the same temp table twice in a procedure even with the if exists drop statement, because the parser sees that you are trying to create a table that already exists and is not smart enough to notice that you are dropping it before trying to recreate? After all it is as though #t already exists at the top of the procedure when it is run.
Pretty sure the answer lies in the Paul White article you mention. SQL doesn’t actually drop the table, if it can be cached, it renames it to an internal name and reuses it (sometimes with old statistics), fun! For Mr. Rodrigues above, I always avoid using table variables (unless I only have 1 row), and use unique table names across stored procedures.
*snip from PW’s lengthy article 🙂
…CREATE and DROP, Don’t
I will talk about this in much more detail in my next post, but the key point is that CREATE TABLE and DROP TABLE do not create and drop temporary tables in a stored procedure, if the temporary object can be cached. The temporary object is renamed to an internal form when DROP TABLE is executed, and renamed back to the same user-visible name when CREATE TABLE is encountered on the next execution. In addition, any statistics that were auto-created on the temporary table are also cached.”…
I came across a similar and frustrating issue the other day when I had a temp table whose definition was based on a condition.
IF @condition = 1
CREATE TABLE #table (col VARCHAR(10))
ELSE
CREATE TABLE #table(col CHAR(1)
I got the error, ‘There is already an object named ‘#table’ in the database.’. The parser must be seeing the first CREATE and not realising that the second is on a different logic path.
If you want to be really annoyed, try this:
IF 1 = 2BEGIN
DECLARE @i int = 5;
END;
SELECT @i;
GO
IF 1 = 2
BEGIN
DECLARE @i INT;
END;
SET @i = 1;
SELECT @i;
GO
It does declare the variable even if the condition is false, but does not run the attribution
There is nothing to be surprised about, but these errors can lead to a stupor. This is the standard work of the compiler.
Use the GO batch separator after DROP TABLE IF EXISTS # dumb (last example) – the script will execute successfully.
Because GO is not a T-SQL statement, the best way to avoid an error in the first example is to use the existence check of such an object in the current session, for example:
IF OBJECT_ID (‘tempdb.dbo.#T1’) IS NOT NULL
BEGIN
PRINT (‘The current session has a temporary table that prevents the start of the procedure! Open a new session and run the procedure again.’);
RETURN;
END;
What about inside a stored procedure?
In fact, even the method I described above will not work (I checked it).
Explanation of this behavior:
https://msdn.microsoft.com/en-us/library/ms174979(v=sql.110).aspx#Anchor_2
“A local temporary table created within a stored procedure or trigger can have the same name as a temporary table that was created before the stored procedure or trigger is called. However, if a query references a temporary table and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against. Nested stored procedures can also create temporary tables with the same name as a temporary table that was created by the stored procedure that called it. However, for modifications to resolve to the table that was created in the nested procedure, the table must have the same structure, with the same column names, as the table created in the calling procedure.”
The moral of the story is: name everything with GUIDs.
Great way!
Absolutely agree with you.
Sorry if this is a dumb question, but there it goes…
Imagine if i create a table #t1 inside a procedure.
If the same user, make 2 simultaneous calls to that procedure, both calls will use the same table? The data will b mixed?
For Q&A, you should head over to https://dba.stackexchange.com/
Thanks!
I just was discussing the theme, and the previous posts lead me to the question i made. Like the one you just made before mine. Maybe we both should use the link you posted for Q&A.
But… I´m not the Blog Owner anyway… That´s a BIG difference!
I found myself the answer: https://dba.stackexchange.com/questions/116240/can-two-sessions-create-temp-tables-with-the-same-name
Thanks
This is ok:
DROP TABLE IF EXISTS #dumb
CREATE TABLE #dumb (Id INT)
INSERT #dumb ( Id )
VALUES ( 0 )
EXEC(‘DROP TABLE IF EXISTS #dumb
CREATE TABLE #dumb (Id INT)
INSERT #dumb (Id)
VALUES ( 1 )’)
But this also fails:
DROP TABLE IF EXISTS #dumb
CREATE TABLE #dumb (Id INT)
INSERT #dumb ( Id )
VALUES ( 0 )
EXEC(‘DROP TABLE IF EXISTS #dumb
CREATE TABLE #dumb (Id INT)
INSERT #dumb (Id)
VALUES ( 1 )’)
select * FROM #dumb