Archive for June, 2013

Adding Indexes to Table Variables

June 20, 2013

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