Table Variables in SQL Server 

   Part - 11

Click here or below for you tube Answer

--> Table Variable:

They behave exactly as a variable, in the below scenario, we are assigning a string and selecting that. However if you use a batch separator, the its scope will not be there.

declare @Variable VARCHAR(10) = '1,2,3'
SELECT @Variable AS SQL_Variable
GO
SELECT @Variable AS SQL_Variable_2


The Red Squiggly line shows that its not accessible.


Lets check the same example for Table Variable

BEGIN

DECLARE @tblEmployee TABLE (
Empid INT NOT NULL,
EmpName VARCHAR(30) NOT NULL
);
INSERT INTO @tblEmployee VALUES (1, 'John'),(2,'Peter')

SELECT * FROM @tblEmployee
SELECT * FROM @tblEmployee

END


→ Table Variable is accessible with the above scopr

Once we use Batch Separator its not accessible anymore:

We need to use recompile hint as the query optimizer doesn't know if it has rows or not
or the right no.of rows in the table variable



However table variable is accessible after the batch separator as well:

CREATE TABLE #tblEmployee  (
Empid INT NOT NULL,
EmpName VARCHAR(30) NOT NULL
);
INSERT INTO #tblEmployee VALUES (1, 'John'),(2,'Peter')

SELECT * FROM #tblEmployee
SELECT * FROM #tblEmployee
GO

SELECT * FROM #tblEmployee

Below is the proof for table variables reside in tempdb

DECLARE @tblEmployee TABLE (

Empid INT NOT NULL,

EmpName VARCHAR(30) NOT NULL

);

INSERT INTO @tblEmployee VALUES (1, 'John'),(2,'Peter')

SELECT * FROM @tblEmployee

WAITFOR DELAY '00:00:30';  

SELECT * FROM @tblEmployee

 

Limitations of table variables

Summary:



Please subscribe to my YouTube Channel "SQL With RaviMartha"