What is the proper syntax for a sql table variable with more than one index on it?
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The other thing I'd add is that since you are indexing it, it sounds like this table variable will have a lot of rows? If so, a temp table is always more efficient since SQL Server ends up spilling the table variable to work tables anyway.
Huh? SQL handles the physical storage of either a temp table or a table variable identically.
The main difference for performance is that stats are created on a temp table, but not on table variables. While temp stats can cause some recompiles, it will also often generate a much better query plan than not having stats at all.
The main difference for performance is that stats are created on a temp table, but not on table variables. While temp stats can cause some recompiles, it will also often generate a much better query plan than not having stats at all.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Regarding which is preferred, my experience has been that temp tables almost always perform better. I use table variables only for very small datasets (10 rows or fewer) and in cases where I need rows to never be rolled back. Otherwise, my suggestion is to stick to temp tables.
Agree with Scott on all counts. Table variables = very small data set that you're only going to use once.
ASKER
Thanks everyone for answering. I had been using a temp table with the two indexes on it but the stored procedure it resides in was taking over a minute to complete. Through trial and error, uncovered the temp table as the bottleneck. So, I decided to try the table variable and it improved it vastly. It only takes 1 second with one index, the unique one. I got greedy, thought I could do better. As for the table size, it could go a couple of thousand records.
If not, create a clustered index on the main way you search, and, if necessary, non-clus indexes on the other ways.
You'll need to use a temp table rather than a table variable. Indeed, if you plan to search a work table multiple times, you should always use a temp table rather than a variable table.