July 2, 2012 at 12:10 pm
My answer: True-False
Give my points back!
July 3, 2012 at 12:28 am
Ric Sierra (7/2/2012)
My answer: True-FalseGive my points back!
1
😀
July 3, 2012 at 9:52 pm
Getting my point back.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 20, 2012 at 2:49 am
As far I know, Primary key, constraints & index - all are valid only for temporary table (#Table) where as only first 2 are valid for table variable (@Table).
July 20, 2012 at 10:41 am
Hugo Kornelis (6/25/2012)
When I answered the question, I *knew* I was taking a gamble on the intended interpretation.What convinced me to pick the "wrong" answer was the wording: "can have primary keys and indexes defined" - I interpreted this as meaning that you could define any index you want, in addition to those implied by primary key and unique constraints.
For table variables, the truth is that the statement is neither true nor false.
"can have primary keys and indexes defined" - true, because you can define indexes by declaring a primary key constraints and unique constraints in the table declaration.
"can have primary keys and indexes defined" - false, because you can define the primary key any way you want (within the standard rules for primary keys), but you can not define indexes any way you want; you cannot defined non-unique indexes, cannot define unique indexes on columns taht you don't want to declare a unique constraint on, cannot include additional columns in a nonclustered index, etc.
Both explanations are valid, so I was looking for an answer option "true for temp tables; depends for table variables".
+1
And I think we've had this same question with the other answer some time in the past. The other interpretation is much more sensible, since "primary keys and indexes" suggsts that indexes created automatically to support constraints are not considered (else why say "and indexes?) so the specifying a UNIQUE constrain shouldn't count as "and index".
I'm rather surprised that Steve hasn't corrected this (and awarded points back).
Tom
July 20, 2012 at 10:45 am
sestell1 (6/25/2012)
I was always under the impression that there was no reason to create indexes on table variables because they exist only in memory.Does anyone know what SQL Server actually does when you define a primary key in a table variable? Does it create an 'index' in memory?
Well, they don't exist only in memory. They to have storage in tempdb. Or did have back in 2000.
Tom
July 20, 2012 at 10:48 am
ronmoses (6/25/2012)
On the contrary, I think that's exactly the kettle of fish the question asks about. "A Table Variable can have primary keys and indexes defined." I don't see the word "you" in there. Can indexes be defined on a table variable? Well, they can exist, and they're certainly not undefined, so the answer must be yes. If you want to clarify, you can say, "yes, by SQL Server when you define a constraint." Can you define them directly? No. But the question didn't ask that. A table variable can have indexes defined, regardless of whether or not you can explicitly define them. Seems like standard QotD material to me.
So even if SQL Server didn't create such indexes the answer would still be yes, because the question is whether it's possible that something could create it, not whether it does? Strikes me as a somewhat pedantic and not altgether useful view!
Tom
July 20, 2012 at 10:59 am
cfradenburg (6/25/2012)
I answered "true, false" because a table variable can only ever have on index. A primary key or (as I learned today) a unique clustered index. It's not possible to create any more so it's not possible for a table variable to have multiple indexes.
I habitually use table variables with multiple indices - several unique key constraints and a primary key constraint. This enables the SQL Server to catch my stupid coding errors that violate teh business constraints that imply those key constraints just as it does for ordinary tables.
Tom
July 20, 2012 at 11:13 am
L' Eomot Inversé (7/20/2012)
sestell1 (6/25/2012)
I was always under the impression that there was no reason to create indexes on table variables because they exist only in memory.Does anyone know what SQL Server actually does when you define a primary key in a table variable? Does it create an 'index' in memory?
Well, they don't exist only in memory. They to have storage in tempdb. Or did have back in 2000.
Yes that is a total myth. Logically it only makes sense they need to use tempdb for table variables. It has to do something with the data when the system runs out of available memory right?
Here is an article that should dispel that myth once and for all.
http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 20, 2012 at 1:16 pm
Re: Myth that @Table variables are only in memory.
It never made sense to me that people would think that MS would develop an open-ended methodology to manage data in table structures, caching in memory and/or writing to disk when appropriate, but then do something different for table variables.
July 20, 2012 at 1:47 pm
john.arnott (7/20/2012)
Re: Myth that @Table variables are only in memory.It never made sense to me that people would think that MS would develop an open-ended methodology to manage data in table structures, caching in memory and/or writing to disk when appropriate, but then do something different for table variables.
Besides which it's physically impossible to limit table vars to RAM only, since the # rows in a table variable are not limited, but RAM is :-).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 31, 2012 at 6:03 am
Hugo Kornelis (6/25/2012)
When I answered the question, I *knew* I was taking a gamble on the intended interpretation.What convinced me to pick the "wrong" answer was the wording: "can have primary keys and indexes defined" - I interpreted this as meaning that you could define any index you want, in addition to those implied by primary key and unique constraints.
For table variables, the truth is that the statement is neither true nor false.
"can have primary keys and indexes defined" - true, because you can define indexes by declaring a primary key constraints and unique constraints in the table declaration.
"can have primary keys and indexes defined" - false, because you can define the primary key any way you want (within the standard rules for primary keys), but you can not define indexes any way you want; you cannot defined non-unique indexes, cannot define unique indexes on columns taht you don't want to declare a unique constraint on, cannot include additional columns in a nonclustered index, etc.
Both explanations are valid, so I was looking for an answer option "true for temp tables; depends for table variables".
😀 Everything depends 😀
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
August 8, 2012 at 12:15 pm
Got it correct!
October 5, 2012 at 7:35 am
+1 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 5, 2012 at 7:56 am
Thanks a lot Wayne for the valuable information.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 15 posts - 61 through 74 (of 74 total)
You must be logged in to reply to this topic. Login to reply