create index on @AA table

[fredong]

Can I create an index on a variable table? I got an error

declare @AA table
(
ID int,
TC varchar(20)
)

create index index1 on @AA (intExportUID)


--Line 149: Incorrect syntax near @AA

 

[AlexCuse]

I don't think you can create indexes on table variables, especially on columns that aren't in the table ;-)

You could try this though:

declare @AA table
(
  ID int primary key clustered,
  TC varchar(20)
)

To achieve an index on the ID field (move it to get index on TC)

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[mrdenny]

Nope. Use a temp table instead.

Table variables are done in memory. Indexes are done to disk. As such you can't create an index on a temp table because after the command has completed the pointers in the index would point to nothing.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com