Filter by partition number when the table is partitioned by computed column

Question

I've created 2 identical tables partitioned by an integer column. In one of the tables the column is computed, in the other table the column is not computed.

When I query the table with computed column filtering a single partition sorting with Clustered index (assuming that outcoming data is already sorted, so no additional sorting is required) it scans the entire table.

CREATE PARTITION FUNCTION pf_test(int) AS RANGE RIGHT FOR VALUES(1, 2, 3, 4)
GO
CREATE PARTITION SCHEME ps_test AS PARTITION pf_test ALL TO([PRIMARY])
GO
CREATE TABLE test_computed
(
    ID BIGINT NOT NULL, 
    ID_C AS CAST(ID % 4 AS INT) PERSISTED, 
    PRIMARY KEY CLUSTERED (ID, ID_C) ON ps_test(ID_C)
) ON ps_test(ID_C)
GO
    
CREATE TABLE test_not_computed
(
    ID BIGINT NOT NULL, 
    ID_C INT NOT NULL, 
    PRIMARY KEY CLUSTERED (ID, ID_C) ON ps_test(ID_C)
) ON ps_test(ID_C)
GO
    
INSERT INTO test_computed(ID)
SELECT TOP 1000000 ROW_NUMBER() OVER(ORDER BY GETDATE())
FROM sys.all_columns a
CROSS JOIN sys.all_columns b
GO
    
INSERT INTO test_not_computed(ID, ID_C)
SELECT TOP 1000000 
    ROW_NUMBER() OVER(ORDER BY GETDATE()), 
    ROW_NUMBER() OVER(ORDER BY GETDATE()) % 4
FROM sys.all_columns a
CROSS JOIN sys.all_columns b
GO

The data is identical, but the execution plans for each query is different.

SELECT TOP 100 *
FROM test_computed
WHERE $partition.pf_test(ID_C) = 1
ORDER BY ID DESC
    
SELECT TOP 100 *
FROM test_not_computed
WHERE $partition.pf_test(ID_C) = 1
ORDER BY ID desc

The real table has billions of rows; we're using partitioning to avoid scanning the entire table.

SQL Server version is

Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64)
Mar 29 2024 23:02:47 Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: )

Answer 1

Early expansion of the computed column interferes with pushing the $partition predicate down into the scan.

The expansion is matched back to the persisted computed column later in the compilation process, but by then it's too late. You end up with a Compute Scalar and Filter combination instead of a nice simple seek on the partition ID. The breakdown in logic also leads to a sort that a human can see isn't necessary.

It's an unfortunate behaviour without a fully supported solution.

To see the difference not expanding the computed column makes, you can try your query with trace flag 176:

SELECT TOP (100) 
    TC.*
FROM dbo.test_computed AS TC
WHERE 
    $PARTITION.pf_test(TC.ID_C) = 1
ORDER BY 
    TC.ID DESC
OPTION (QUERYTRACEON 176);

The execution plan is then:

I wrote about this in my article, Properly Persisted Computed Columns.