gpt4 book ai didi

sql-server - 唯一约束的 SQL Server 筛选索引

转载 作者:行者123 更新时间:2023-12-01 04:08:09 26 4
gpt4 key购买 nike

我正在使用 SQL Server 2008。我有一个如下所示的数据库表(省略了不重要的列):

CREATE TABLE [dbo].[ImageDocument_FaxProperties](
[FaxPropertyID] [int] PRIMARY KEY IDENTITY(1,1),
[Agent] [varchar](25) NULL,
[ParentImageDocumentId] [uniqueidentifier] NULL
)

我想创建一个约束,只要每行的 ParentImageDocumentIds 相同,同一个 Agent 可以有多行,但 Agent 不能有不同 ParentImageDocumentIds 的行。我知道这不是一个好的表结构,但它是遗留的,我不允许更改它。 NULL ParentImageDocumentIds 应该被认为是不同的。

例如:
PK   Agent      ParentImageDocumentId  -This is ok
# person1 {D09C3900-0300} {.. other columns ..}
# person1 {D09C3900-0300} {.. other columns ..}

PK Agent ParentImageDocumentId -Check constraint prevents 2nd row insertion
# person1 NULL {.. other columns ..}
# person1 NULL {.. other columns ..}

PK Agent ParentImageDocumentId -Check constraint prevents 2nd row insertion
# person1 NULL {.. other columns ..}
# person1 {A13E5B21-93DE} {.. other columns ..}

PK Agent ParentImageDocumentId -Check constraint prevents 2nd row insertion
# person1 {D09C3900-0300} {.. other columns ..}
# person1 {A13E5B21-93DE} {.. other columns ..}

我想知道为此编写约束的最佳方法是什么。 Agent 上的唯一索引不起作用,因为它们有时可以同时拥有两者。 Agent 上的唯一值 ParentImageDocumentId 将允许它们具有不同的 GUID,这是不行的。带有“WHERE ParentImageDocumentId IS NULL AND Agent IS NOT NULL”的过滤索引将用于防止双重 NULL,但不能防止不同的 GUID 或 GUID & NULL。

以下是两个可行的解决方案,但是我想知道是否有更好的方法。索引模式绑定(bind) View 允许我创建更复杂的过滤索引。另一种方法是使用函数的表级检查约束,这应该可以工作,但是添加检查约束是超慢的。我猜它正在为表中的每一行重新运行我的函数,但这对于添加表级约束应该是不必要的,因为该函数没有检查特定行。有没有解决的办法?我倾向于索引 View ,但想知道是否有其他选择(除了更改我的表结构)以及哪种选择是最好的。

解决方案#1:
CREATE VIEW ImageDocument_FaxProperties_Assignments WITH SCHEMABINDING
AS
SELECT Agent, ParentImageDocumentId, COUNT_BIG(*) as numPages FROM dbo.ImageDocument_FaxProperties
WHERE Status IN ( 'PROC', 'LINKING' )
AND Agent IS NOT NULL
GROUP BY Agent, ParentImageDocumentId
GO

CREATE UNIQUE CLUSTERED INDEX [IDX_ImageDocument_FaxProperties_Assignments_Unique] ON [ImageDocument_FaxProperties_Assignments] (Agent)
GO

解决方案#2:
CREATE FUNCTION ImageDocument_FaxProperties_Assignments_CheckConstraint() RETURNS BIT
AS
BEGIN
DECLARE @Result BIT = 0;
WITH Assignments AS
(SELECT Agent, DENSE_RANK() OVER (PARTITION BY Agent ORDER BY ParentImageDocumentId) AS assignmentNum
FROM dbo.ImageDocument_FaxProperties
WHERE Status IN ( 'PROC', 'LINKING' )
AND Agent IS NOT NULL
)

SELECT @Result = 1 FROM Assignments WHERE assignmentNum > 1

RETURN @Result
END
GO
ALTER TABLE [ImageDocument_FaxProperties]
ADD CONSTRAINT ImageDocument_FaxProperties_Assignments_Unique CHECK (dbo.ImageDocument_FaxProperties_Assignments_CheckConstraint() = 0)

最佳答案

索引 View 将是您建议的两个中更好的选择。

UDF 中的这种逻辑效率低下(它 对每一行进行评估)并且难以正确处理。参见例如以下帖子

  • Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates
  • Snapshot isolation: A threat for integrity? (Part 4)
  • 关于sql-server - 唯一约束的 SQL Server 筛选索引,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/7988519/

    26 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com