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/%5B/url%5D.
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
March 12, 2012 at 7:34 am
I got it for the right logical reason but on the wrong object (constraint, not temp table). Great question!
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply