SQL SERVER – Table Variables, Temp Tables and Parallel Queries

During the recent  Comprehensive Database Performance Health Check I heard a very interesting comment from my client and that made me write this blog post. Their point was about table variables, temp tables, and parallel queries. Let us discuss today.

My client thought that it is not a good idea to use Table Variables and Temp Tables as they do no support parallel operations. Actually, that is not true.

Table Variables and Temp Tables support Parallel Queries and Parallel Operations.

Let us see a very simple example of the same. The following query is using table variables and temp tables, the following script needs to run in a single execution. Please note that I have used the sample database AdventureWorks for this example.

-- Create test result
DECLARE @TableVar TABLE (SalesOrderID INT, CarrierTrackingNumber NVARCHAR(25));
INSERT INTO @TableVar (SalesOrderID, CarrierTrackingNumber)
SELECT [SalesOrderID], [CarrierTrackingNumber]
FROM [Sales].[SalesOrderDetail]
ORDER BY [UnitPrice] DESC;
CREATE TABLE #TableVar (SalesOrderID INT, CarrierTrackingNumber NVARCHAR(25));
INSERT INTO #TableVar (SalesOrderID, CarrierTrackingNumber)
SELECT [SalesOrderID], [CarrierTrackingNumber]
FROM [Sales].[SalesOrderDetail]
ORDER BY [UnitPrice] DESC;
-- Now SELECT data
SELECT *
FROM @TableVar
ORDER BY CarrierTrackingNumber DESC;
SELECT *
FROM #TableVar
ORDER BY CarrierTrackingNumber DESC;
-- Clean up
DROP TABLE #TableVar;

Now here is the execution plan of the part where I have selected the data.

It is very clear from the execution plan that when we select data from Table Variables and Temp Tables both support parallel operations.

Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

• Copy Database – SQL in Sixty Seconds #169
• 9 SQL SERVER Performance Tuning Tips – SQL in Sixty Seconds #168
• Excel – Sum vs SubTotal – SQL in Sixty Seconds #167
• 3 Ways to Configure MAXDOP – SQL in Sixty Seconds #166
• Get Memory Details – SQL in Sixty Seconds #165
• Get CPU Details – SQL in Sixty Seconds #164
• Shutdown SQL Server Via T-SQL – SQL in Sixty Seconds #163
• SQL Server on Linux – SQL in Sixty Seconds 162
• Query Ignoring CPU Threads – SQL in Sixty Seconds 161
• Bitwise Puzzle – SQL in Sixty Seconds 160
• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

Reference: Pinal Dave (http://blog.SQLAuthority.com)