Table Variables in Sql Server
Like the temp table in SQL Server, a table variable is a sort of local variable used to store data temporarily. Table variables are kept in the 'temp db' database.
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',',');
What's Your Reaction?