Jump Start: Table Variables

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 Horn
BSBEV  B's Beverages
CONSH  Consolidated Holdings
EASTC  Eastern Connection
NORTS  North/South
SEVES  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.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.