Temp Tables

  • Comments posted to this topic are about the item Temp Tables

  • Should it be dependent on the version of SQL Server targeted on?

    In fact, the article referenced for this question was only focused on SQL 2000.

    In your reference article, it clearly presented the sample code,

    Listing 2. Using a table variable.

    declare @t table

    (OrderID int primary key,

    RequiredDate datetime not null,

    ShippedDate datetime null)

    insert @t

    select o1.OrderID, o1.RequiredDate, o1.ShippedDate

    from Orders o1

    where

    o1.EmployeeID = 9

    select o1.OrderID, (select count (*) from @t o2

    where

    (o2.RequiredDate < o1.RequiredDate

    or (o2.RequiredDate = o1.RequiredDate

    and o2.OrderID <= o1.OrderID)))

    as SequenceNo, o1.RequiredDate, o1.ShippedDate

    from @t o1

    order by o1.RequiredDate

    in which a primary key was explicitly created.

    This is contradicted to the later version SQL Server on Limitations and Restrictions for table variable. Reference: http://msdn.microsoft.com/en-us/library/ms175010%28v=sql.105%29.aspx

  • You cannot define indexes at object creation. And while a typical table will create an index for a primary key, I suspect that this does not happen for table variables. And in at least SQL 2008, indexes are explicitly NOT supported for table variables (from the link on the post above):

    Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases, performance may improve by using temporary tables instead, which support indexes and statistics. For more information about temporary tables, see CREATE TABLE (Transact-SQL).

    Given this wording, I think that the question is not at all clear what it is intending. The only possible indexes might be a byproduct of primary key creation, and otherwise indexes are NOT supported.

  • I answer: TRUE / FALSE

    because i remember that:

    Use table variable if we have less than 100 rows Otherwise use a temporary table.

    If we create indexes we must use a temporary table.

  • DECLARE @tmp_Table table (StudID int Primary key,StudName VARCHAR(2000),Class VARCHAR(50))

    CREATE INDEX Idx1 ON @tmp_table(StudID)

    CREATE table #tmp_Table (StudID int Primary key,StudName VARCHAR(2000),Class VARCHAR(50))

    CREATE INDEX Idx1 ON #tmp_table(Class)

    I execute above code in my Sql2008

    but in case of table variable it shows Error ,

    But we can set Indexes on Table variables at the time of table Definition

    DECLARE @tmp_Table table (StudID int Primary key,StudName VARCHAR(2000),Class VARCHAR(50),

    UNIQUE (Class) )

    [font="Verdana"] There is no Wrong time to do a Right thing ๐Ÿ™‚ [/font]

  • jeff.mason (6/23/2012)


    The only possible indexes might be a byproduct of primary key creation, and otherwise indexes are NOT supported.

    This is incorrect, because unique indexes are supported too.

    declare @Tmp table

    ( id int identity primary key,

    idx_column_1 varchar(100) unique,

    idx_column_2 datetime,

    unique (idx_column_2, id) -- a kind of non-unique index on idx_column_2

    -- id serves as a uniqueifier

    );

  • BOL: Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases, performance may improve by using temporary tables instead, which support indexes and statistics.

    So, the right answer is True, False (Option 1).

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


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Poorly worded question. There is no way for the reader to identify if the author meant implicit or explicit index creation. And the fact that both Primary Keys AND Indexes were in question, it was more likely that explicit indexes were meant.

    Best Regards,

    Chris Bรผttner

  • Haining (6/23/2012)


    Should it be dependent on the version of SQL Server targeted on?

    (...)

    This is contradicted to the later version SQL Server on Limitations and Restrictions for table variable. Reference: http://msdn.microsoft.com/en-us/library/ms175010%28v=sql.105%29.aspx

    No, this has been the case since table variables were introduced (in SQL 2000), and never changed since. The information at the link you post (which applies to SQL Server 2008R2) explicitly mentions PRIMARY KEY and UNIQUE as types of constraints that can be declared for a table variable. It does not explicitly say that an index will be implicitly created for those constraints, but that is explained at other sources. (I don't have time to hunt down a good reference now; sorry).

    jeff.mason (6/23/2012)


    You cannot define indexes at object creation. And while a typical table will create an index for a primary key, I suspect that this does not happen for table variables.

    Your suspicion is wrong. A supporting index will always be created for primary key and unique constraints, even in table variables.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • As the question is worded, I would expect explicit indices to be creatable for the "correct" answer to be correct. Otherwise, why not just say "create indices" as the primary key denotes an implicit index in itself. (AFAIK)

    For the question to be valid, I would expect an index to be creatable without creating a constraint.

  • Hugo Kornelis (6/25/2012)


    When I answered the question, I *knew* I was taking a gamble on the intended interpretation.

    ...... so I was looking for an answer option "true for temp tables; depends for table variables".

    So was I - thanks Hugo

    The impossible can be done at once, miracles may take a little longer ๐Ÿ™‚

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

    ...

    Yes, exactly. In this case however I chose the more liberal interpretation that you can have indexes defined (even if they are not the ones you want) so the answer should be true - but it was a close call.

  • There are no ways to DEFINE indexes on table variables. Indexes may or may not be created by the implementation when you define constraints, but that is an implementation detail.


    Just because you're right doesn't mean everybody else is wrong.

  • Got it wrong for all the reasons mentioned above.

    Better luck tomorrow ๐Ÿ™‚

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 75 total)

You must be logged in to reply to this topic. Login to reply