Temp Tables

  • My answer: True-False

    Give my points back!

  • Ric Sierra (7/2/2012)


    My answer: True-False

    Give my points back!

    1

    😀

  • Getting my point back.

  • 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).

  • 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

  • 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

  • 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

  • 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

  • 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/

  • 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.

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • 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

  • Got it correct!

  • +1 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • 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