Most of the time you don’t gain very much performance from obsessing about details but instead from focusing on finding flaws in the general design.. Once in a while it’s still fun to obsess about the details though, and this post is about just that, details.
I find myself using both temporary tables and table variables in MSSQL without any preference for either or, so I decided to dive a little deeper into if there is a difference, and more importantly when I should be using what.
The biggest difference between the two is that temporary tables are actually created on the disk like a regular table. This also in turn means that temporary tables are pretty much like any other table and support all the things you are used to. Table variables are instead stored in memory and suffer from limitations with the usage of constraints, default values on columns, as well as no indexes.
When reading the above, temporary tables seemed like a clear winner in most any situation, but as with all things first impressions can be deceiving. The “truth” is that temporary tables are not the best option when dealing with smaller sets of data as there is a good chance that the sql server will decide to update statistics and force a recompile. So in the case of smaller datasets table variables should actually be faster.
To get some real numbers on what google was telling me I created the simple test case below, and as predicted the table variable was faster when doing a insert and select with 100k rows (small data set).
But as with all things results will probably vary so try the code out for yourself, and let me know if you come to a completly different conclusion.
-- Test settings DECLARE @loops AS INT = 100000 DECLARE @topSize AS INT = 10 -- Test variables DECLARE @temp AS NVARCHAR(MAX) DECLARE @resultstable TABLE ( id SMALLINT PRIMARY KEY CLUSTERED , method NVARCHAR(50) , starttime DATETIME2 , endtime DATETIME2 , timeused_ms AS ( DATEDIFF(ms, starttime, endtime) ) PERSISTED ) -- Clear out cache for true results DBCC freeproccache DBCC DROPCLEANBUFFERS -- Temp Table INSERT INTO @resultstable ( id, method, starttime ) VALUES ( 1, 'Temporary Table', GETDATE() ) CREATE TABLE #temptable ( c1 INT, c2 VARCHAR(MAX) ) DECLARE @temptablecounter AS INT = 0 , @innerresult1 AS NVARCHAR(MAX) WHILE @temptablecounter < @loops BEGIN SET @temptablecounter = @temptablecounter + 1 INSERT INTO #temptable ( c1, c2 ) VALUES ( RAND(123), NEWID() ) -- select the last top 10 rows into a local variable.. SET @temp = ( SELECT TOP ( @topSize ) ISNULL(c2 + CAST(c1 AS NVARCHAR(MAX)) + ' ,', '') FROM #temptable FOR XML PATH('') ) END DROP TABLE #temptable UPDATE r SET endtime = GETDATE() FROM @resultstable r WHERE r.id = 1 -- Table Variable INSERT INTO @resultstable ( id, method, starttime ) VALUES ( 2, 'Table Variable', GETDATE() ) DECLARE @variabletable TABLE ( c1 INT, c2 VARCHAR(MAX) ) DECLARE @tempvariablecounter AS INT = 0 , @innerresult2 AS NVARCHAR(MAX) WHILE @tempvariablecounter < @loops BEGIN SET @tempvariablecounter = @tempvariablecounter + 1 INSERT INTO @variabletable ( c1, c2 ) VALUES ( RAND(123), NEWID() ) -- select the last top 10 rows into a local variable.. SET @temp = ( SELECT TOP ( @topSize ) ISNULL(c2 + CAST(c1 AS NVARCHAR(MAX)) + ' ,', '') FROM @variabletable FOR XML PATH('') ) END UPDATE r SET endtime = GETDATE() FROM @resultstable r WHERE r.id = 2 -- Display results SELECT rt.id , rt.method , rt.starttime , rt.endtime , rt.timeused_ms FROM @resultstable rt
© 2013 Syntax Warriors