June 23, 2012 at 11:32 am
Comments posted to this topic are about the item Temp Tables
June 23, 2012 at 11:47 am
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
June 23, 2012 at 8:04 pm
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.
June 24, 2012 at 9:16 pm
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.
June 24, 2012 at 10:24 pm
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]
June 24, 2012 at 11:29 pm
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
);
June 25, 2012 at 12:39 am
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).
June 25, 2012 at 12:55 am
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".
June 25, 2012 at 1:02 am
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
June 25, 2012 at 1:05 am
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.
June 25, 2012 at 1:32 am
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.
June 25, 2012 at 1:38 am
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 🙂
June 25, 2012 at 2:35 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.
...
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.
June 25, 2012 at 2:41 am
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.
June 25, 2012 at 2:53 am
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 74 total)
You must be logged in to reply to this topic. Login to reply