Scalar-valued Functions on Computed Columns Prevent Parallelism

Parallelism is when a query is executed across multiple CPU threads. Usually (but not always), this means the query completes faster, compared to being executed using one thread (serial).

Tables that contain a computed column with a scalar function prevent a query from going parallel, even if the column isn’t referenced in your query.

Let’s look at an example. The following is a simple query against the StackOverflow2013 database (which is available as a free download).

				
					SELECT u.DisplayName, u.Id
FROM dbo.Users u
WHERE u.Location LIKE '%CA'
ORDER BY u.DisplayName;

				
			

This is a fairly nasty and slow query, so we want parallelism to speed it up. Sure enough, the plan shows that we have it.

Now let’s introduce a computed column with a user-defined scalar-valued function to the Users table. We’ll do this by adding a new column to hold the number of comments that each user has made and generate this value within a new function called fnGetCommentCountForUser.

Here’s what it looks like.

				
					CREATE OR ALTER FUNCTION [dbo].[fnGetCommentCountForUser](@UserID int)
RETURNS int
AS
BEGIN    
    RETURN (SELECT COUNT(*) FROM [dbo].[Comments] AS [C] WHERE [C].[UserId] = @UserID);
END
GO

ALTER TABLE [dbo].[Users] ADD CommentCount AS dbo.[fnGetCommentCountForUser]([Id]);

				
			

Let’s re-run the same query.

				
					SELECT u.DisplayName, u.Id
FROM dbo.Users u
WHERE u.Location LIKE '%CA'
ORDER BY u.DisplayName;

				
			

We have the same query plan but minus to parallelism operator!

Even though our query does not explicitly touch the computed column, it still can’t go parallel. Unfortunately, there’s no way of forcing the behaviour either.

If you want to see this in action, Erik Darling has a short but sweet video on this exact problem.

Summary

Problems caused by scalar functions are well documented. This is just one more example. My advice is to just avoid using them in computed columns (or check constraints!) unless you’re comfortable with the impact.

Thanks for reading!

Subscribe to our newsletter to be the first to hear about new features, vacancies, and industry news.

Picture of Shaun Austin

Shaun Austin

Shaun has been working with SQL Server for over 20 years, as both a developer and DBA. He is passionate about optimising and troubleshooting SQL Server environments, ensuring that customers get the maximum value from their cloud or on-premise infrastructure. Shaun regularly blogs about SQL Server administration and performance tuning, frequently contributing to user group channels on platforms such as LinkedIn.