主页 / dba / 问题 / 343858
Accepted
Michael J Swart
Asked: 2024-11-26 23:45:27 +0800 CST

如何快速添加受信任的检查约束

  • 772

我正在向一个大表添加检查约束，因为我想准备使用分区切换将其切换为分区表。

该检查是对列的简单不等式检查，并且该列上有索引。但是当我添加约束时，SQL Server 仍然会扫描整个表。是否可以快速添加约束并保持其可信？我希望这可以通过索引来实现。

以下是该扫描的再现图：

USE tempdb;
GO

SELECT * 
INTO MY_MESSAGES
FROM sys.messages;

CREATE CLUSTERED INDEX IX_MY_MESSAGES ON dbo.MY_MESSAGES(message_id);

SET STATISTICS IO ON;
ALTER TABLE dbo.MY_MESSAGES
    ADD CONSTRAINT CK_mymessages CHECK (message_id < 50000);
SET STATISTICS IO OFF;

DROP TABLE IF EXISTS dbo.MY_MESSAGES;

这显示在消息选项卡中：

Table 'MY_MESSAGES'. Scan count 1, logical reads 10955
sql-server

3 个回答

  1. Best Answer

    不幸的是，约束检查逻辑非常基础，不会像您希望的那样利用索引。它不会寻找不满足约束的值。它可以，但它没有。

    正如丹所说，薄的非聚集索引可以使扫描更有效率。

    Table 'MY_MESSAGES'. Scan count 1, logical reads 314, physical reads 0

    您可以使用查询存储捕获​​查询计划。它将显示扫描、带有CASE表达式的计算标量以测试约束条件，以及在必要时抛出错误的断言。

    查询商店计划

    使用非聚集索引

    [Expr1003] = Scalar Operator(
    CASE WHEN [dbo].[MY_MESSAGES].[message_id]>=(50000) THEN (0) ELSE NULL END)

    *** Output Tree: (trivial plan) ***

PhyOp_StreamCheck (Check COLUMN CNST: CK_mymessages COL: message_id) 
    PhyOp_ComputeScalar
        PhyOp_NOP
            PhyOp_Range 
                TBL: dbo.MY_MESSAGES(2) ASC  
                Bmk ( QCOL: [dbo].[MY_MESSAGES].message_id COL: Uniq1001 ) 
                IsRow: COL: IsBaseRow1000 
        AncOp_PrjList 
            AncOp_PrjEl COL: Expr1003 
                ScaOp_Intrinsic x_iopBuildSwitch
                    ScaOp_Comp x_cmpGe
                        ScaOp_Identifier QCOL: [dbo].[MY_MESSAGES].message_id
                        ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=50000)
    ScaOp_Identifier COL: Expr1003
    • 3

  2. 需要对表或索引进行全面扫描，以确保现有值符合检查约束表达式。启用非聚集索引后，您可能会看到性能略有改善，message_id因为验证所需的逻辑读取次数会减少，但仍需要扫描索引中的每一行。

    我正在向一个大表添加检查约束，因为我想准备使用分区切换将其切换为分区表。

    考虑以与目标分区表类似的方式对该表进行分区。这将确保源分区中的数据符合目标表分区边界，允许使用SWITCH不受信任的检查约束，从而避免使用扫描来验证数据。

    • 2

  3. 可以使用“WITH NOCHECK”快速添加约束以忽略现有数据。您可以信任该约束以应用于新/更改的数据。您不知道现有数据是否符合检查约束。但是，您可以运行自己的选择查询来验证现有数据。可以使用 PK 进行批处理检查，因为一旦检查了范围，它将保持有效。

    ALTER TABLE TableName
WITH NOCHECK
ADD CONSTRAINT ConstraintName CHECK (Condition);
    • 0

相关问题