" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
|
Need a Dynamic Table inside Stored Procedure?
We have two options:
a.Temporary Tables
b.Table Variable
Which one is better to use? Which one can provide better performance? Lets have a look at both of them by the means of comparison:
One: Transaction Logs are not recorded for Table Variables. This simply means, transaction or rollback statements are not going to work on Table Variables. So, if we don’t need transaction, why make the database handle the burden? We go for Table Variables.
Two: Temporary Tables can not be pre-compiled. Precompilation in SQL Server can save a lot of time and increase performance. SQL Server does a re-compilation for the procedures with Temporary Tables.
|
|
Three: Table variable has a scope just like other variables. They are not visible in inner stored procedures and in EXEC statements.
Try It:
|
--creating a Temporary Table CREATETABLE #TemporaryTable(s varchar(200)) --creating a Table Variable DECLARE @TableVariable TABLE (s varchar(200)) INSERTINTO #TemporaryTable SELECT'old val #' INSERTINTO @TableVariable SELECT'old val @' BEGINTRANSACTION UPDATE #TemporaryTable SET s='new val #' UPDATE @TableVariable SET s='new val @' ROLLBACKTRANSACTION --After ROLLBACK, table variable will show --the new value, --as "Transaction Logs are not recorded for Table Variables" --Check The Results:: select*from #TemporaryTable select*from @TableVariable
Above all, Table Variables are not to replace Temporary Tables. Both have different purpose and we have choice.