Table variables and SQL Server

In a recent post, I mentioned temporary tables (you can find that post here). An alternative to temporary tables are table variables. Tablevariables are a lot like temporary tables, except the table variable has a few advantages over temporary tables:

• Table variables usually use fewer resources than a temporary table, which makes it a little bit faster
• Table variables will not exist after the procedure that called it exits, so there is no clean-up required with DROP statements like temporary tables

We declare table variables sort of like other variables, but the syntax looks like this:

DECLARE @<VariableName> TABLE

(

<COLUMNS>

);

Here is an example, using the Library database I created in an earlier post:

1. USE Library
3. DECLARE @SomeAuthors TABLE
4. (
5.     authorID INT
6.     , first_Name VARCHAR(50)
7. );

Here is a way we could use table variables:

Download Code

1. USE Library
3. DECLARE @SomeAuthors TABLE
4. (
5.     author_ID INT
6.     , first_Name VARCHAR(50)
7. );
9. INSERT INTO @SomeAuthors SELECT author_ID, first_Name
10. FROM Authors WHERE first_Name = ' Geoffrey';
12. UPDATE Authors SET Authors.last_Name = 'We updated this column'
13. WHERE Authors.first_Name IN (SELECT first_Name FROM @SomeAuthors);
15. SELECT * FROM Authors;

What this is doing is created a table variable called "SomeAuthors" and it is going to take the records from the Authors table in our Library database and put them into our SomeAuthors temporary variable. Then this little script is going to update all our records in the Authors table based on what is in the SomeAuthors variable. This can be expanded to do some rather cool things when we need to update records. The final line is just there to return the records of the Authors table to see what our script has done.