Temporary Objects 2

  • Comments posted to this topic are about the item Temporary Objects 2

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Hi Bitbucket,

    The link you provide is not correct.

    http://www.sqlservercentral.com/questions/UNION/88678/http://www.sqlservercentral.com/Forums/Topic1205649-1222-2.aspx

    And there is a typo in the question.

    SELECT Id,,ProductionDate FROM Foobar

    Initially I was confused whether it has to do anything with the question itself.

    Anyways finally managed to get it right. 🙂

  • Once again I looked at my notes and here is the link

    Posted 11/15/2011 1:34 PM

    http://www.sqlservercentral.com/Forums/Topic1205649-1222-2.aspx

    Mr Hugo Kornelis comment is

    in my display mode is at the bottom of page 2 of the forum

    The title of the forum is Table variable - 2

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Once again I looked at my notes and here is the link

    Posted 11/15/2011 1:34 PM

    http://www.sqlservercentral.com/Forums/Topic1205649-1222-2.aspx

    Mr Hugo Kornelis comment is

    in my display mode is at the bottom of page 2 of the forum

    The title of the forum is Table variable - 2

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • The answer is I think incorrect:

    SELECT Id,,ProductionDate FROM Foobar - if you assume this is not a typo then both first and second users will receive an error message - I demand a recount!

  • The real answer is: the proc cannot be created because of the flagrant syntax violation.

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

  • I am embarassed to admit that I got this question, that apparently was based on my comments, wrong.

    I should have been warned. I even checked the code several times. All because I thought that if both programs run without error (as I assumed), there would have been THREE correct answers. So I checked. And checked again.

    I did contemplate the extra comma, but figured that this would not be Ron's style, so it had to be a genuine type (hint: test all code before submitting QotD!)

    I did also contemplate the inconsistent use of upper- and lowercase, but figure that wouldn't be Ron's style either (hint: test all code on a database with case-sensitive collation)

    And after reading the script yet another time, I ticked the first three options - and then kicked myself for overlooking the named constraint.

    Thanks, Ron! Good question (though unfortunately hampered by the typo and the inconsistent upper-/lowercase).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Well, I didn't catch the error on the select from Foobar, but just thought about locking while selecting/inserting.

    I didn't really look into the temp table as this should be local to the procedure, but the constraint eluded my reading abilities. I have on other occations been bitten by ghe "named sometning" in a procedure, so I should be warned.

    Anyway, the "select any two correct" should have hinted at the correct answer. If we disregard deadlocks between concurrent runs, then instance #2 should always wait for instance #1, så if it were possible to complete #2 at all, it should. And as others have pointed out: If both completed, you would have 3 check marks.

  • That one took me about 20 minutes of careful thought to get what the problem was - I knew there had to be one somewhere (besides the obvious typo). Useful to be aware of the issue.

  • Got this right as I was hit by it a few weeks ago after an attempt to "tidy up" some code by naming the constraints :blush:

  • Even if you hadn't added the constraint to the table, the second program would still fail because there would already be the temp table from the first program.

    I wrote a small bit on my blog about what goes on in tempdb as regards temporary object names, which kind of touches on why you can have multiple table variables of the same name, but not multiple temp tables.

    http://sqlmunkee.blogspot.com/2011/09/temporary-variable-couple-of-points.html

    [font="Courier New"]sqlmunkee[/font]
    [font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]

  • sqlmunkee (3/9/2012)


    Even if you hadn't added the constraint to the table, the second program would still fail because there would already be the temp table from the first program.

    That's not true. You can create as many identically named temp tables as you want provided they are in different sessions (even 2 different connections for the same user).

  • Toreador (3/9/2012)


    sqlmunkee (3/9/2012)


    Even if you hadn't added the constraint to the table, the second program would still fail because there would already be the temp table from the first program.

    That's not true. You can create as many identically named temp tables as you want provided they are in different sessions (even 2 different connections for the same user).

    yes, the temp table with same name can definitely existed in different sessions.

  • I agree with monkeyloafer the answer is wrong

  • You're right, and for the reasons in my blog post 🙂

    My bad. Sorry, I'll get my coat.

    [font="Courier New"]sqlmunkee[/font]
    [font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]

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

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