index on table variable

Hi guys,
Can we put Index on table type variable like if we create a
table type variable by this:
DECLARE @.Results TABLE (
[rownum] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL,
[PlanID] BIGINT,
PlanPriceID BIGINT,
PricePerMonth MONEY,
ConnectionFee MONEY,
ConnectionFeeWModem MONEY,
Priority INT
)
after creating this I put index on @.Results
Like...
CREATE INDEX plan_ind
ON @.Results (PlanID)
It is giving Error, can't we create index on that
ManishHi
You can not create an index on a table variable, in general if you are using
a table variable to warrant an index, it may be better to use a temporary
table instead.
John
"Manish Sukhija" wrote:

> Hi guys,
> Can we put Index on table type variable like if we create a
> table type variable by this:
> DECLARE @.Results TABLE (
> [rownum] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL,
> [PlanID] BIGINT,
> PlanPriceID BIGINT,
> PricePerMonth MONEY,
> ConnectionFee MONEY,
> ConnectionFeeWModem MONEY,
> Priority INT
> )
> after creating this I put index on @.Results
> Like...
> CREATE INDEX plan_ind
> ON @.Results (PlanID)
> It is giving Error, can't we create index on that
> Manish
>|||Hi John,
If i create temp table instead of table type type variable, will
it make any difference on code, i mean we have to drop temp table after
completion of work but we are need not to do this work in table variable.
bye...
"John Bell" wrote:
> Hi
> You can not create an index on a table variable, in general if you are usi
ng
> a table variable to warrant an index, it may be better to use a temporary
> table instead.
> John
> "Manish Sukhija" wrote:
>|||Well,
There is no other difference in terms of code other than you have to drop
the temp table. But, outside the deveopers point of view. Teble variables ar
e
in memory tables but temp tables are stored in temp db. But again table
variables can be stored in temp db if there is no sufficient memory. you can
use local temp tables in peace if this particular proc where you are using
doesn't have high concurrency.
But, just a question, why do you need a primary key on rownum?
and on plan id. If you can give a little more insight on what your
requirement is, then may be we can get it done with the table variables only
.|||Hi
Temporary tables will be automatically dropped when they go out of scope,
although you may want to explicitly drop them. See the information on
temporary tables in the Books Online topic "CREATE TABLE"
Make sure that you declare the temporary table at the start of a stored
procedure to help avoid re-compilation.
John
"Manish Sukhija" wrote:
> Hi John,
> If i create temp table instead of table type type variable, wil
l
> it make any difference on code, i mean we have to drop temp table after
> completion of work but we are need not to do this work in table variable.
> bye...
> "John Bell" wrote:
>|||You can put indexes on table variables by using part of the constriant
syntax, see below...
DECLARE @.Results TABLE (
[rownum] [int] IDENTITY (1, 1) NOT NULL,
[PlanID] BIGINT null unique ( planid, rownum ),
PlanPriceID BIGINT,
PricePerMonth MONEY,
ConnectionFee MONEY,
ConnectionFeeWModem MONEY,
Priority INT
)
select *
from @.results
where planid = 1245
The above does an index s because i have put a unique constraint on
planid, rownum.
You can't specify the index in an hint and there aren't any statistics for
the optimiser to use so don't assume it will be used etc... you are probably
better using # tables if you are going to start indexing stuff because of
performance; table variables are really good as a solution to stop plan
recompilation compared to # tables.
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:22D81225-08F7-4483-9442-7839BF26F02B@.microsoft.com...
> Hi guys,
> Can we put Index on table type variable like if we create a
> table type variable by this:
> DECLARE @.Results TABLE (
> [rownum] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL,
> [PlanID] BIGINT,
> PlanPriceID BIGINT,
> PricePerMonth MONEY,
> ConnectionFee MONEY,
> ConnectionFeeWModem MONEY,
> Priority INT
> )
> after creating this I put index on @.Results
> Like...
> CREATE INDEX plan_ind
> ON @.Results (PlanID)
> It is giving Error, can't we create index on that
> Manish
>|||> If i create temp table instead of table type type variable, will
> it make any difference on code,
http://www.aspfaq.com/2475

> i mean we have to drop temp table after
> completion of work but we are need not to do this work in table variable.
Well, no, technically you do not have to drop a table variable, but in any
case, if this is your idea of "extra work"...