November 24, 2015 at 1:30 pm
I'm looking for a reference on why this does not work I expect (It throws an error)
begin transaction
select 1 as a, 2 as b into #temp
drop table #temp
select 1 as a, 2 as b, 3 as c into #temp
commit
select * from #temp
produces an error message:
Msg 2714, Level 16, State 1, Line 5
There is already an object named '#temp' in the database.
However, if I remove the #'s and run again, all is well.
So, what is it about temporary tables and transactions? Is there a good reference I can look up?
Gerald Britton, Pluralsight courses
November 24, 2015 at 1:32 pm
Nothing to do with transactions. That's a parse-time error (well, binding phase, not the parser itself). The algebriser sees that the object is being created twice as it binds the queries (before execution even starts) and throws an error at that point.
Not sure why permanent tables behave differently, could be deferred compile. During binding, the algebriser sees that the table name doesn't exist at the start and doesn't try to bind any statements referencing it, hence doesn't throw the error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2015 at 1:38 pm
GilaMonster (11/24/2015)
Nothing to do with transactions. That's a parse-time error (well, binding phase, not the parser itself). The parser sees that the object is being created twice as it parses the batch (before execution even starts) and the parser throws an error.Not sure why permanent tables behave differently, could be deferred compile. During binding, the algebriser sees that the table name doesn't exist at the start and doesn't try to bind any statements referencing it, hence doesn't throw the error.
Thanks, Gail.
Weird though (wrt permanent tables).
Gerald Britton, Pluralsight courses
November 24, 2015 at 2:04 pm
I believe it may be due to the deferred resolution SQL uses for permanent tables that don't exist during parsing. Then again, maybe not :blink:
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 24, 2015 at 2:25 pm
ScottPletcher (11/24/2015)
I believe it may be due to the deferred resolution SQL uses for permanent tables that don't exist during parsing. Then again, maybe not :blink:
I'm thinking something else is going on, or something is not documented. deferred resolution is documented within the context of creating a stored procedure, which I get. My example is just a simple script -- no procs. Of course the parser is the same, so it could be just incomplete documentation.
For that matter, why should the behavior for temporary tables be different? A table can be built in one proc and referenced in another one as part of a single process -- maybe not best practice but I've seen it in the wild. So when the parser sees the reference to the temp table, it doesn't know if it exists or not. Not to mention, that I get the error on the second SELECT ... INTO, not any other reference.
For a perm table, it is as if the parser sees the intervening drop statement and says "well, this changes things!" but not so for temp tables
FWIW the only doc I could google on the subject is for 2008R2, Nothing more recent that I could find easily
Gerald Britton, Pluralsight courses
November 24, 2015 at 2:30 pm
Well, it's not quite undocumented; they just explicitly disallowed this.
From https://msdn.microsoft.com/en-us/library/ms174979.aspx:
If more than one temporary table is created inside a single stored procedure or batch, they must have different names.
It would be nice if they explained exactly why they disallowed it, but they do at least say you can't do it.
Also from https://connect.microsoft.com/SQLServer/feedback/details/666430/temp-table-with-the-same-name-create-drop-and-create-gives-error, they responded similarly, saying:
Hello,
The behavior you are seeing is by design. Within the same batch or module, we don't allow creation of temporary table with the same name more than once. If you want to then please separate them into multiple batches or use dynamic SQL for different batches. We don't plan to change this behavior so I am closing this as by design.
--
Umachandar, SQL Programmability Team
It would be nice to have an explanation and not just "You can't do it", but c'est la vie 🙂
November 24, 2015 at 2:33 pm
good find! that settles it then
Gerald Britton, Pluralsight courses
November 24, 2015 at 2:41 pm
g.britton (11/24/2015)
I'm thinking something else is going on, or something is not documented. deferred resolution is documented within the context of creating a stored procedure, which I get.
I'm pretty sure it's deferred compile. A batch behaves the same as a create procedure for the purposes of parsing and binding. Difference is that a batch goes on to be executed directly, while the contents of a create procedure are executed later
The parser won't bind permanent tables that don't exist (compile is deferred until the statement executes, as opposed to the batch), but it appears to do some form of cursory binding or checking on temp tables, probably because it's far more likely for someone to make mistakes like creating the same temp table twice than it is for a permanent table to be created twice in a batch. It's not a full binding, you still see deferred compile on things like a DROP TABLE #DoesntExist
For a perm table, it is as if the parser sees the intervening drop statement and says "well, this changes things!" but not so for temp tables
I think it's more a case that the parser goes "Don't know that table, going to ignore that statement for now, will worry about it at the time that it executes"
RAISERROR ('Hello',10,1)
CREATE TABLE T1 (i INT)
CREATE TABLE T1 (i INT)
Error occurs at the point of the second CREATE TABLE executing
RAISERROR ('Hello',10,1)
CREATE TABLE #T1 (i INT)
CREATE TABLE #T1 (i INT)
Nothing executes, error raised before execution of batch starts
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply