In SQL Server, table variables are types of local variables that allows us to store and manipulate data just like a regular table without actually writing data into the database.
Table variables are incredibly useful when we need to temporarily store data withing a specific scope such as a stored procedure, function, or a batch of SQL statements.
What Are Table Variables?
LIke we mentioned, table variables in SQL Server are temporary storage structures that allow us to store a set of rows and columns just regular SQL tables.
However, unlike regular tables, table variables are scoped to a given batch, procedure, or function in which they are declared. This makes them useful for storing short-term data without actually writing data to the database.
One major use of table variables is reduced table locking and logging overhead when you compare them to actual temporary tables.
SQL Server Declare Table Variable
In SQL Server, we declare a table variable by using the declare statement followed by the @ symbol, the variable name, and the data type. It is basically like a simplified version of an actual table.
We can break down the definition syntax as shown below:
DECLARE @TableName TABLE
(
Column1 DataType,
Column2 DataType,
-- ...
);
Take a look at the example code below that demonstrates how to use the example syntax above to declare a table variable in SQL Server.
DECLARE @EmployeeTable TABLE
(
EmployeeID INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50)
);
In this case, we declare a temporary table called EmployeeTable with three main columns. The first is the EmployeeID of INT data type.
The others are FirstName and LastName columns that allows us to store character values.
Scope of Table Variables
It is important to keep in mind about the scope of table variables. This is because it is a major feature of how they work.
As mentioned, table variables are local variables, hence, the scope is limited to the batch, stored procedure, or user-defined function in which they are declared. They are not accessible outside of their scope.
Inserting Data into Table Variables
We can insert data into a table variable using the insert into clause just like we normally would in a regular table.
An example syntax is as shown:
INSERT INTO @TableName (Column1, Column2, ...)
VALUES (Value1, Value2, ...);
Using the previous table variable we created, we can add sample data as shown:
INSERT INTO @EmployeeTable (EmployeeID, FirstName, LastName)
VALUES (1, 'Ames', 'Yeh');
INSERT INTO @EmployeeTable (EmployeeID, FirstName, LastName)
VALUES (2, 'Alan', 'Joek');
We can also insert data into a table variable by selecting the data from another table or using a subquery as demonstrated in the example below:
INSERT INTO @TableName (Column1, Column2, ...)
SELECT Column1, Column2, ...
FROM AnotherTable
WHERE Condition;
Querying Table Variables
As you can guess, we can query data from a table variable using standard SQL SELECT statements. An example is as shown below:
SELECT * FROM @EmployeeTable;
Like a regular table, we can apply various filtering, sorting, and aggregation operations.
Restrictions on Table Variables
While table variables are versatile, there are some limitations to keep in mind:
- No Indexing - Table variables do not support indexing, so performance can suffer for large datasets or complex queries.
- No Statistics - SQL Server does not maintain statistics on table variables, which can lead to suboptimal query plans.
- Limited Transactions - Table variables do not support explicit transactions, making it impossible to control their behavior within a transaction explicitly.
Working with User-Defined Functions
We can use table variables within user-defined functions to store and manipulate data. The following example demonstrates how to use a table variable to store the result of a UDF.
CREATE FUNCTION dbo.GetAverageSalary()
RETURNS DECIMAL(18, 2)
AS
BEGIN
DECLARE @SalaryTable TABLE
(
Salary DECIMAL(18, 2)
);
INSERT INTO @SalaryTable (Salary)
SELECT Salary FROM Employee;
DECLARE @AverageSalary DECIMAL(18, 2);
SELECT @AverageSalary = AVG(Salary)
FROM @SalaryTable;
RETURN @AverageSalary;
END;
In the example above, we define a a simple user-defined function that returns the average salary of employees in a table variable.
We can then call this function in the SQL statements to retrieve the average salary.
Conclusion.
In conclusion, table variables in SQL Server are valuable tools for temporary data storage and manipulation within specific scopes. This guide showed how to work with table variables from declaring them to using them in user-defined functions.
