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

Advertisements