I want to create a simple Table Variable with 2 fields in it and 2 indexes on it.
The first field requires a UNIQUE CLUSTERED INDEX.
The second field requires a NONCLUSTERED INDEX.
DECLARE @Users TABLE (
[UserId] [bigint] PRIMARY KEY,
[UserTypeId] [int] NOT NULL,
UNIQUE CLUSTERED (UserId))
CREATE NONCLUSTERED INDEX IDX_Users_UserTypeId ON @Users(UserTypeId)
I'm getting this error:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '@Users'.
What is the proper syntax for a sql table variable with more than one index on it?