|
Home > Archive > microsoft.public.sqlserver.server > August 2002 > indexing table data type?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
indexing table data type?
|
|
|
| Below is a valid small piece of code from a large proc that I run in place
of a regular temp table:
declare @buys table
(trs_id decimal(18,0), trs_ai_acct_key char(6),trs_shares decimal(15,4),
trs_cusip_no char(9),
trs_unit_price decimal(15,7), trs_date_created datetime,
trs_transaction_code char(1))
insert into @buys
select
trs_id , trs_ai_acct_key ,trs_shares, trs_cusip_no,trs_unit_price ,
trs_date_created, trs_transaction_code
from trs_50
where trs_cusip_no = @cusip and trs_ai_acct_key = @acct
and trs_transaction_code = 'B'
Far faster than a regular temp table for this situation. I wanted to be
greedy and index it as well:
create index myindex
on @buys(trs_id)
It barfed. I dont beleive this can work but thought I would ask JIC to see
if it can be done.
Thanks in advance.
| |
| Anith Sen 2002-08-06, 5:23 pm |
| Non-clustered indexes cannot be created on table variables,
other than the system indexes that are created for a PRIMARY
or UNIQUE constraint. That can influence the query performance
when compared to a temporary table with non-clustered indexes.
--
- Anith
|
|
|
|
|