March 9, 2012 at 5:21 am
Hugo Kornelis (3/9/2012)
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).
I am too use to using a case insensitive collation .. another bad habit that I have developed. I did test the code BEFORE submitting for a QOD, and that extra comma must have slipped in at that time, now that is not a valid excuse (as I well know), what can I say except things happen, and it is my fault for which I apologize to all.
March 9, 2012 at 5:21 am
Good straightforward question and answer.
Easy for me, as it was from Hugo's first comment that I first discovered that people could have named constraints (instead of letting SQLServer generate the constraint names) on temporary tables, and that was sufficiently recent (just last November) for me not to have forgotten the shock (shock because I could see where it would probably lead and so immediately tested it, with a result that was confirmed by Hugo's second comment in that discussion).
Tom
March 9, 2012 at 5:49 am
L' Eomot Inversé (3/9/2012)
Good straightforward question and answer.Easy for me, as it was from Hugo's first comment that I first discovered that people could have named constraints (instead of letting SQLServer generate the constraint names) on temporary tables, and that was sufficiently recent (just last November) for me not to have forgotten the shock (shock because I could see where it would probably lead and so immediately tested it, with a result that was confirmed by Hugo's second comment in that discussion).
I also followed your path, especially since it was Hugo commenting on another QOD that I had published. The number of comments for that QOD was rather sparse and I felt that more people should be aware of the pitfalls of what at first appearence appears to be innocuous. Hence this QOD.
March 9, 2012 at 6:30 am
I think of this having less to do with temporary objects so much as it does with schema restrictions: you can't have 2 objects with the same name in the schema. But it's easier to make the mistake when creating temp tables, b/c you can create many temp tables with the "same" name.
I found posted examples of this problem here: http://sequelserver.blogspot.com/2006/12/named-constraints-on-temp-tables.html and here: http://www.sqlservercentral.com/articles/63472/[/url].
Also, in my haste, I overlooked the explicitly named constraint in the question, and I was about to check the first answer (both sessions execute successfully), but you gave it away by telling us to "SELECT THE CORRECT 2 ANSWERS". So I sharpened my eyeballs! Thanks for the question,
Rich
March 9, 2012 at 6:54 am
Typo in the question!!
March 9, 2012 at 7:22 am
Learned something today, thanks.
March 9, 2012 at 7:46 am
I looked at it and since answer 1 also made answer 2 and 3 correct, that was not the answer. So my second take was the correct answer but I had to sit and think was there a gotcha that I was missing. I didn't think this was extremely difficult but it was thought provoking.
March 9, 2012 at 8:03 am
Thinking about this a bit more, the question would have been a bit more difficult if the first answer were eliminated. Then both running to success would have been a potential answer. Either way it made m e think.
March 9, 2012 at 8:36 am
great question. good reminder - cheers
March 9, 2012 at 9:53 am
That typo threw me way off. I want my point back.
Just kidding - I thought Ron might appreciate that.:hehe:
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
March 9, 2012 at 10:10 am
Thanks for the question Ron. Learned something today!
March 9, 2012 at 10:47 am
Great question. Definitely learned something today.
I agree that the typo was a serious distraction, but I thought it was pretty obviously a typo and I assumed that QOTD would NEVER, NEVER try to stump us with a trick question....
😛
Rob Schripsema
Propack, Inc.
March 9, 2012 at 10:55 am
SQLRNNR (3/9/2012)
That typo threw me way off. I want my point back.So OK let you have one of mine ...
Thanks for the laugh I need it and that is for sure (no typo here)
Just kidding - I thought Ron might appreciate that.:hehe:
March 9, 2012 at 12:52 pm
Good question! Good to be reminded of this about constraints on temporary tables.
March 9, 2012 at 1:23 pm
I almost got this wrong but the 'select 2 answers' bit excludes the need for the answer 'both run to completion', which would thus give me 3 answers.
The first option should be removed to reduce second-guessing to the correct answer. I know the bit about not naming Temp Table constraints but I was skimming and didn't really read into it the first time. *shrugs*
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply