You would have seen my blog-post which was about the Temporary Tables. If you want to follow to that post, please click the link:
This blog-post is all about Table Variables and the difference between the Table Variables and Temporary Tables in SQL Server.
With the arrival of SQL Server 2000, Microsoft has introduced table variables as an alternate to use temporary tables.
- Table variables store a set of records; therefore it will be declared as variables rather than created with SQL DDL statements. For e.g.,
Here, we are creating a table variable ‘@Student’, as shown below:
DECLARE @Student TABLE( S_ID INT PRIMARY KEY, StudName VARCHAR(50), StudBranch VARCHAR(50))
Now, if we want to insert some values, then:
INSERT INTO @Student VALUES ( 1, 'Piyush','Computer-Science');
If you want to see the records in the table, then:
SELECT * FROM @Student
- The one and only way that we can pass a table variable to another stored procedure as an input is in table-valued parameters, and then they are read-only in the called routine.
- A table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable as we create with a DECLARE statement.
Differences between Table Variables and Temporary Tables:
- A table variable will generally use fewer resources than a temporary table. Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead.
- Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure. Table variables can often avoid this recompilation hit.
- The table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in EXEC statements. Also, they cannot be used in an insert/exec statement.
- Table variables are a superior alternative to using temporary tables in many situations. The ability to use a table variable as the return value of a UDF is one of the best uses of table variables.
- Table variables can offer performance benefits and flexibility when compared to temporary tables.
Well, this was all about the Table Variables and the difference with the Temporary Tables.
And also comments on this!!