Inline UDFs do not prevent parallel execution plans

Lots of things can prevent parallel execution plans, but inline UDFs are not one of them. Inline UDFs are essentially macros - they are at least as fast as CTEs. As such, the optimizer can produce exactly the same parallel execution plans whether if our queries are wrapped in inline UDFs or copied and pasted into the main query.

As usual, I will provide a repro script.

Prerequisites

Let us set up a table with 2 million rows - that should be enough to qualify for a parallel plan:


CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY);
GO

INSERT INTO dbo.Numbers
    ( n )
        VALUES  ( 1 );
GO
DECLARE @i INT;
    SET @i=0;
WHILE @i<21  
    BEGIN
    INSERT INTO dbo.Numbers
        ( n )
        SELECT n + POWER(2, @i)
        FROM dbo.Numbers;
    SET @i = @i + 1;
    END;  
GO

SELECT  n ,
        'asdfasdfas' AS Filler
INTO    dbo.ParallelTest
FROM    dbo.Numbers ;
GO

ALTER TABLE dbo.ParallelTest ADD CONSTRAINT PK_ParallelTest PRIMARY KEY(n) ;
GO  

Running a query with parallel execution plan

The following query runs with parallel execution plan (number of executions is 12):

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT COUNT(*) FROM dbo.ParallelTest
WHERE Filler LIKE '%nosuchstring%'

Let us wrap it in an inline UDF:

CREATE FUNCTION dbo.MatchCountInParallelTestByPattern ( @Pattern VARCHAR(10) )
RETURNS TABLE
AS RETURN
  ( SELECT  COUNT(*) AS MatchCount
    FROM    dbo.ParallelTest
    WHERE   Filler LIKE @Pattern
  ) ;

The following query invokes the inline UDF, yet still runs with exactly the same parallel execution plan (number of executions is the same: 12):


SELECT  MatchCount
FROM    dbo.MatchCountInParallelTestByPattern('%nosuchstring%') ;
 

Conclusion

At the time of this writing, reusing code wrapped in inline UDFs does not prevent parallel execution plans, at least on the following version:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )