Is the following possible? I am unable to do so. Do I have to have a permanent table to create index?
declare @Beatles table
(
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)
