Get Start With MS SQL Table Variable

In SQL, a table variable is a type of variable that can hold a set of rows (like a temporary table) and can be used to store intermediate results or pass data between procedures. Table variables are often used within stored procedures and functions.

Here’s a basic overview of how to use table variables in SQL:

Getting Started

A table variable contains a collection of data or records. It can be used in batches, stored procedures, and user-defined functions. There is no need to drop the table variable after use.

SQL Server Declare Table Variable

A table variable is declared using the DECLARE keyword, followed by the table's structure (columns, types, etc.).As like normal table and temp table, constraints(primary key, check etc.) and identity column can be used with table variable. Declaration syntax of table variable is very similar to a CREATE TABLE statement.

 DECLARE @Student TABLE  
 (  
    ID int,   
    Name NVARCHAR(100)  
 )  

Inserting Data into a Table Variable

Data into a table variable just like a regular table, using INSERT INTO:

 INSERT INTO @MyTableVar (ID, Name, CreatedDate)  
 VALUES (1, 'Ram Shankar', GETDATE()),  
     (2, 'Laxman Prasad', GETDATE());  

Querying Data from a Table Variable

You can query a table variable using a SELECT statement, just like querying a regular table:

 SELECT * FROM @MyTableVar;  

Modifying Data in a Table Variable

You can also perform UPDATE and DELETE operations on a table variable:

 -- Update a row  
 UPDATE @MyTableVar  
 SET Name = 'John A. Doe'  
 WHERE ID = 1;  
 -- Delete a row  
 DELETE FROM @MyTableVar  
 WHERE ID = 2;  

Using Table Variables in Stored Procedures

 CREATE PROCEDURE GetEmployeeDetails  
 AS  
 BEGIN  
   DECLARE @EmployeeDetails TABLE (  
     EmployeeID INT,  
     EmployeeName VARCHAR(100),  
     Salary DECIMAL(10, 2)  
   );  
   INSERT INTO @EmployeeDetails  
   SELECT EmployeeID, EmployeeName, Salary  
   FROM Employees;  
   SELECT * FROM @EmployeeDetails;  
 END;  

The variable will no longer exist after the procedure exits hence there is no need to drop table variable.

Advantages of Table Variables:

• Scope: Table variables are only visible within the batch, function, or stored procedure where they are declared.
• Performance: For small sets of data, table variables can sometimes offer better performance compared to temporary tables, especially since they are stored in memory.
• No Locking: Table variables don't hold locks, which might reduce some contention issues in highly concurrent systems.

Differences Between Table Variables and Temporary Tables

• Scope: Table variables are only available within the current scope (e.g., stored procedure or batch), whereas temporary tables (#temp) are session-specific.
• Transaction Support: Table variables do not support transactions in the same way temporary tables do.
• Indexes: Table variables do not support indexes (unless they are explicitly created, such as primary keys or unique constraints) as opposed to temporary tables which support clustered and non-clustered indexes.

This should give you a solid understanding of how to use table variables in SQL!, I hope you have enjoyed it.

Thanks