EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial SQL Table Variables
Secondary Sidebar
MongoDB vs Postgres

Oracle Java

Data Analysis Tools

MongoDB vs Cassandra

Data Structure Interview Questions

Career In Devops

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.

SQL Table Variables

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

SQL Table Variables 1

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:

SQL Table Variables 2

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

SELECT * FROM @Orders WHERE order_amount=50.00;

Output:

find particular row(s) or records

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:

delete the record

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:

order quantity

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
Popular Course in this category
JDBC Training (6 Courses, 7+ Projects)
  6 Online Courses |  7 Hands-on Projects |  37+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

PHP Training (5 Courses, 3 Project)4.9
Windows 10 Training (4 Courses, 4+ Projects)4.8
SQL Training Program (10 Courses, 8+ Projects)4.7
PL SQL Training (4 Courses, 2+ Projects)4.7
Oracle Training (17 Courses, 8+ Projects)4.7
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more