主页 / user-247288

Anil Raghavan's questions

Martin Hope
Anil Raghavan
Asked: 2022-02-03 06:10:46 +0800 CST
  • 6

在我最喜欢的 SQL Server 大师 Brent Ozar 的网站上引用Erik Darling 的这篇博文：

当您单独从该表中选择时，它会显示“ CouldNotGenerateValidParallelPlan ”。

但是，当您将该表连接到另一个没有调用标量 UDF 的检查约束/计算列的表时，查询将与“找到足够好的计划”并行

USE tempdb;
SET NOCOUNT ON;

SELECT TOP 10000
       ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )) AS ID, DATEADD(MINUTE, m.message_id, SYSDATETIME()) AS SomeDate
INTO   dbo.constraint_test_1
FROM   sys.messages AS m, sys.messages AS m2;
GO

SELECT TOP 10000
       ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )) AS ID, DATEADD(MINUTE, m.message_id, SYSDATETIME()) AS SomeDate
INTO   dbo.constraint_test_2
FROM   sys.messages AS m, sys.messages AS m2;
GO

CREATE  FUNCTION dbo.DateCheck ( @d DATETIME2(7))
RETURNS BIT
WITH    RETURNS NULL ON NULL INPUT
AS
    BEGIN
        DECLARE @Out BIT;
        SELECT  @Out = CASE WHEN @d < DATEADD(DAY, 30, SYSDATETIME()) THEN 1 ELSE 0 END;
        RETURN  @Out;
    END;
GO

ALTER TABLE dbo.constraint_test_1 ADD CONSTRAINT ck_cc_dt CHECK ( dbo.DateCheck(SomeDate) = 1 );

SELECT  *
FROM    dbo.constraint_test_1
OPTION  (QUERYTRACEON 8649, MAXDOP 0, RECOMPILE); -- Does not go parallel

SELECT  T1.ID, T2.SomeDate
FROM    dbo.constraint_test_1 T1 INNER JOIN
        dbo.constraint_test_2 T2 ON T1.ID = T2.ID
OPTION  (QUERYTRACEON 8649, MAXDOP 0, RECOMPILE); -- Goes parallel
sql-server parallelism