An overview of the SQL table variable
An overview of the SQL table variable
We do not require dropping the table variable. As mentioned earlier, the scope of the table variable is within the batch. The scope of it lasts at the end of the batch or procedure.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolAn overview of the SQL table variable
October 16, 2019 by Rajendra Gupta This article explores the SQL Table variables and their usage using different examples.Introduction
We use many intermediate tables to perform data manipulation, computation, and data sorting from multiple sources. It is beneficial to use temporary tables instead of a regular table and drop them later. Usually, we define SQL temporary tables using the # symbol: 1234567 CREATE TABLE #temp(DBname VARCHAR(20));INSERT INTO #temp SELECT name FROM sys.sysdatabases;SELECT *FROM #temp;DROP TABLE #temp; A SQL temporary table uses following process. Define Use (Insert, update, delete, select) Drop In this article, we will look at an alternative way of storing temporary data, i.e. SQL table variables. Let’s take an overview of it along with multiple examples.Overview of SQL table variables
The table variable is a special data type that can be used to store temporary data similar to a temporary table. The syntax for the table variable looks similar to defining a new table using the CREATE TABLE statement: 1234 DECLARE @Products TABLE(ProductID INT,ProductName VARCHAR(30)); In the above query, we specified a table variable using the DECLARE and TABLE keyword. The table variable name must start with the @ symbol. We also define table columns, data types, constraint similar to a regular table.Scope of SQL table variable
The table variable scope is within the batch. We can define a table variable inside a stored procedure and function as well. In this case, the table variable scope is within the stored procedure and function. We cannot use it outside the scope of the batch, stored procedure or function.Insert data into a table variable
We can insert data into a table variable similar to a regular table: 123456789 DECLARE @Products TABLE(ProductID INT, ProductName VARCHAR(30));INSERT INTO @ProductsVALUES(1, 'Carpet'); You can retrieve a record from a table variable using the Select statement: We cannot drop the table variable using the DROP Table statement. If you try to drop it, you get incorrect syntax message:We do not require dropping the table variable. As mentioned earlier, the scope of the table variable is within the batch. The scope of it lasts at the end of the batch or procedure.
The storage location of SQL table variable
Let’s think of a few questions: What is the storage location of a table variable? Is it created in the source database in which we execute the script? Most people are confused about the table variable location and think that it is stored in memory. Let’s check it out. We can use sys.tables for listing tables in the tempdb database. Let’s execute the following query and do the following: Check the existing tables in tempdb Declare a table variable Check the tables in tempdb again 1234567891011 SELECT *FROM TEMPDB.sys.tables;GODECLARE @Products TABLE(ProductID INT, ProductName VARCHAR(30));GoSELECT *FROM TEMPDB.sys.tables;GO In the output, we did not get any existing table before and after declaring a table variable: Does it mean that the table variable is not stored in the tempdb database? No, it is not valid. You can note that we use Go statement after each step to finish the batch before starting the subsequent batch. Table variable scope finishes at the end of the batch, and SQL Server automatically drops it. Let’s run the following query. In the modified query, we removed the Go statement after declaring a table variable and checking the existence of it in the tempdb: 12345678910 SELECT *FROM TEMPDB.sys.tables;GODECLARE @Products TABLE(ProductID INT, ProductName VARCHAR(30));SELECT *FROM TEMPDB.sys.tables;GO Now, in the output, it shows the table variable in the tempdb database:Table variable and explicit transaction
We cannot use the table variable in the explicit transaction, it does not return any error message, but it skips the transaction. In the following query, we execute the query in four steps: Declare a table variable Starts a transaction using the BEGIN TRAN statement Insert record into the table variable Rollback transaction using ROLLBACK TRAN Verify whether the record exists in the table variable or not. It should not exist because we performed rollback in step 4 12345678 DECLARE @TableVariableA TABLE(Name VARCHAR(30));BEGIN TRAN;INSERT INTO @TableVariableAVALUES('SQL Server');ROLLBACK TRAN;SELECT *FROM @TableVariableA;GO In the output, we can verify that ROLLBACK TRAN did not perform a rollback of data from the table variable: If we require explicit transactions, we can use the temporary tables. The explicit transaction works on temporary tables.User-defined functions UDF and table variable
We can define and use table variables in the user-defined functions as well. Execute the following query: Define a user-defined function using CREATE FUNCTION Statement Define a table variable and define columns for it Define UDF activity in the BEGIN…END statement Return the variable value 12345678910 CREATE FUNCTION TableVariableWithinAFunction()RETURNS @TableVariable TABLE(Name VARCHAR(50))AS BEGIN INSERT INTO @TableVariable SELECT FirstName FROM [DemoSQL].[dbo].[Employee]; RETURN; END;GO Later, we can use a UDF function to retrieve the records: 1 Select * from TableVariableWithinAFunction() It retrieves the records from UDF: This example showed that we could use table variables in a user-defined function as well. We cannot use temporary tables inside a user-defined function.Indexes and table variable
Table variables are a particular type of data types. We can use these table variables similar to the user table to hold temporary data. Q: Is it possible to add indexes on the table variables? A: No, we cannot define indexes on the table variables. Q: If we cannot define indexes on table variables, do we have any alternatives for it? A: Yes, indeed, we cannot define index in the table variables, but we can define primary and unique key constraints on the table variables: 12345 DECLARE @Customer TABLE(CustomerId INT PRIMARY KEY NONCLUSTERED, CustomerName NVARCHAR(30) UNIQUE CLUSTERED(CustomerName)); We cannot define an explicit clustered and non-clustered index on the table variable. Primary key and unique key constraints automatically create the internal indexes on it. You can use these constraints to unique define rows in an index as well. Can we modify a SQL table variable structure after declaration? A: No, we cannot alter a table variable definition after the declaration. Suppose you define a table variable for holding the Varchar data type of length 50. Later, our requirement changes, and we want to modify it for length 100. We cannot alter a table variable structure. We can define another table variable. We can also modify the definition of an existing table variable.Conclusion
In this article, we explored the SQL table variables and their usage for storing temporary data. We also compared it with the temporary tables. Let’s have a quick summary of what we have learned: Temporary table SQL table variable Syntax We use the following format for defining a temporary table: Define Use Drop 12 CREATE TABLE #Employee (Id INT, Name VARCHAR(50)) We define a temporary table using the hash sign. In Table variable, we use the following format: Define Use We cannot drop a table variable using an explicit drop statement. Storage It is stored in the tempdb system database. The storage for the table variable is also in the tempdb database. Transactions We can use temporary tables in explicit transactions as well. Table variables cannot be used in explicit transactions. User-defined function We cannot use it in the user-defined functions. We can use table variables in the UDF. Indexes We can define explicit indexes on the temporary tables. We cannot define explicit indexes on table variables. We can use primary and unique key constraints. Scope Scope of a temporary table is local and global as defined while creating it. Scope of the table variable is within the batch. We cannot use it outside the batch. Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022