Table variables are alternative of temporary tables. As,for instance, temporary tables are table variables store a set of record. The syntax of table variable shown below:
We can insert values using INSERT INTO clause in table variable. We can use SELECT to fetch data and UPDATE, DELETE for manipulate data in table variable. In table variable the constraint types allowed are PRIMARY KEY, UNIQUE, NULL, and CHECK but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY constraints are not allowed. Table variable Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement. We can return a table variable from user-defined function.
|
It is just like temporary table (#EmpForm) but some differences are there.
A table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable we create with a DECLARE statement. Temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed.
Table variable data will not be rolled back when a transaction rolled back. Temporary table data will be rolled back when a transaction rolled back.
The variable will no longer exist after the procedure exits, there will be no table to clean up with a DROP statement. Temporary tables are automatically dropped when they go out of scope unless explicitly dropped using DROP TABLE.
The restricted scope of a table variable gives SQL Server some liberty to perform optimizations.
A table variable will generally use fewer resources than a temporary table.
Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure.
We cannot use a table variable as an input or an output parameter.
We cannot truncate a table variables whereas we can truncate a temporary table.
We can not drop table variable as it automatically drop as it goes out of scope. We can not run separate command first create and insert and update it should execute in single shot. Whereas Temporary table we can do separate we can see it in temp db also.
|
Temp Table and Table Variable — both are created in TempDB and not in memory.
SELECT name,type_desc ,create_date FROM tempdb.sys.objects
WHERE type = 'U'
order by create_date desc
Both present in the temp db
drop table #TTEmployee
Temporary table should be explicitly drooped. but not table variable.
For more
No comments:
Post a Comment