Temp Tables

  • Sergiy

    SSC Guru

    Points: 109836

    Comments posted to this topic are about the item Temp Tables

  • yogyogi97

    SSC Veteran

    Points: 248

    Thanks for the question. easy one on last day of week

    +1

  • Dineshbabu

    Hall of Fame

    Points: 3220

    Good question. Just yesterday Hugo is explaining about parsing in another QotD.

    --
    Dineshbabu
    Desire to learn new things..

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    I really like this question. It's a very import basic question.

    Thanks for question. 🙂

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Easy one to end the workweek. Thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • TomThomson

    SSC Guru

    Points: 104773

    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.

    Tom

  • Mike Is Here

    Hall of Fame

    Points: 3348

    Thanks for the question. I only remember this from a different compiler question.

  • mtassin

    SSC-Insane

    Points: 23099

    Thanks for the question... easy one.... only because I've tried to do this too many times in the past.

    I no longer try to do this... because I've found it generally better to make a pair of stored procs, one for each branch and either call them from another "switchboard" proc or have the code itself determine which one to call.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • SqlOnMyMind

    SSCertifiable

    Points: 5049

    Danny Ocean (5/24/2013)


    I really like this question. It's a very import basic question.

    Thanks for question. 🙂

    +1

    Thanks!

  • malleswarareddy_m

    SSCertifiable

    Points: 5847

    Easy one for the day

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • Ed Wagner

    SSC Guru

    Points: 286985

    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.

  • Revenant

    SSC-Forever

    Points: 42467

    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!

  • Michael Riemer

    SSCertifiable

    Points: 5164

    Thanks for the great basics question... Discovered this the hardway a few years back!!!

  • SQLRNNR

    SSC Guru

    Points: 281252

    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, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • batgirl

    SSCarpal Tunnel

    Points: 4979

    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.

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply