Thursday, 21 February 2013

Multiple Column Primary Keys on Table Variables

I luuurrrve table variables in SQL Server - in fact they are my third favorite thing after otters and custard.

I actually used them for a couple of years without realizing that I could define a primary key, boosting performance:

DECLARE @Cakes table (CakeId int PRIMARY KEY, CakeName varchar(30), HasFrosting bit)

For some of my recent work on Giant Panda Planet I found I needed my table variable to have a multiple column primary key... was it an impossible dream?

Not really:

DECLARE @PandaFriends table (
PandaId int,
FriendId int,
IsBestFriend bit,
PRIMARY KEY(PandaId, FriendId)

Side Pocket!