SQL Server TABLE Variable
When you need a temporary table in SQL Server 2000 (upwards) many people will implement a standard temporary table i.e.
SELECT
ProductID,
ProductName,
UnitPrice
INTO #tmpTable
FROM
Products
WHERE
UnitsInStock < 40
SELECT * FROM #tmpTable
DROP TABLE #tmpTable
There is nothing wrong with this but there is generally a better way, using the T-SQL TABLE variable. There are several benefits to using this and they include:-
- They only exist for the scope of the Stored Procedure and therefore do not explicity have to be dropped.
- They reduce the locking and logging resource/time when used as part of a Transaction as they are not a physical table that will be affected by any Rollback
- They reduce the number of re-compilations required for the Stored Procedure.
So the above query can be changed to
DECLARE @tmpTable TABLE(ProductID int, ProductName nvarchar(100), UnitPrice money)
INSERT INTO @tmpTable
SELECT
ProductID,
ProductName,
UnitPrice
FROM
Products
WHERE
UnitsInStock < 40
SELECT * FROM @tmpTable
More details can be found here http://support.microsoft.com/default.aspx?scid=kb;en-us;305977
| Author |
: Greg Duffield |
| Published |
: Friday, 14 July, 2006 |
Greg has too many years experience in developement from VB for DOS all the way through to Windows Workflow and LINQ while covering pretty much every technology in between. A complete MS evangelist he is now Director of the only MS Gold Partner IT services company in Norfolk. Wehere they are producing Web 2 Applications for various sectors, and are currently entering the Haulage industry with their latest product.