Please see
CREATE INDEX (Transact-SQL)[
^]
You have to declare the table first, and create your index on it in a second time.
Updated after comment:
I checked, and a temp table is declared with the
#
sign, not the
@
sign (which defines a variable).
Thus:
CREATE TABLE #tmptable (Id int IDENTITY(1,1) PRIMARY KEY, ProductId int)
CREATE NONCLUSTERED INDEX ind1 ON #tmptable (Id, ProductId)
The
NONCLUSTERED
keyword is optional, as it is the default.
Source:
Create Index on Temp Table[
^]
Note: you will have to drop the temp table at the end of your script if you do not want it to persist:
DROP TABLE #tmptable
OR
You can declare an index in a table variable, you just omitted a comma:
DECLARE @tmptable TABLE (
Id int identity(1,1)
,ProductId int
,INDEX IX3 NONCLUSTERED(Id,ProductId)
)
Source:
SO: SQL Server : Creating an index on a table variable[
^]