Table Variables in SQL server 2000

Using table variables instead is always faster than using temp tables in SQL server 2000. Below is a sample create table command to create a table variable.

declare @t table
(OrderID int ,
RequiredDate datetime,
ShippedDate datetime)

If you are using a table variable inside a query i.e. if you are writing a query and doing a join on a table variable its always better to have primary keys and constraints on table variable. Below is a sample how you can add primary keys and constraints to a table variable.

declare @t table
(OrderID int primary key,
RequiredDate datetime not null,
ShippedDate datetime null,
unique (RequiredDate, OrderID))

Thats it for today. In the next post we will see how we can return table datatype from a UDF (user defined funcation) in SQL server 2000.