[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

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?
0
MariaHalt
Asked:
MariaHalt
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
You cannot explicitly create  indexes on table variable, you need to create temp table for this.
If you just need to create a clustered index,  declare it as primary key; but you cant create any other index on a table variable
0
 
Scott PletcherSenior DBACommented:
Are you really searching the table by all three columns?

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.
0
 
Randy Knight, MCMCommented:
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott PletcherSenior DBACommented:
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.
0
 
Randy Knight, MCMCommented:
This is an old article but still applicable.

http://support.microsoft.com/kb/305977/en-us
0
 
Scott PletcherSenior DBACommented:
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.
0
 
Randy Knight, MCMCommented:
Agree with Scott on all counts.  Table variables = very small data set that you're only going to use once.
0
 
MariaHaltAuthor Commented:
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now