Applications

Using Primary Keys and Indexes on Table Variables

I am starting to appreciate the absolute awesomeness of table variables. Rather than using those dreaded cursors, I use a temporary working table and use update statements to apply my calculations. The performance benefits are incredibly tangible.

Table variables are very powerful and can do just about anything a temporary table can do. The only major difference is that once a table variable is declared, it cannot be altered. In addition, all indexes must use unique constraints.

Declaring a Table Variable with a Primary Key

DECLARE @work TABLE
(
consultant_id int primary key,
consultant_name varchar(50),
month_date datetime,
project_id int,
project_name varchar(255),
total_hours float
)

Declaring a Table Variable with a Composite Primary Key

DECLARE @work TABLE
(
consultant_id int,
consultant_name varchar(50),
month_date datetime,
project_id int,
project_name varchar(255),
total_hours float
primary key (month_date, consultant_id, project_id)
)

Declaring a Table Variable with a Composite Unique Index

DECLARE @work TABLE
(
id int identity primary key,
consultant_id int,
consultant_name varchar(50),
month_date datetime,
project_id int,
project_name varchar(255),
total_hours float
unique clustered (id, consultant_name)
)

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.