GeekZilla
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
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.
