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.

SQL SERVER - Table Variables, Temp Tables and Parallel Queries parallelqueries2-800x129

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.

SQL SERVER - Table Variables, Temp Tables and Parallel Queries parallelqueries

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.

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

Parallel, SQL CPU, SQL Scripts, SQL Server, SQL Variable, Temp Table
Previous Post
SQL SERVER – 3 Questions Answered on One Query Many Plans
Next Post
SQL SERVER – Index Scans are Not Always Bad

Related Posts

Leave a Reply