Jump Start: Table Variables

Basic T-SQL data types, such as string, date, and int, are great for functions that require a single data value, but T-SQL also supports another type of variable that you can use in your scripts: table variables. SQL Server has supported table variables since the release of SQL Server 2000.

Creating a table variable is a lot like creating a table, as you can see in the sample code below:

DECLARE @MyLondonCustomers TABLE (CustID nchar(5), CustName nvarchar(40))

However, remember that table variables are, at their core, variables, and as such, they are not persistent. When your T-SQL batch ends, the table variable is gone.

You can use table variables just like regular tables. The code below shows how you can populate the @MyLondonCustomers table variable with data from the Northwind customers table, and then query the table variable.

INSERT INTO @MyLondonCustomers (CustID, CustName)  SELECT CustomerID, CompanyName    FROM Customers WHERE City = 'London'SELECT * FROM @MyLondonCustomers

The results of this query will contain just the rows from the @MyLondonCustomers table variable:

CustID CustName------ ----------------------------------------AROUT  Around the HornBSBEV  B's BeveragesCONSH  Consolidated HoldingsEASTC  Eastern ConnectionNORTS  North/SouthSEVES  Seven Seas Imports

Why might you use table variables? They can provide better performance than temporary tables built in a temporary database. Table variables are especially useful when you need to group together related information for more convenient access or when your script needs to access the same information multiple times.