Can you add an Index to a table variable?

[Joe]

Hi,
I've got 2 table variables inside of an SQL 2000 function:
@tmpBigList(BItemID, BRank)
@tmpSmallList (ItemID, Rank)

The following UPDATE statement can run for a long time if @TmpTable1
has 500 rows and @TmpTable2 has 35,000 rows.

UDPATE @tmpBigList
SET BRank = t.Rank
FROM @tmpBigList
JOIN @tmpSmallList t on t.ItemID = BItemID

Looking at the Query Plan, you see that the INNER JOIN Of @tmpBigList
to @TmpSmallList results in 500 * 35,000 = 17,500,000 rows being
returned from @TmpSmallList. That takes a long time.
An index would help, but it appears that you can't add an index to a
table variable.

Changing to a temp table does not work since it's in a function.
Thanks,
Joe Landes

[Hugo Kornelis]

On 1 Dec 2004 21:15:00 -0800, Joe wrote:

(snip)

An index would help, but it appears that you can't add an index to a
table variable.

Hi Joe,

That is correct. The only way to create an index on a table variable is to
create a UNIQUE or PRIMARY KEY constraint when declaring the variable.

If neither of your tables allows duplicates in ItemID, the declare would
be like this:

DECLARE @tmpBigList TABLE (BItemID int NOT NULL
,BRank int NOT NULL
,PRIMARY KEY(BITemID)
)
DECLARE @tmpSmallList TABLE (ItemID int NOT NULL
,Rank int NOT NULL
,PRIMARY KEY(ItemID)
)

Note: check the datatypes and nullability agains your requirements.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)