Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Table variable - 2 Expand / Collapse
Author
Message
Posted Tuesday, November 15, 2011 6:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:58 PM
Points: 5,595, Visits: 24,989
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
Posted Tuesday, November 15, 2011 6:04 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Thanks for the question.

http://brittcluff.blogspot.com/
Post #1205887
Posted Tuesday, November 15, 2011 6:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 11,194, Visits: 11,142
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
Posted Tuesday, November 15, 2011 6:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:58 PM
Points: 5,595, Visits: 24,989
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
Posted Tuesday, November 15, 2011 7:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 421, Visits: 980
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
Posted Tuesday, November 15, 2011 7:47 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 4:00 AM
Points: 21,397, Visits: 9,612
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
Posted Tuesday, November 15, 2011 9:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, Visits: 778
Great question - cheers
Post #1206082
Posted Tuesday, November 15, 2011 10:14 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:32 PM
Points: 8,738, Visits: 9,284
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
Post #1206159
Posted Tuesday, November 15, 2011 10:24 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:50 AM
Points: 4,425, Visits: 3,417
Very neat -- thank you!
Post #1206170
Posted Tuesday, November 15, 2011 11:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 5,977, Visits: 8,237
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 »

Add to briefcase ««123»»

Permissions Expand / Collapse