Table Variables in Sql Server

This Article helps you to Understand Table Variables in Sql Server

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

DECLARE @Company TABLE (
    CompanyName nvarchar(MAX),
    IsActive bit
);

The scope of table variables
Similar to local variables, table variables are out of scope at the end of the batch.
If you define a table variable in a stored procedure or user-defined function, the table variable will no longer exist after the stored procedure or user-defined function exits.

Inserting data into the table variables
Once declared, the table variable is empty. You can insert rows into the table variables using the INSERT statement:

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

Querying data from the table variables
You can query data from the table variables using the SELECT statement:

SELECT 
    *
FROM
    @Company;

Note that you need to execute the whole batch or you will get an error

Restrictions on table variables

• You have to define the structure of the table variable during the declaration.
• Unlike a regular or temporary table, you cannot alter the structure of the table variables after they are declared.
• Unfortunately, table variables do not contain statistics. Therefore, you should use table variables to hold a small number of rows.
• You cannot use the table variable as an input or output parameter like other data types. However, you can return a table variable from a user-defined function
• You cannot create non-clustered indexes for table variables. However, memory-optimized table variables are now accessible with SQL Server 2014 thanks to the new In-Memory OLTP feature, which enables you to use non-clustered indexes in the table variable declaration.
• If you are using a table variable with a join, you need to alias the table in order to execute the query.

Performance of table variables

• Using table variables in a stored procedure results in fewer recompilations than using a temporary table.
• A table variable use fewer resources than a temporary table with less locking and logging overhead.
• Similar to the temporary table, the table variables do live in the temp db database, not in the memory.

When To Use Table variable ? 
Use Table variable, if you have less than 1000 rows otherwise prefer Temporary tables.

Location of Table variable:- 
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.

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',',');