Adding Indexes to Table Variables

Problem: Adding Indexes to Table Variables

Solution: When creating a table variable create a unique constraint on the table

eg DECLARE @table TABLE (f1 int, UNIQUE(f1))

This will create a unique non clustered index on field f1, you can create a clustered index by creating a Primary Key on a field but these things can only be done at variable creation time, you can not alter a table variable to add these things.

If your field is not unique then add an identy colyumn to your table and use this in the unique index

eg DECLARE @table TABLE (f1 int,f2 int identity(1,1) , UNIQUE(f1,f2))


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: