Table Variables in Sql Server

Analogous to a temporary table in SQL Server. The table variable encompasses all properties of the local variable; however, local variables possess certain limitations that are not present in temporary or regular tables.

Scope: The extent of table variablesLike local variables, table variables are out of scope with the conclusion of the batch. When a table variable is defined within a stored procedure or user-defined function, it ceases to exist upon the termination of that process or function.

 

Declare: The name of the table variables must start with the @ symbol.

DECLARE @Company TABLE (

CompanyName nvarchar(MAX),

IsActive bit);

 

Add Data: Rows can be added to the table variables via the INSERT statement:

INSERT INTO @Company
SELECT ComanyName,IsActive
FROM Company 
WHERE IsActive = 1

 

Retrieving data from the table variablesData from the table variables can be queried using the SELECT statement:

SELECT * 
FROM @Company;

 

Using table variables in user-defined functions

CREATE OR ALTER FUNCTION udfDoSplit(
@string VARCHAR(MAX),
@delimiter VARCHAR(50) = ' ')
-- define table variable and return back a table variable
RETURNS @parts TABLE (
idx INT IDENTITY PRIMARY KEY,
val VARCHAR(MAX)
)
AS
BEGIN
DECLARE @index INT = -1;
WHILE (LEN(@string) 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @string) ;
IF (@index = 0) AND (LEN(@string) 0)
BEGIN
-- insert into table variable
INSERT INTO @parts
VALUES (@string);
BREAK
END

IF (@index 1)
BEGIN
-- insert into table variable
INSERT INTO @parts
VALUES (LEFT(@string, @index - 1));
SET @string = RIGHT(@string, (LEN(@string) - @index));
END
ELSE
SET @string = RIGHT(@string, (LEN(@string) - @index));
END
RETURN
END

GO

SELECT * FROM udfDoSplit('first,second,third',',');

The table variable is a specific category of local variable designed for the temporary storage of data

Limitations on variables in tables 

• While declaring the table variable, you must provide its structure. Once declared, table variables cannot be restructured, unlike regular or temporary tables.
• Regrettably, statistics are not included in table variables. So, if your table just has a few rows, you can utilise table variables to store them.
• Table variables are not compatible with other data types that allow for  input/output. 
• Nevertheless, user-defined functions do have the ability to return table variables.
• Table variables do not support non-clustered index creation. The new In-Memory OLTP feature in SQL Server 2014 makes it possible to use non-clustered indexes 
• In the table variable declaration, allowing you to access memory-optimized table variables.
• You must alias the table before you can execute the query that uses a table variable with a join.

 

Table variable performance

• Table variables in a stored Procesdure cause less recompilation than transient tables.
• A table variable uses less resources than a temporary table with less locking and logging overhead.
• Like the temporary table, the table variables do exist in the temp db database, not in the memory.
• When Should Table Variable Be Used? If you have less than 1000 rows, use Table variable; otherwise, use Temporary tables.

A Table variable will store in the physical memory for some of the data, then later when the size increases it will be moved to the temp db.