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:
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.
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:
AROUT Around the Horn
BSBEV B's Beverages
CONSH Consolidated Holdings
EASTC Eastern Connection
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.