SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table variable - 2


Table variable - 2

Author
Message
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7773 Visits: 25280
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
Britt Cluff
Britt Cluff
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1799 Visits: 253
Thanks for the question.

http://brittcluff.blogspot.com/
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15542 Visits: 11354
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7773 Visits: 25280
Ninja's_RGR'us (11/15/2011)
I love the new question style Ron! Nice way to do this Cool.


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
Bob Razumich
Bob Razumich
Mr or Mrs. 500
Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)

Group: General Forum Members
Points: 575 Visits: 1181
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.
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28735 Visits: 9671
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 :-).
OzYbOi d(-_-)b
OzYbOi d(-_-)b
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1346 Visits: 778
Great question - cheers
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14202 Visits: 12197
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

Revenant
Revenant
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7288 Visits: 4857
Very neat -- thank you!
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10829 Visits: 11967
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search