مقایسه داده جداول SQL Server روش‌ها و کدها

مقایسه دقیق داده‌ها بین جداول SQL Server: روش‌های عملی و کدهای کاربردی

یکی از چالش‌های رایج در مدیریت پایگاه‌های داده، تشخیص تفاوت‌ها و مقایسه داده‌ها بین دو جدول SQL Server است. این نیاز ممکن است برای تأیید صحت داده‌ها پس از انتقال، شناسایی تغییرات، یا همگام‌سازی جداول به وجود آید. در این مقاله به بررسی چندین روش کارآمد برای مقایسه محتوای جداول خواهیم پرداخت.

آماده‌سازی جداول نمونه

برای نمایش روش‌های مختلف، ابتدا دو جدول نمونه با داده‌های مشابه اما با تفاوت‌هایی جزئی ایجاد می‌کنیم:

-- Create sample tables
CREATE TABLE tbl_Employee_A (EmployeeId INT, FirstName VARCHAR(50), LastName VARCHAR(50), Salary DECIMAL(10,2))
CREATE TABLE tbl_Employee_B (EmployeeId INT, FirstName VARCHAR(50), LastName VARCHAR(50), Salary DECIMAL(10,2))

-- Insert data into Table A
INSERT INTO tbl_Employee_A VALUES (1, 'John', 'Doe', 50000.00)
INSERT INTO tbl_Employee_A VALUES (2, 'Jane', 'Smith', 60000.00)
INSERT INTO tbl_Employee_A VALUES (3, 'Peter', 'Jones', 75000.00)
INSERT INTO tbl_Employee_A VALUES (4, 'Mary', 'Brown', 55000.00)

-- Insert data into Table B (with some differences)
INSERT INTO tbl_Employee_B VALUES (1, 'John', 'Doe', 50000.00)
INSERT INTO tbl_Employee_B VALUES (2, 'Jane', 'Smith', 65000.00) -- Different Salary
INSERT INTO tbl_Employee_B VALUES (3, 'Peter', 'Jones', 75000.00)
INSERT INTO tbl_Employee_B VALUES (5, 'David', 'Green', 80000.00) -- New Employee

روش اول: استفاده از EXCEPT (یا MINUS)

عبارت `EXCEPT` یکی از ساده‌ترین و کارآمدترین روش‌ها برای شناسایی ردیف‌هایی است که در یک جدول وجود دارند اما در جدول دیگر خیر. این عملگر، نتایج کوئری اول را برمی‌گرداند به شرطی که در نتایج کوئری دوم وجود نداشته باشند.

برای یافتن ردیف‌های موجود در `tbl_Employee_A` که در `tbl_Employee_B` نیستند:

-- Find rows in TableA that are not in TableB
SELECT *
FROM tbl_Employee_A
EXCEPT
SELECT *
FROM tbl_Employee_B;

برای یافتن ردیف‌های موجود در `tbl_Employee_B` که در `tbl_Employee_A` نیستند:

-- Find rows in TableB that are not in TableA
SELECT *
FROM tbl_Employee_B
EXCEPT
SELECT *
FROM tbl_Employee_A;

این روش به سرعت تفاوت‌های ردیف به ردیف را شناسایی می‌کند، اما برای شناسایی ردیف‌هایی که در هر دو جدول با `ID` یکسان وجود دارند ولی سایر مقادیرشان تفاوت دارد، نیاز به رویکردهای دیگری داریم.

روش دوم: استفاده از OUTER JOIN

استفاده از `FULL OUTER JOIN` به همراه شرط `WHERE` برای `IS NULL` در یکی از طرفین `JOIN`، روشی قدرتمند برای شناسایی ردیف‌هایی است که فقط در یکی از دو جدول حضور دارند و یا ردیف‌هایی که در هر دو جدول با مقادیر متفاوت وجود دارند (در صورت `JOIN` بر اساس کلید اصلی و مقایسه ستون‌های دیگر).

برای شناسایی ردیف‌هایی که فقط در یکی از دو جدول وجود دارند (ردیف‌های اضافه یا حذف شده):

SELECT
    ISNULL(A.EmployeeId, B.EmployeeId) AS EmployeeId,
    A.FirstName AS A_FirstName, B.FirstName AS B_FirstName,
    A.LastName AS A_LastName, B.LastName AS B_LastName,
    A.Salary AS A_Salary, B.Salary AS B_Salary
FROM tbl_Employee_A A
FULL OUTER JOIN tbl_Employee_B B
ON A.EmployeeId = B.EmployeeId
WHERE A.EmployeeId IS NULL OR B.EmployeeId IS NULL;

برای شناسایی ردیف‌هایی که بر اساس `EmployeeId` در هر دو جدول مشترک هستند اما در یک یا چند ستون دیگر تفاوت دارند:

SELECT
    A.EmployeeId,
    A.FirstName AS A_FirstName, B.FirstName AS B_FirstName,
    A.LastName AS A_LastName, B.LastName AS B_LastName,
    A.Salary AS A_Salary, B.Salary AS B_Salary
FROM tbl_Employee_A A
INNER JOIN tbl_Employee_B B
ON A.EmployeeId = B.EmployeeId
WHERE A.FirstName  B.FirstName OR
      A.LastName  B.LastName OR
      A.Salary  B.Salary;

توجه داشته باشید که در این روش، باید به مدیریت مقادیر `NULL` در ستون‌های مورد مقایسه دقت شود. برای مقادیر `NULL`، عملگر `_` (“) به درستی کار نمی‌کند و باید از `(A.Col IS NULL AND B.Col IS NOT NULL) OR (A.Col IS NOT NULL AND B.Col IS NULL) OR (A.Col B.Col)` استفاده کرد.

روش سوم: استفاده از CHECKSUM

تابع `CHECKSUM` یک مقدار هش (hash value) بر اساس مقادیر ستون‌های مشخص شده تولید می‌کند. اگر مقدار `CHECKSUM` برای دو ردیف یکسان باشد، احتمالاً محتوای آن ردیف‌ها نیز یکسان است. با این حال، `CHECKSUM` ممکن است در موارد نادر، برای داده‌های مختلف مقادیر یکسانی تولید کند (`collision`).

نحو (Syntax) تابع `CHECKSUM` به شرح زیر است:

CHECKSUM ( * | expression [ ,…n ] )

برای مقایسه ردیف‌های مشترک با استفاده از `CHECKSUM`:

SELECT
    A.EmployeeId,
    A.FirstName AS A_FirstName, B.FirstName AS B_FirstName,
    A.LastName AS A_LastName, B.LastName AS B_LastName,
    A.Salary AS A_Salary, B.Salary AS B_Salary
FROM tbl_Employee_A A
INNER JOIN tbl_Employee_B B
ON A.EmployeeId = B.EmployeeId
WHERE CHECKSUM(A.FirstName, A.LastName, A.Salary)  CHECKSUM(B.FirstName, B.LastName, B.Salary);

مهم است که ترتیب ستون‌ها در تابع `CHECKSUM` در هر دو طرف مقایسه یکسان باشد و همچنین در نظر داشته باشید که `CHECKSUM` مقادیر `NULL` را متفاوت با مقادیر غیر `NULL` صفر مدیریت می‌کند که می‌تواند منجر به نتایج نادرست شود.

روش چهارم: استفاده از HASHBYTES

تابع `HASHBYTES` یک الگوریتم هش رمزنگاری (مانند MD5, SHA1, SHA2_256, SHA2_512) را برای تولید مقدار هش با اطمینان بالا به کار می‌برد. این روش نسبت به `CHECKSUM` امنیت و دقت بالاتری در شناسایی تفاوت‌ها ارائه می‌دهد، زیرا احتمال `collision` (تولید هش یکسان برای ورودی‌های متفاوت) بسیار کمتر است.

نحو (Syntax) تابع `HASHBYTES` به شرح زیر است:

HASHBYTES ( ‘<algorithm>’, { @input | ‘input’ } )

برای مقایسه ردیف‌های مشترک با استفاده از `HASHBYTES`:

SELECT
    A.EmployeeId,
    A.FirstName AS A_FirstName, B.FirstName AS B_FirstName,
    A.LastName AS A_LastName, B.LastName AS B_LastName,
    A.Salary AS A_Salary, B.Salary AS B_Salary
FROM tbl_Employee_A A
INNER JOIN tbl_Employee_B B
ON A.EmployeeId = B.EmployeeId
WHERE HASHBYTES('SHA2_256', CONCAT(A.FirstName, A.LastName, A.Salary))  HASHBYTES('SHA2_256', CONCAT(B.FirstName, B.LastName, B.Salary));

هنگام استفاده از `HASHBYTES`، برای اطمینان از صحت مقایسه، باید تمامی ستون‌های مورد نظر را با استفاده از `CONCAT` (یا `+` برای رشته‌ها) به یک رشته واحد تبدیل کنید. همچنین، برای مدیریت مقادیر `NULL`، می‌توانید از `ISNULL` یا `COALESCE` قبل از `CONCAT` استفاده کنید تا `NULL`ها به یک مقدار پیش‌فرض تبدیل شوند. برای مثال: `CONCAT(ISNULL(A.FirstName, ”), ISNULL(A.LastName, ”), ISNULL(A.Salary, ”))`. این رویکرد اطمینان می‌دهد که `NULL`ها به طور سازگار در محاسبه هش لحاظ می‌شوند و از بروز خطاهای مقایسه‌ای جلوگیری می‌کند.