ROWCOUNT کنترل جریان و بهینه سازی در sqlserver

افزایش کارایی عملیات SQL Server با درک عمیق @@ROWCOUNT

تابع سیستمی سراسری `@@ROWCOUNT` در SQL Server، یک ابزار حیاتی برای کنترل جریان منطقی در دستورات و رویه‌های ذخیره شده (Stored Procedures) است. این تابع، تعداد ردیف‌های تحت تأثیر آخرین دستور T-SQL اجرا شده را برمی‌گرداند. درک صحیح نحوه عملکرد آن برای نوشتن کد SQL بهینه، قابل اعتماد و کارآمد ضروری است. این تابع به شما کمک می‌کند تا تصمیمات منطقی مبتنی بر نتایج عملیات‌های پایگاه داده بگیرید، مانند بررسی اینکه آیا یک عملیات درج، به روزرسانی یا حذف موفق بوده و چند ردیف را تحت تأثیر قرار داده است.

**نحوه عملکرد @@ROWCOUNT**

مقدار `@@ROWCOUNT` پس از هر دستور T-SQL به روز می‌شود. این به روزرسانی برای انواع دستورات SQL مانند `SELECT`, `INSERT`, `UPDATE`, `DELETE` و حتی دستورات کنترلی مانند `SET`, `PRINT`, `IF` اتفاق می‌افتد.

برای مثال، یک دستور `SELECT` مقدار `@@ROWCOUNT` را به تعداد ردیف‌هایی که از پایگاه داده بازیابی کرده، تنظیم می‌کند. این امکان را به شما می‌دهد تا بلافاصله پس از یک پرس و جو (query)، از تعداد نتایج مطلع شوید.

SELECT *
FROM Production.Product
WHERE ProductID < 100;

SELECT @@ROWCOUNT AS RowsAffected;

در این مثال، `@@ROWCOUNT` تعداد ردیف‌های بازگردانده شده توسط دستور `SELECT` قبلی را نشان می‌دهد.

**@@ROWCOUNT با دستورات تغییر داده (DML)**

دستورات تغییر داده (DML) مانند `INSERT`, `UPDATE`, و `DELETE` نیز `@@ROWCOUNT` را به تعداد ردیف‌های واقعی که تحت تأثیر قرار گرفته‌اند، تنظیم می‌کنند. این برای اطمینان از اینکه عملیات شما به درستی انجام شده، بسیار مفید است.

برای مشاهده تعداد ردیف‌های درج شده:

CREATE TABLE dbo.TestRowCount (ID INT IDENTITY(1,1), Name NVARCHAR(50));
INSERT INTO dbo.TestRowCount (Name) VALUES ('Test1'), ('Test2');
SELECT @@ROWCOUNT AS InsertedRows;
DROP TABLE dbo.TestRowCount;

در اینجا، `InsertedRows` تعداد ردیف‌هایی را که توسط دستور `INSERT` درج شده‌اند، نمایش می‌دهد.

برای مشاهده تعداد ردیف‌های به‌روزرسانی شده:

CREATE TABLE dbo.TestRowCount (ID INT IDENTITY(1,1), Name NVARCHAR(50));
INSERT INTO dbo.TestRowCount (Name) VALUES ('Test1'), ('Test2'), ('Test3');
UPDATE dbo.TestRowCount SET Name = 'UpdatedTest' WHERE Name = 'Test1' OR Name = 'Test2';
SELECT @@ROWCOUNT AS UpdatedRows;
DROP TABLE dbo.TestRowCount;

`UpdatedRows` تعداد ردیف‌هایی را نشان می‌دهد که توسط دستور `UPDATE` اصلاح شده‌اند.

برای مشاهده تعداد ردیف‌های حذف شده:

CREATE TABLE dbo.TestRowCount (ID INT IDENTITY(1,1), Name NVARCHAR(50));
INSERT INTO dbo.TestRowCount (Name) VALUES ('Test1'), ('Test2'), ('Test3');
DELETE FROM dbo.TestRowCount WHERE Name = 'Test1';
SELECT @@ROWCOUNT AS DeletedRows;
DROP TABLE dbo.TestRowCount;

`DeletedRows` تعداد ردیف‌هایی را که توسط دستور `DELETE` حذف شده‌اند، برمی‌گرداند.

با این حال، دستور `TRUNCATE TABLE` رفتار متفاوتی دارد. این دستور به سرعت تمام ردیف‌ها را از یک جدول حذف می‌کند اما `@@ROWCOUNT` را به 0 تنظیم می‌کند، زیرا این عملیات یک عملیات DDL (Data Definition Language) است و نه DML که تک تک ردیف‌ها را پردازش کند.

CREATE TABLE dbo.TestRowCount (ID INT IDENTITY(1,1), Name NVARCHAR(50));
INSERT INTO dbo.TestRowCount (Name) VALUES ('Test1'), ('Test2');
TRUNCATE TABLE dbo.TestRowCount;
SELECT @@ROWCOUNT AS TruncatedRows;
DROP TABLE dbo.TestRowCount;

همانطور که مشاهده می‌کنید، `TruncatedRows` مقدار 0 را برمی‌گرداند، حتی اگر جدول خالی شده باشد.

**تأثیر SET NOCOUNT ON**

گزینه `SET NOCOUNT ON` از ارسال پیام‌های مربوط به تعداد ردیف‌های تحت تأثیر یک دستور T-SQL به کلاینت جلوگیری می‌کند. این قابلیت به ویژه برای رویه‌های ذخیره شده (Stored Procedures) بسیار مفید است، زیرا با کاهش ترافیک شبکه، کارایی را بهبود می‌بخشد. با این حال، باید توجه داشت که با وجود `SET NOCOUNT ON`، تابع `@@ROWCOUNT` همچنان مقدار صحیح تعداد ردیف‌های تحت تأثیر آخرین دستور را در خود نگه می‌دارد و فقط از ارسال پیام‌های اضافی به کلاینت جلوگیری می‌کند. به عنوان مثال، اگر اسکریپت زیر را اجرا کنید، پیام تعداد ردیف‌های مربوط به دستور SELECT را مشاهده نخواهید کرد، اما `@@ROWCOUNT` مقدار آن را نشان خواهد داد.

SET NOCOUNT ON;
SELECT * FROM Production.Product WHERE ProductID < 100;
SELECT @@ROWCOUNT AS RowsAffectedWithNoCountOn;
SET NOCOUNT OFF;

در اینجا، با وجود `SET NOCOUNT ON`، `RowsAffectedWithNoCountOn` همچنان تعداد ردیف‌های بازگردانده شده توسط `SELECT` را نمایش می‌دهد.

**محدودیت‌های @@ROWCOUNT و راه‌حل‌ها**

`@@ROWCOUNT` به ازای هر دستور، مقداردهی مجدد می‌شود. این بدان معنی است که اگر بین دستور مورد نظر و استفاده از `@@ROWCOUNT`، دستور دیگری اجرا شود (مانند `PRINT`, `SET`, `IF` یا حتی یک `SELECT` که مقادیر ثابتی را برمی‌گرداند)، مقدار `@@ROWCOUNT` تغییر خواهد کرد. برای حفظ مقدار آن در یک بلاک کد، باید بلافاصله آن را در یک متغیر ذخیره کنید.

مثال:

DECLARE @RowCount INT;
SELECT * FROM Production.Product WHERE ProductID < 100;
-- بلافاصله @@ROWCOUNT را در یک متغیر ذخیره می‌کنیم
SET @RowCount = @@ROWCOUNT;
PRINT 'تعداد ردیف‌های بازیابی شده توسط SELECT: ' + CAST(@RowCount AS NVARCHAR(10));
-- در اینجا، @@ROWCOUNT به دلیل دستور PRINT به 1 تنظیم مجدد می‌شود.
SELECT @@ROWCOUNT AS RowCountAfterPrint; -- این مقدار 1 خواهد بود

همانطور که می‌بینید، بعد از دستور `PRINT`، مقدار `@@ROWCOUNT` به 1 تغییر می‌کند، اما مقدار اصلی در `@RowCount` ذخیره شده است.

**استفاده از عبارت OUTPUT به عنوان جایگزین**

برای عملیات DML، عبارت `OUTPUT` یک جایگزین قدرتمند و انعطاف‌پذیر برای `@@ROWCOUNT` ارائه می‌دهد، به خصوص زمانی که نیاز دارید جزئیات ردیف‌های تحت تأثیر را نیز ثبت کنید (نه فقط تعداد آن‌ها). عبارت `OUTPUT` به شما اجازه می‌دهد تا داده‌های درج شده، به‌روزرسانی شده یا حذف شده را مستقیماً از دستور DML بازیابی کنید.

مثال استفاده از `OUTPUT` برای درج:

CREATE TABLE dbo.TestRowCount (ID INT IDENTITY(1,1), Name NVARCHAR(50));
DECLARE @InsertedNames TABLE (ID INT, Name NVARCHAR(50));

INSERT INTO dbo.TestRowCount (Name)
OUTPUT inserted.ID, inserted.Name INTO @InsertedNames
VALUES ('محصول الف'), ('محصول ب'), ('محصول ج');

SELECT * FROM @InsertedNames;
SELECT @@ROWCOUNT AS CapturedRowsWithOutput;
DROP TABLE dbo.TestRowCount;

در این مثال، عبارت `OUTPUT` نه تنها به شما اجازه می‌دهد تا ردیف‌های درج شده را مشاهده کنید، بلکه `@@ROWCOUNT` نیز به درستی تعداد ردیف‌هایی را که از طریق `OUTPUT` به جدول متغیر هدایت شده‌اند، نمایش می‌دهد. استفاده از `OUTPUT` زمانی که نیاز به جزئیات بیشتری فراتر از صرفاً تعداد ردیف‌های تحت تأثیر دارید، توصیه می‌شود.