Table variables, like temp tables, are used to temporarily store data. This functions as a variable and is only present for a specific batch of query execution. When it comes out of the batch, it is dropped.
The scope of table variables is confined to the current batch and Stored Procedure, whereas the scope of a Temp Table is much broader.
You can build a temp table with SELECT INTO, which is faster to write and may let you deal with changing datatypes over time because you don’t need to describe your temp table structure ahead of time. Table variables must be defined in the same way that regular tables are. The data types you define must correspond to the values you insert.
Temporary tables can use CREATE INDEX, but table variables cannot. Table tables are a fantastic solution if you have a huge amount of data that will be faster accessed by index. Using Table Variables within user-defined functions expands the use of those functions. If you’re writing a function, use table variables over temp tables unless you have a strong reason not to.
Both temporary tables and table variables are stored in tempDB.
As previously indicated, table variables are created in the same manner as conventional tables. The only difference is that the table name begins with “@.” Look at the below example.
CREATE TABLE @EmpDataset (
EmpNo INT,
EName VARCHAR(50),
Job VARCHAR(40),
DeptNo INT);
INSERT INTO @EmpDataset
SELECT EmpNo, EName, Job, DeptNo FROM Emp WHERE DeptNo = 10 AND Sal > 3000
Once the batch execution completed, the table variable will be deleted automatically.
Hope you find this article helpful.
Happy learning!!
Big Data and SQL 
One comment