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.
