SQL Server で ROWCOUNT_BIG() 関数を使用して削除された行数をカウントする

2024-04-06

SQL Server ストアドプロシージャ内で削除された行数をカウントするには、いくつかの方法があります。

方法

@@ROWCOUNTを使用する

これは最も簡単な方法ですが、いくつかの制限があります。

DELETE ステートメントが単一のテーブルに対して実行されている必要があります。
OUTPUT 句を使用することはできません。
トランザクション内で実行されている必要があります。

以下の例は、Customers テーブルから削除された行数をカウントする方法を示しています。

USE AdventureWorks2019;

GO

CREATE PROCEDURE [dbo].[usp_DeleteCustomer]
    @CustomerID int
AS
BEGIN

    DELETE FROM Customers
    WHERE CustomerID = @CustomerID;

    SELECT @@ROWCOUNT AS DeletedRows;

END;

GO

EXEC [dbo].[usp_DeleteCustomer] 1;

OUTPUT 句を使用する

@@ROWCOUNT の制限を回避するには、OUTPUT 句を使用して削除された行数をカウントできます。

USE AdventureWorks2019;

GO

CREATE PROCEDURE [dbo].[usp_DeleteCustomer]
    @CustomerID int
AS
BEGIN

    DELETE FROM Customers
    OUTPUT DELETED.*
    WHERE CustomerID = @CustomerID;

END;

GO

DECLARE @DeletedRows int;

EXEC [dbo].[usp_DeleteCustomer] 1, @DeletedRows OUTPUT;

SELECT @DeletedRows AS DeletedRows;

トリガーを使用する

削除された行数を監査する必要がある場合は、トリガーを使用できます。

USE AdventureWorks2019;

GO

CREATE TRIGGER [dbo].[tr_Customer_Delete]
ON [dbo].[Customers]
AFTER DELETE
AS
BEGIN

    INSERT INTO AuditLog (TableName, RowsDeleted)
    VALUES ('Customers', @@ROWCOUNT);

END;

GO

DELETE FROM Customers
WHERE CustomerID = 1;

SELECT * FROM AuditLog;

テーブル変数を使用して、削除される行を一時的に保存し、その数をカウントできます。

USE AdventureWorks2019;

GO

DECLARE @DeletedCustomers TABLE (
    CustomerID int,
    FirstName varchar(50),
    LastName varchar(50)
);

INSERT INTO @DeletedCustomers
SELECT *
FROM Customers
WHERE CustomerID = 1;

DELETE FROM Customers
WHERE CustomerID = 1;

SELECT COUNT(*) AS DeletedRows
FROM @DeletedCustomers;

使用する方法は、要件と環境によって異なります。

その他の考慮事項

削除された行数を正確にカウントするには、トランザクション内で実行する必要があります。
削除された行の情報を保存する必要がある場合は、OUTPUT 句またはトリガーを使用する必要があります。

USE AdventureWorks2019;

GO

CREATE PROCEDURE [dbo].[usp_DeleteCustomer]
    @CustomerID int
AS
BEGIN

    DELETE FROM Customers
    WHERE CustomerID = @CustomerID;

    SELECT @@ROWCOUNT AS DeletedRows;

END;

GO

EXEC [dbo].[usp_DeleteCustomer] 1;

USE AdventureWorks2019;

GO

CREATE PROCEDURE [dbo].[usp_DeleteCustomer]
    @CustomerID int
AS
BEGIN

    DELETE FROM Customers
    OUTPUT DELETED.*
    WHERE CustomerID = @CustomerID;

END;

GO

DECLARE @DeletedRows int;

EXEC [dbo].[usp_DeleteCustomer] 1, @DeletedRows OUTPUT;

SELECT @DeletedRows AS DeletedRows;

USE AdventureWorks2019;

GO

CREATE TRIGGER [dbo].[tr_Customer_Delete]
ON [dbo].[Customers]
AFTER DELETE
AS
BEGIN

    INSERT INTO AuditLog (TableName, RowsDeleted)
    VALUES ('Customers', @@ROWCOUNT);

END;

GO

DELETE FROM Customers
WHERE CustomerID = 1;

SELECT * FROM AuditLog;

テーブル変数を使用する

USE AdventureWorks2019;

GO

DECLARE @DeletedCustomers TABLE (
    CustomerID int,
    FirstName varchar(50),
    LastName varchar(50)
);

INSERT INTO @DeletedCustomers
SELECT *
FROM Customers
WHERE CustomerID = 1;

DELETE FROM Customers
WHERE CustomerID = 1;

SELECT COUNT(*) AS DeletedRows
FROM @DeletedCustomers;

削除された行数をカウントする他の方法

ROWCOUNT_BIG() 関数は、@@ROWCOUNT よりも大きな値をカウントできます。

USE AdventureWorks2019;

GO

CREATE PROCEDURE [dbo].[usp_DeleteCustomer]
    @CustomerID int
AS
BEGIN

    DELETE FROM Customers
    WHERE CustomerID = @CustomerID;

    SELECT ROWCOUNT_BIG() AS DeletedRows;

END;

GO

EXEC [dbo].[usp_DeleteCustomer] 1;

システムテーブルを使用する

sys.dm_tran_commit_table システムテーブルを使用して、コミットされたトランザクション内の削除された行数をカウントできます。

USE AdventureWorks2019;

GO

SELECT COUNT(*) AS DeletedRows
FROM sys.dm_tran_commit_table
WHERE object_id = OBJECT_ID('Customers')
AND operation = 'DELETE';