Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Temp Tables Expand / Collapse
Author
Message
Posted Friday, May 24, 2013 8:46 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:41 PM
Points: 3,960, Visits: 2,970
It's a good question because it's one of the basics we've all run into at one point or another. I usually handle it by using an ALTER TABLE later in the code when necessary, but (as with everything) it depends on the situation.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1456545
Posted Friday, May 24, 2013 9:00 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 4,386, Visits: 3,396
Carla Wilson-484785 (5/24/2013)
Danny Ocean (5/24/2013)
I really like this question. It's a very import basic question.
Thanks for question.


+1
Thanks!

+1
And more thanks!
Post #1456559
Posted Friday, May 24, 2013 9:51 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:40 AM
Points: 1,719, Visits: 470
Thanks for the great basics question... Discovered this the hardway a few years back!!!
Post #1456593
Posted Friday, May 24, 2013 9:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:13 PM
Points: 21,229, Visits: 14,937
L' Eomot Inversé (5/24/2013)
Quite a fun question, and a good explanation. Thanks for that.

It rather nicely shows up the pitfalls of doing a half-way decent job of parsing. The "table already exists" message is of course spurious - the table doesn't exist - but could easily mislead someone into wasting time looking for the "existing" table. If the contents of the THEN and ELSE branches were swapped, the use of USER as a column name would be detected as a syntax error and the table already exists message would not be generated. If the THEN branch is as in the question and the ELSE branch and the keyword ELSE are deleted, there will be no syntax error but DROP TABLE will fail at run time with an object doesn't exist error. This behaviour is pretty silly.

This muddle doesn't exists when creating a view, a procedure, or a function, because rules were introduced to prevent it. Perhaps the MS team considered that permitting conditional table creation was more important than avoiding the muddle. In my view it would have been better to have a rule for table creation of same protective type as the rule for views and avoid the muddle. Or of course to have a parser that detects multiple errors, like the parsers for many other languages, instead of giving up on the first one, and/or to keep the rule that statements that create a table with can't appear twice in the same batch regardless of control flow but provide an appropriate error message quite distinct from the "table already exists" error message.

Good practise is of course never to write code that includes conditional table creation, and it's common for it to be the first statement in a batch (the preceding batch is a conditional drop). So requiring a table creation statement to come before any non-DDL code in a batch would work; but procs have to have a batch each, and a view definition has to be the first statement in a batch, it can't be preceded by any statement, not just not by any non-DDL statement, so I wouldn't see any difficulty with requiring create table to the first statement in a batch. But there's a risk that it would kill a lot of the code out there in the wild, because best practise is not followed by all. So probably it's now too late to do "the right thing" and protect create table in the same way as create view. That makes the parser that doesn't give up the best option, and I suspect that that would be a lot of work starting from the current parser - too much work for MS to consider doing it.


+1




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1456596
Posted Friday, May 24, 2013 10:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 1,342, Visits: 1,512
L' Eomot Inversé (5/24/2013)
Quite a fun question, and a good explanation. Thanks for that.

It rather nicely shows up the pitfalls of doing a half-way decent job of parsing. The "table already exists" message is of course spurious - the table doesn't exist - but could easily mislead someone into wasting time looking for the "existing" table. If the contents of the THEN and ELSE branches were swapped, the use of USER as a column name would be detected as a syntax error and the table already exists message would not be generated. If the THEN branch is as in the question and the ELSE branch and the keyword ELSE are deleted, there will be no syntax error but DROP TABLE will fail at run time with an object doesn't exist error. This behaviour is pretty silly.


+2

I cringed at the wording of the correct answer "Code fails because object with name #Table already exists in database".

Some days I make this harder than it needs to be.
Post #1456601
Posted Saturday, May 25, 2013 2:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:48 AM
Points: 1,118, Visits: 1,582
Very Good One.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1456774
Posted Monday, May 27, 2013 12:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 27, 2013 5:37 AM
Points: 306, Visits: 524
i faced that issue so i knew the ans



Neeraj prasad sharma
[url=http://tutorialsqlserver.com/][/url]







Neeraj Prasad Sharma
Sql Server Tutorials
Post #1456975
Posted Monday, May 27, 2013 2:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
Good question, but the answer is wrong.

SQL Server evaluates object references during parse time. Flow-control logic is not applied at this stage.

This is not true. If you change the temporary table to a regular table (e.g. dbo.Tab), the script will work.
DECLARE @MoreColumns bit;
SET @MoreColumns = 1

IF @MoreColumns = 0
CREATE TABLE dbo.Tab (
id int,
name varchar(50)
);
ELSE
BEGIN
CREATE TABLE dbo.Tab (
id int,
name varchar(50),
Description varchar(8000),
[USER] nvarchar(200)
)
END
GO
DROP TABLE dbo.Tab

If more than one temporary table is created inside a single stored procedure or batch, they must have different names
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/1e068443-b9ea-486a-804f-ce7b6e048e8b.htm
Obviously SQL Server does not care if the two create table statements are mutually exclusive and does not allow that.


Best Regards,
Chris Büttner
Post #1456999
Posted Monday, May 27, 2013 3:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 27, 2013 5:37 AM
Points: 306, Visits: 524
1 +

Neeraj Prasad Sharma
Sql Server Tutorials
Post #1457026
Posted Monday, May 27, 2013 4:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:00 AM
Points: 252, Visits: 225
If more than one temporary table is created inside a single stored procedure or batch, they must have different names
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/1e068443-b9ea-486a-804f-ce7b6e048e8b.htm
Obviously SQL Server does not care if the two create table statements are mutually exclusive and does not allow that.[/quote]

+1 nice question


Manik
You cannot get to the top by sitting on your bottom.
Post #1457051
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse