<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by bitbucket
»
Table variable - 2
24 posts, Page 2 of 3
««
1
2
3
»»
Table variable - 2
Rate Topic
Display Mode
Topic Options
Author
Message
bitbucket-25253
bitbucket-25253
Posted Tuesday, November 15, 2011 6:04 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:16 PM
Points: 5,101,
Visits: 20,201
Hugo Kornelis (11/15/2011)
Good questione, but the explanation is a bit too short. I assume Ron intended to write "You cannot create a named constraint
on a table variable
". On temporary and permanent tables, creating names constraints is supported (though it's not really a good idea for temporary tables).
I kept my explanation deliberately short, hopping that people would read the excellent article here on SSC by Wayne Sheffield, and thus learn a great deal more than I could say in a few words.
If everything seems to be going well, you have obviously overlooked something.
Ron
Please help us, help you -before posting a question please
read
Before posting a performance problem please
read
Post #1205886
Britt Cluff
Britt Cluff
Posted Tuesday, November 15, 2011 6:04 AM
SSCommitted
Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:17 PM
Points: 1,558,
Visits: 247
Thanks for the question.
http://brittcluff.blogspot.com/
Post #1205887
Paul White
Paul White
Posted Tuesday, November 15, 2011 6:05 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 7:18 PM
Points: 10,989,
Visits: 10,532
I like this question too. Table variables often get a poor press, but not allowing named constraints (which could well conflict for objects in tempdb) is one thing I like about them. The ability to create named constraints on #temp tables is almost certainly only there for backward compatibility.
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1205888
bitbucket-25253
bitbucket-25253
Posted Tuesday, November 15, 2011 6:14 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:16 PM
Points: 5,101,
Visits: 20,201
Ninja's_RGR'us (11/15/2011)
I love the new question style Ron! Nice way to do this
.
Why thank you for the compliment, it made my morning joyous.
If everything seems to be going well, you have obviously overlooked something.
Ron
Please help us, help you -before posting a question please
read
Before posting a performance problem please
read
Post #1205893
Bob Razumich
Bob Razumich
Posted Tuesday, November 15, 2011 7:42 AM
Old Hand
Group: General Forum Members
Last Login: Today @ 7:33 PM
Points: 376,
Visits: 884
Good question, thank you.
I will "third" liking your question format. It forces us to actually
type
the code if we want to cheat that way before answering the question.
Or conversely, if lazy and not feeling like typing, doing research.
Sorry folks, I'm sure all of us have done that at least once - I freely admit that myself.
Post #1205991
Ninja's_RGR'us
Ninja's_RGR'us
Posted Tuesday, November 15, 2011 7:47 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 6:43 PM
Points: 21,357,
Visits: 9,536
brazumich (11/15/2011)
Good question, thank you.
I will "third" liking your question format. It forces us to actually
type
the code if we want to cheat that way before answering the question.
Or conversely, if lazy and not feeling like typing, doing research.
Sorry folks, I'm sure all of us have done that at least once - I freely admit that myself.
My point is that you can't answer without reading everything. It seems to twart lots of the usual complaints
.
Post #1206003
OzYbOi d(-_-)b
OzYbOi d(-_-)b
Posted Tuesday, November 15, 2011 9:01 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176,
Visits: 778
Great question - cheers
Post #1206082
L' Eomot Inversé
L' Eomot Inversé
Posted Tuesday, November 15, 2011 10:14 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:48 PM
Points: 7,088,
Visits: 7,143
Nice question.
It made me think, briefly, because I was startled by the unnamed single column constraint. I'm so used to writing unnamed single column primary key, unique, reference, and check constraints (and of course default constraints, but they are inherently single column anyway) as part of the column definition that seeing one written elsewhere was a surprise; but after a very rapid reflection realised that as the syntax is available for (unnamed) multicolumn constraints there's no reaon it can't be used for (unnamed) single column constraints as an alternative to including the constraint in the column definition.
From the discussion I learnt that named constraints are allowed on temp tables; that is really awful - table variables have this one right.
Tom
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #1206159
Revenant
Revenant
Posted Tuesday, November 15, 2011 10:24 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 3,454,
Visits: 2,529
Very neat -- thank you!
Post #1206170
Hugo Kornelis
Hugo Kornelis
Posted Tuesday, November 15, 2011 11:34 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 4:30 PM
Points: 5,235,
Visits: 7,038
L' Eomot Inversé (11/15/2011)
From the discussion I learnt that named constraints are allowed on temp tables; that is really awful - table variables have this one right.
Yes, that is awful indeed. I found that out the hard way, when I decided to add "always name constraints" to my list of best practices to follow. I changed some code, tested, then quickly removed the constraint names for the temp tables. For those who never tried - it'll give you errors. If you create a table named #temp, SQL Server will apply some magic to give it a real name that's unique and that maps to the #temp pseudo-name. If you add a constraint named pk_#temp to that table, SQL Server will do no magic at all; it will simply use that constraint name - and give you an error message ("there is already an object named pk_#temp in the database") as soon as a second connection runs the same code before the temporary table from the first connection has been removed.
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog:
http://sqlblog.com/blogs/hugo_kornelis
Post #1206218
« Prev Topic
|
Next Topic »
24 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
