Table variables in SQL Server

While fetching data from SQL Server, I encountered a situation where a set of records had to be stored locally. The sole intention was to avoid multiple execution of a complex query to filter out these records.  

Upon 'googling' for a solution, got to know that it is possible to declare variables called 'Table Variables' in SQL Server (since MS SQL Server 2000) to hold user defined records at server side.

They are created like tables, the only difference being these are local in scope and will be cleaned up by the SQL Server after procedure execution. Due to the same, it requires lesser resources to be used.

Example

 DECLARE @TableVariable TABLE

( Roll int PRIMARY KEY, 

  Name varchar(40),

  Age int CHECK (AGE<65)

)

One point to be noted here is that we can even set constraints on the attributes, just like during table creation. Records can be added using the 'INSERT INTO' clause as in the case of tables. 

This certainly helped me in achieving my objective. Hope it helps you too.

Further reading

http://odetocode.com/Articles/365.aspx