sql server 2000 TSQL: creating index on table variable

Is the following possible? I am unable to do so. Do I have to have a permanent table to create index?

declare @Beatles table
    (开发者_如何学JAVA
        LastName varchar(20) ,      
        FirstName varchar(20) 
    )

CREATE CLUSTERED INDEX Index_Name_Clstd ON @Beatles(LastName)

---

Not on a table variable, but on a temp table see this http://www.sqlteam.com/article/optimizing-performance-indexes-on-temp-tables

---

No, you cannot create indices on a table variable - see this article here and this posting here comparing local, global temporary tables to table variables.

Restrictions

You cannot create a non-clustered index on a table variable, unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table (SQL Server enforces any UNIQUE or PRIMARY KEY constraints using an index).

---

According to this post - YES you can. The following declaration will generate 2 indexes:

DECLARE @Users TABLE
(
    UserID  INT PRIMARY KEY,
    UserName VARCHAR(50),
    FirstName VARCHAR(50),
    UNIQUE (UserName,UserID)
)

The first index will be clustered, and will include the primary key. The second index will be non clustered and will include the the columns listed in the unique constraint. Here is another post, showing how to force the query optimizer to use the indexes generated dynamically, because it will tend to ignore them (the indexes will be generated after the execution plan is evaluated)