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
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: 15963 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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

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

http://brittcluff.blogspot.com/
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36060 Visits: 11361
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
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: 15963 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
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

Group: General Forum Members
Points: 800 Visits: 1216
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
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68407 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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1710 Visits: 778
Great question - cheers
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26160 Visits: 12500
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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12601 Visits: 5010
Very neat -- thank you!
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18945 Visits: 12426
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