SQL Table Variables

Introduction to SQL Table Variables

SQL table variable is a local variable type that temporarily stores data in a set of rows. For instance, if one wants to manipulate intermediate data quickly, one can use temporary tables in SQL. They are similar to temporary tables because they only store data temporarily.

Key Highlights

• SQL table variables allow storing a few rows of data temporarily.
• They allow fast execution of commands and save memory.
• Their only limitation is the limited scope.
• Use the at (@) sign in the DECLARE statement to create the variable.

Working with SQL Table Variables – Syntax

Begin with installing the SQL server on the respective device.

To create a table variable, use the DECLARE statement, followed by the variable name and the data type of the table. The name must be preceded by an at (@) sign.

Syntax for creating:

DECLARE @TABLEVARIABLE TABLE
(column1 dataType,
column2 dataType,
.
.
.
columnN dataType
);

Syntax for inserting values:

Declare a variable and define columns as per our requirements. After creation, insert data in the variable, as shown below.

INSERT INTO @table_variable(column1, column2, ...valueN)
VALUES (value1, value2, … valueN);

Syntax for deleting values:

The syntax to delete values is:

DELETE FROM @table_variable WHERE [condition];

Examples of SQL Table Variables

Given below are the examples mentioned:

Example #1

Mr. Max requires a table variable called “Students” to hold student data temporarily. It has three columns: serial number (srNO) of INT data type, name of the student (Name), and email address of the student (Email) of VARCHAR data type.

Declaration:

DECLARE @Students TABLE
(SrNO INT,
Name VARCHAR(40),
Email VARCHAR(40)
);

Now, to add the student data, use the INSERT method.

INSERT INTO @Students
VALUES
(1, 'Kate', '[email protected]'),
(2, 'John', '[email protected]'),
(3, 'Alex', '[email protected]'),
(4, 'Brad', '[email protected]'),
(5, 'Jenny', '[email protected]')

This query will insert five rows in @Students. Now, use the SELECT statement to view the records.

SELECT * FROM @Students;

Output:

Example #2

A grocery store, FoodMart, needs to keep track of orders received temporarily. Declare the table variable @Orders, which will have three columns: order_ID, order_quantity, and order_amount.

Declaration:

DECLARE @Orders TABLE (
order_ID INT NOT NULL,
order_quantity INT NOT NULL,
order_amount DEC(6,2) NOT NULL
);

Now, add some records using the INSERT function.

INSERT INTO @Orders
VALUES
(1, 2, 50.00),
(2, 4, 20.50),
(3, 1, 5.50),
(4, 3, 35.50),
(5, 1, 75.00);

To get the data, we can run the following code:

SELECT * FROM @Orders;

Output:

One can easily find particular row(s) or records with the following query.

SELECT * FROM @Orders WHERE order_amount=50.00;

Output:

We can also update and delete the records or the rows.

DELETE FROM @Orders WHERE order_ID=1;
SELECT * FROM @Orders;

This particular command will delete the record from @Orders where the order_ID equals 1.

Output:

We can also update the records or the rows using the SET command in SQL.

UPDATE @Orders SET order_quantity=2 WHERE order_ID=2;
SELECT * FROM @Orders;

The above code would update the order quantity where the order id equals 2 in @Orders.

Output:

SQL Table Variables: Benefits and Limitations

Benefits	Limitations
Saves Memory: Intermediate data required for processing exists temporarily, and it gets deleted at the end of the execution of the batch.	Limited Scope: The only drawback is that you can only reference them within its scope. So here, they are scope limited.
Fast Execution: Since table variables exist in the tempdb database rather than memory, their execution is high-speed.	No Updation: Once you have declared them, you can not create, update or delete the columns from it.
Easy Updation: They allow the easy insertion, updation, and deletion of records within user-defined approaches.	No Joins: One cannot join table variables by their names.

Final Thoughts

This article discussed the table variables, their declaration, implementation, advantages and disadvantages, and some practical code examples. Table variables are helpful for cases where data has to be stored only temporarily. Moreover, they allow faster execution of queries on data.

Frequently Asked Questions (FAQs)

Given below are the FAQs mentioned:

Q1. What are table variables in SQL?

Answer: They are specialized local variables that temporarily hold data. They have limited scope and reside in tempdb rather than memory.

Q2. How do you define table variables?

Answer: They are defined using the DECLARE statement in SQL Server.

The syntax is as follows:

DECLARE @exampleData TABLE (
ID INT,
Name VARCHAR(20),
Address VARCHAR(100)
);

This example creates a table variable called @exampleData with three columns: ID, Name, and Address. ID is of type INT, Name is of type VARCHAR with a length of 20, and Address is of type VARCHAR with a length of 100.

Q3. When to use table variables in SQL Server?

Answer: They are used in SQL Server when you need to store a small set of data that you will use within a single stored procedure, function, or batch.

Q4. How do you pass a table variable to a stored procedure?

Answer: You can pass it to a stored procedure by using the EXEC statement and provide the table variable as a parameter.

The syntax would be as follows:

EXEC stored_procedure_name @table_variable_name = table_variable

Q5. Which is better, table variable or temp table?

Answer: Table variables are better when dealing with a smaller amount of data, as not logging in the transaction log makes them faster. However, temp tables are accessible to multiple stored procedures and batches, and they can also be indexed and have constraints, which table variables cannot. So the temp tables are better for larger, complex data.

Recommended Articles

We hope that this EDUCBA information on “SQL Table Variables” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. SQL Examples
2. SQL REGEX
3. SQL Quick References
4. SQL REGEX