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))

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s


%d bloggers like this: