SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temp Tables


Temp Tables

Author
Message
imarran
imarran
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 116
Comments posted to this topic are about the item Temp Tables
Haining
Haining
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: 1724 Visits: 210
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
jeff.mason
jeff.mason
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1809 Visits: 2137
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.
nghiavt
nghiavt
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 80
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.
Samith C
Samith C
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 163
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) )

There is no Wrong time to do a Right thing Smile
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3898 Visits: 4408
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
);


palotaiarpad
palotaiarpad
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2215 Visits: 783
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).
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13185 Visits: 12179
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Christian Buettner-167247
Christian Buettner-167247
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4089 Visits: 3889
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
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13185 Visits: 12179
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 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