Hello Folks,
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.
Table Variables:
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!!
Regards
Piyush Bajaj
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
little addition
table variable is out of transaction scope.
Example:
DECLARE @Student TABLE(
S_ID INT PRIMARY KEY,
StudName VARCHAR(50),
StudBranch VARCHAR(50))
CREATE TABLE #Student
(
S_ID INT PRIMARY KEY,
StudName VARCHAR(50),
StudBranch VARCHAR(50))
Begin transaction
INSERT INTO @Student VALUES ( 1, ‘Piyush’,’Computer-Science’);
INSERT INTO #Student VALUES ( 1, ‘Piyush’,’Computer-Science’);
RollBack transaction
SELECT * FROM #Student
SELECT * FROM @Student
Hi Piyush,
If we want to keep millions of record, that I think temporary table could be the good option as we create indexes on any of the column of temp tables however tables variables can have only primary key.
Correct me if I am wrong here.
-Abhishek
Yes DM ….you are right, thanx for sharing it. 🙂
Yes Abhishekh, you are very much right, for large data set we tend to use Temporary Table. It is the best option… 🙂
No statistics is collected for table variables therefore estimated number of records selected from variable is always 1, so using them can lead to sub-optimal query plan and drastically decrease performance. To avoid it you have to use RECOMPILE OPTION which annihilates one of the benefits of table variables.
Alexander’s comment is very important. The QO’s rowcount estimate of 1 quickly leads to bad plans if the table variable has more than a few rows. You may not see this until the code is deployed to production, unless you test with realistic data. The fixed 1 row estimate can throw off the QO’s costing pretty dramatically, leading to poorly-performing plans that seem to show up and then disappear at random. For this reason I tend to default to temp tables unless I anticipate that the proc will be called frequently. The rule of thumb I use is that if the proc will not be called more frequently than a few dozen executions per second, I default to temp tables. If the proc will be executed more frequently than that, I use table variables, but only in places where I can be confident that I will not be storing more than a few dozen rows in them.
Re: the collation issue, you can avoid hardcoding a particular collation by using “collate database_default”:
Create table #table1 (column1 varchar(30) collate database_default)
Hi,
can anybody explain me sql server architecture with (component and diagram)?
Nice one