代码之家 › 专栏 › 技术社区 › mwigdahl

检测MS SQL Server中列更改的最有效方法

performance sql-server-2000 sql-server-2008 tsql sql-server

23

mwigdahl · 技术社区 · 17 年前

我们的系统运行在SQL Server 2000上,我们正在准备升级到SQL Server 2008。我们有很多触发器代码,需要检测给定列中的更改,然后在该列发生更改时对其进行操作。

UPDATE() 和 COLUMNS_UPDATED() 不哪些列实际上已更改。

要确定哪些列已更改,您需要类似以下代码(对于支持null的列):

IF UPDATE(Col1)
    SELECT @col1_changed = COUNT(*) 
    FROM Inserted i
        INNER JOIN Deleted d ON i.Table_ID = d.Table_ID
    WHERE ISNULL(i.Col1, '<unique null value>') 
            != ISNULL(i.Col1, '<unique null value>')

对于您感兴趣测试的每一列,都需要重复此代码。然后,您可以检查“已更改”值以确定是否执行昂贵的操作。当然,这段代码本身是有问题的,因为它只告诉您,在所有被修改的行中,列中至少有一个值发生了更改。

您可以使用以下方法测试单个UPDATE语句:

UPDATE Table SET Col1 = CASE WHEN i.Col1 = d.Col1 
          THEN Col1 
          ELSE dbo.fnTransform(Col1) END
FROM Inserted i
    INNER JOIN Deleted d ON i.Table_ID = d.Table_ID

... 但是,当您需要调用存储过程时,这种方法不起作用。在这种情况下,你必须依靠其他方法,据我所知。

我的问题是,对于在触发器中根据修改行中的特定列值是否实际发生了更改来预测数据库操作的问题,是否有人有见解(或者更好的是,有硬数据)知道最好/最便宜的方法是什么。上述两种方法似乎都不理想,我想知道是否存在更好的方法。

5 回复 | 直到 13 年前

1

19

HLGEM 17 年前

让我们从我永远不会,我的意思是永远不会调用触发器中存储的进程开始。要考虑多行插入,您必须将光标移到进程中。这意味着您刚刚通过基于集合的查询加载的200000行(例如,将所有价格更新10%)可能会在触发器尝试处理加载时将表锁定数小时。另外,如果进程中发生了一些变化,您可以完全中断对表的任何插入,甚至完全挂起表。我坚信触发器代码不应该调用触发器之外的任何东西。

就我个人而言,我更喜欢简单地完成我的任务。如果我已经在触发器中编写了我想要正确执行的操作,它将只更新、删除或插入已更改的列。

示例:假设出于性能原因,您希望更新存储在两个位置的last_name字段,因为该字段中放置了非规范化。

update t
set lname = i.lname
from table2 t 
join inserted i on t.fkfield = i.pkfield
where t.lname <>i.lname

正如您所见,它只会更新与我正在更新的表中当前不同的LName。

如果要执行审核并仅记录更改的行,则使用以下所有字段进行比较

2

10

arghtype Castaldi 12 年前

我想您可能需要使用EXCEPT运算符进行调查。它是一个基于集合的运算符,可以删除未更改的行。好的一面是,当它在EXCEPT运算符之前列出的第一个集合中查找行时,将null值视为相等,而在EXCEPT运算符之后列出的第二个集合中不查找行

WITH ChangedData AS (
SELECT d.Table_ID , d.Col1 FROM deleted d
EXCEPT 
SELECT i.Table_ID , i.Col1  FROM inserted i
)
/*Do Something with the ChangedData */

这将处理允许在不使用 ISNULL() 在触发器中,只返回更改为col1的行的ID,这是一种基于集合的检测更改的好方法。我还没有测试过这种方法,但它很值得你花时间。我认为除了SQL Server 2005之外,它还引入了其他功能。

3

8

mwigdahl 17 年前

尽管HLGEM在上面给出了一些很好的建议,但这并不是我所需要的。在过去的几天里,我做了很多测试,我想我至少应该在这里分享一下结果,因为看起来不会有更多的信息。

我设置了一个表,它实际上是我们系统的一个主表的一个较窄的子集(9列),并用生产数据填充它,使其与我们的生产版本的表一样深。

然后,我复制了该表,在第一个表中,我编写了一个触发器,试图检测每一个单独的列更改,然后根据该列中的数据是否实际更改来判断每个列的更新。

然后我运行了4个测试:

将单个列更新为单个行
一列更新到10000行
将九列更新为一行
九列更新到10000行

我得到的结果相当有趣:

第二种方法(SET子句中的一个update语句具有复杂的大小写逻辑)的性能一致优于单独的更改检测(在很大程度上取决于测试),只有一个例外,即在SQL 2000上运行的单个列更改会影响索引该列的许多行。在我们的特殊情况下,我们不会做很多像这样的狭隘、深入的更新,因此就我的目的而言,单语句方法绝对是一种可行的方法。

我很想听听其他人对类似类型测试的结果,看看我的结论是否像我怀疑的那样普遍,或者它们是否特定于我们的特定配置。

为了让您开始,这里是我使用的测试脚本——您显然需要拿出其他数据来填充它:

create table test1
( 
    t_id int NOT NULL PRIMARY KEY,
    i1 int NULL,
    i2 int NULL,
    i3 int NULL,
    v1 varchar(500) NULL,
    v2 varchar(500) NULL,
    v3 varchar(500) NULL,
    d1 datetime NULL,
    d2 datetime NULL,
    d3 datetime NULL
)

create table test2
( 
    t_id int NOT NULL PRIMARY KEY,
    i1 int NULL,
    i2 int NULL,
    i3 int NULL,
    v1 varchar(500) NULL,
    v2 varchar(500) NULL,
    v3 varchar(500) NULL,
    d1 datetime NULL,
    d2 datetime NULL,
    d3 datetime NULL
)

-- optional indexing here, test with it on and off...
CREATE INDEX [IX_test1_i1] ON [dbo].[test1] ([i1])
CREATE INDEX [IX_test1_i2] ON [dbo].[test1] ([i2])
CREATE INDEX [IX_test1_i3] ON [dbo].[test1] ([i3])
CREATE INDEX [IX_test1_v1] ON [dbo].[test1] ([v1])
CREATE INDEX [IX_test1_v2] ON [dbo].[test1] ([v2])
CREATE INDEX [IX_test1_v3] ON [dbo].[test1] ([v3])
CREATE INDEX [IX_test1_d1] ON [dbo].[test1] ([d1])
CREATE INDEX [IX_test1_d2] ON [dbo].[test1] ([d2])
CREATE INDEX [IX_test1_d3] ON [dbo].[test1] ([d3])

CREATE INDEX [IX_test2_i1] ON [dbo].[test2] ([i1])
CREATE INDEX [IX_test2_i2] ON [dbo].[test2] ([i2])
CREATE INDEX [IX_test2_i3] ON [dbo].[test2] ([i3])
CREATE INDEX [IX_test2_v1] ON [dbo].[test2] ([v1])
CREATE INDEX [IX_test2_v2] ON [dbo].[test2] ([v2])
CREATE INDEX [IX_test2_v3] ON [dbo].[test2] ([v3])
CREATE INDEX [IX_test2_d1] ON [dbo].[test2] ([d1])
CREATE INDEX [IX_test2_d2] ON [dbo].[test2] ([d2])
CREATE INDEX [IX_test2_d3] ON [dbo].[test2] ([d3])

insert into test1 (t_id, i1, i2, i3, v1, v2, v3, d1, d2, d3)
-- add data population here...

insert into test2 (t_id, i1, i2, i3, v1, v2, v3, d1, d2, d3)
select t_id, i1, i2, i3, v1, v2, v3, d1, d2, d3 from test1

go

create trigger test1_update on test1 for update
as
begin

declare @i1_changed int,
    @i2_changed int,
    @i3_changed int,
    @v1_changed int,
    @v2_changed int,
    @v3_changed int,
    @d1_changed int,
    @d2_changed int,
    @d3_changed int

IF UPDATE(i1)
    SELECT @i1_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.i1,0) != ISNULL(d.i1,0)
IF UPDATE(i2)
    SELECT @i2_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.i2,0) != ISNULL(d.i2,0)
IF UPDATE(i3)
    SELECT @i3_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.i3,0) != ISNULL(d.i3,0)
IF UPDATE(v1)
    SELECT @v1_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.v1,'') != ISNULL(d.v1,'')
IF UPDATE(v2)
    SELECT @v2_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.v2,'') != ISNULL(d.v2,'')
IF UPDATE(v3)
    SELECT @v3_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.v3,'') != ISNULL(d.v3,'')
IF UPDATE(d1)
    SELECT @d1_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.d1,'1/1/1980') != ISNULL(d.d1,'1/1/1980')
IF UPDATE(d2)
    SELECT @d2_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.d2,'1/1/1980') != ISNULL(d.d2,'1/1/1980')
IF UPDATE(d3)
    SELECT @d3_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.d3,'1/1/1980') != ISNULL(d.d3,'1/1/1980')

if (@i1_changed > 0)
begin
    UPDATE test1 SET i1 = CASE WHEN i.i1 > d.i1 THEN i.i1 ELSE d.i1 END
    FROM test1
        INNER JOIN inserted i ON test1.t_id = i.t_id
        INNER JOIN deleted d ON i.t_id = d.t_id
    WHERE i.i1 != d.i1
end

if (@i2_changed > 0)
begin
    UPDATE test1 SET i2 = CASE WHEN i.i2 > d.i2 THEN POWER(i.i2, 1.1) ELSE POWER(d.i2, 1.1) END
    FROM test1
        INNER JOIN inserted i ON test1.t_id = i.t_id
        INNER JOIN deleted d ON i.t_id = d.t_id
    WHERE i.i2 != d.i2
end

if (@i3_changed > 0)
begin
    UPDATE test1 SET i3 = i.i3 ^ d.i3
    FROM test1
        INNER JOIN inserted i ON test1.t_id = i.t_id
        INNER JOIN deleted d ON i.t_id = d.t_id
    WHERE i.i3 != d.i3
end

if (@v1_changed > 0)
begin
    UPDATE test1 SET v1 = i.v1 + 'a'
    FROM test1
        INNER JOIN inserted i ON test1.t_id = i.t_id
        INNER JOIN deleted d ON i.t_id = d.t_id
    WHERE i.v1 != d.v1
end

UPDATE test1 SET v2 = LEFT(i.v2, 5) + '|' + RIGHT(d.v2, 5)
FROM test1
    INNER JOIN inserted i ON test1.t_id = i.t_id
    INNER JOIN deleted d ON i.t_id = d.t_id

if (@v3_changed > 0)
begin
    UPDATE test1 SET v3 = LEFT(i.v3, 5) + '|' + LEFT(i.v2, 5) + '|' + LEFT(i.v1, 5)
    FROM test1
        INNER JOIN inserted i ON test1.t_id = i.t_id
        INNER JOIN deleted d ON i.t_id = d.t_id
    WHERE i.v3 != d.v3
end

if (@d1_changed > 0)
begin
    UPDATE test1 SET d1 = DATEADD(dd, 1, i.d1)
    FROM test1
        INNER JOIN inserted i ON test1.t_id = i.t_id
        INNER JOIN deleted d ON i.t_id = d.t_id
    WHERE i.d1 != d.d1
end

if (@d2_changed > 0)
begin
    UPDATE test1 SET d2 = DATEADD(dd, DATEDIFF(dd, i.d2, d.d2), d.d2)
    FROM test1
        INNER JOIN inserted i ON test1.t_id = i.t_id
        INNER JOIN deleted d ON i.t_id = d.t_id
    WHERE i.d2 != d.d2
end

UPDATE test1 SET d3 = DATEADD(dd, 15, i.d3)
FROM test1
    INNER JOIN inserted i ON test1.t_id = i.t_id
    INNER JOIN deleted d ON i.t_id = d.t_id

end

go

create trigger test2_update on test2 for update
as
begin

    UPDATE test2 SET
        i1 = 
            CASE
            WHEN ISNULL(i.i1, 0) != ISNULL(d.i1, 0)
            THEN CASE WHEN i.i1 > d.i1 THEN i.i1 ELSE d.i1 END
            ELSE test2.i1 END,
        i2 = 
            CASE
            WHEN ISNULL(i.i2, 0) != ISNULL(d.i2, 0)
            THEN CASE WHEN i.i2 > d.i2 THEN POWER(i.i2, 1.1) ELSE POWER(d.i2, 1.1) END
            ELSE test2.i2 END,
        i3 = 
            CASE
            WHEN ISNULL(i.i3, 0) != ISNULL(d.i3, 0)
            THEN i.i3 ^ d.i3
            ELSE test2.i3 END,
        v1 = 
            CASE
            WHEN ISNULL(i.v1, '') != ISNULL(d.v1, '')
            THEN i.v1 + 'a'
            ELSE test2.v1 END,
        v2 = LEFT(i.v2, 5) + '|' + RIGHT(d.v2, 5),
        v3 = 
            CASE
            WHEN ISNULL(i.v3, '') != ISNULL(d.v3, '')
            THEN LEFT(i.v3, 5) + '|' + LEFT(i.v2, 5) + '|' + LEFT(i.v1, 5)
            ELSE test2.v3 END,
        d1 = 
            CASE
            WHEN ISNULL(i.d1, '1/1/1980') != ISNULL(d.d1, '1/1/1980')
            THEN DATEADD(dd, 1, i.d1)
            ELSE test2.d1 END,
        d2 = 
            CASE
            WHEN ISNULL(i.d2, '1/1/1980') != ISNULL(d.d2, '1/1/1980')
            THEN DATEADD(dd, DATEDIFF(dd, i.d2, d.d2), d.d2)
            ELSE test2.d2 END,
        d3 = DATEADD(dd, 15, i.d3)
    FROM test2
        INNER JOIN inserted i ON test2.t_id = i.t_id
        INNER JOIN deleted d ON test2.t_id = d.t_id

end

go

-----
-- the below code can be used to confirm that the triggers operated identically over both tables after a test
select top 10 test1.i1, test2.i1, test1.i2, test2.i2, test1.i3, test2.i3, test1.v1, test2.v1, test1.v2, test2.v2, test1.v3, test2.v3, test1.d1, test1.d1, test1.d2, test2.d2, test1.d3, test2.d3
from test1 inner join test2 on test1.t_id = test2.t_id
where 
    test1.i1 != test2.i1 or 
    test1.i2 != test2.i2 or
    test1.i3 != test2.i3 or
    test1.v1 != test2.v1 or 
    test1.v2 != test2.v2 or
    test1.v3 != test2.v3 or
    test1.d1 != test2.d1 or 
    test1.d2 != test2.d2 or
    test1.d3 != test2.d3

-- test 1 -- one column, one row
update test1 set i3 = 64 where t_id = 1000
go
update test2 set i3 = 64 where t_id = 1000
go

update test1 set i3 = 64 where t_id = 1001
go
update test2 set i3 = 64 where t_id = 1001
go

-- test 2 -- one column, 10000 rows
update test1 set v3 = LEFT(v3, 50) where t_id between 10000 and 20000
go
update test2 set v3 = LEFT(v3, 50) where t_id between 10000 and 20000
go

-- test 3 -- all columns, 1 row, non-self-referential
update test1 set i1 = 1000, i2 = 2000, i3 = 3000, v1 = 'R12345123', v2 = 'Happy!', v3 = 'I am v3!!!', d1 = '1/1/1985', d2 = '1/1/1988', d3 = NULL
where t_id = 3000
go
update test2 set i1 = 1000, i2 = 2000, i3 = 3000, v1 = 'R12345123', v2 = 'Happy!', v3 = 'I am v3!!!', d1 = '1/1/1985', d2 = '1/1/1988', d3 = NULL
where t_id = 3000
go

-- test 4 -- all columns, 10000 rows, non-self-referential
update test1 set i1 = 1000, i2 = 2000, i3 = 3000, v1 = 'R12345123', v2 = 'Happy!', v3 = 'I am v3!!!', d1 = '1/1/1985', d2 = '1/1/1988', d3 = NULL
where t_id between 30000 and 40000
go
update test2 set i1 = 1000, i2 = 2000, i3 = 3000, v1 = 'R12345123', v2 = 'Happy!', v3 = 'I am v3!!!', d1 = '1/1/1985', d2 = '1/1/1988', d3 = NULL
where t_id between 30000 and 40000
go

-----

drop table test1
drop table test2

4

6

David Coster 9 年前

我之所以添加这个答案,是因为我将“插入”放在“删除”之前,以便检测插入和更新。所以我通常可以有一个触发器来覆盖插入和更新。还可以通过添加或(不存在(选择*从插入)和存在(选择*从删除)来检测删除

它使用EXCEPT set运算符从左侧查询返回在右侧查询中找不到的任何行。此代码可用于插入、更新和删除触发器。

“PKID”列是主键。需要启用两组之间的匹配。如果主键有多个列,则需要包含所有列,以便在插入集和删除集之间进行正确匹配。

-- Only do trigger logic if specific field values change.
IF EXISTS(SELECT  PKID
                ,Column1
                ,Column7
                ,Column10
          FROM inserted
          EXCEPT
          SELECT PKID
                ,Column1
                ,Column7
                ,Column10
          FROM deleted )    -- Tests for modifications to fields that we are interested in
OR (NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)) -- Have a deletion
BEGIN
          -- Put code here that does the work in the trigger

END

如果要在后续触发器逻辑中使用更改的行,我通常会将EXCEPT查询的结果放入一个表变量中,以后可以引用该表变量。

5

4

Marek Grzenkowicz 14 年前

SQL Server 2008中还有另一种用于更改跟踪的技术:

Comparing Change Data Capture and Change Tracking