SQL SERVER NON-Clustered Index on table variable?

Well, it's probably too late for Priyanka, but someone else may find this useful.

There IS a way to create a non unique index on a temp table, using a small trick: add an identity column and make it the last field of your primary key.

DECLARE @MyTable TABLE (IXField1 int, IXFiled2 int, Field3 bit, HelperIX int IDENTITY (1,1), PRIMARY KEY/UNIQUE (IXField1, IXField2, HelperIX)

The only indexes you can apply to table variables are the implicit indexes which are behind PRIMARY KEY or UNIQUE constraints. If what you're wanting to index isn't unique, there's no way to index it in a table variable.

If it is unique, you have to specify the constraint when you declare the variable:

declare @t table (
    ID int not null,
    Val1 varchar(10) not null,
    PRIMARY KEY NONCLUSTERED (ID),
    UNIQUE CLUSTERED (Val1)
)
insert into @t (ID,Val1)
select 1,'abc'

select * from @t

You can't freely create indexes on a Table Variable like that, however you can create a PRIMARY KEY like this (note you can also make it a NONCLUSTERED PK):

DECLARE @risk TABLE (rskid int PRIMARY KEY)

Of course, assumes you will be inserting unique values.

Alternatively, you would need to use a temp table.