What is WorkTable in SQL Server? – Interview Question of the Week #146

Question: What is WorkTable in SQL Server?

Answer: If there is one question, I have been waiting to receive was this one. I had often thought that I will just make this one and post it out, but I have always kept the policy that I will only post questions which I see in interviews or in my SQL Server Performance Tuning Practical Workshop consultancy.

Before I answer this question, let us see where do we see worktables in SQL Server.

Let us run following script in the SQL Server Management Studio on the sample database AdventureWorks.

USE AdventureWorks2014
GO
SET STATISTICS IO ON
GO
SELECT *
FROM Production.Product p
CROSS JOIN Production.Product p1
GO

If you run above query, there is good chance that it will give you following details in the message windows.

Table ‘Product’. Scan count 2, logical reads 30, physical reads 1, read-ahead reads 17, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable‘. Scan count 1, logical reads 8110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You will notice in the message there is a table called worktable. However, if you see the original table there is no worktable. This is where the question has originated.

The question in another word is – Why does statistics show the worktable where it is not there in the original query?

Well, the answer is very simple – Quite often SQL Server has to perform any logical operations for any specific queries and to perform these logical operations SQL Server has to build a worktable. Worktables are built in tempdb and are dropped automatically when they are no longer needed.

So in other words, if you see worktable in SQL statistics, it means it is using TempDB.

I will be happy to read your experience and further observations. Please leave a comment.

Reference: Pinal Dave (https://blog.sqlauthority.com)