راه‌هایی برای مقایسه و یافتن تفاوت‌ها برای جداول و داده‌های SQL Server

نوشته شده توسط: تیم فنی نیک آموز

تاریخ انتشار: ۰۱ اسفند ۱۴۰۰

آخرین بروزرسانی: ۲۴ آبان ۱۴۰۲

زمان مطالعه: 18 دقیقه

مسئله

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

راه‌حل

من روش‌های مختلفی را برای شناسایی تغییرات با استفاده از کوئری‌های مختلف SQL و همچنین چند ابزار توسعه، پوشش خواهم داد.

فرض کنید دو جدول مشابه در پایگاه داده‌های مختلف داریم و می‌خواهیم بدانیم چه چیزی متفاوت است. در اینجا اسکریپتی وجود دارد که پایگاه داده‌ها، جداول و داده‌های نمونه را برای این مقاله، ایجاد می‌کند.

CREATE DATABASE dbtest01
GO
USE dbtest01
GO
CREATE TABLE [dbo].[article] ([id] [nchar](10) NOT NULL, [type] [nchar](10) NULL, [cost] [nchar](10) NULL,
CONSTRAINT [PK_article] PRIMARY KEY CLUSTERED
(
[id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[article]
VALUES ('001', '1', '40'),
('۰۰۲', '۲', '۸۰'),
('۰۰۳', '۳', '۱۲۰')
GO
CREATE DATABASE dbtest02
GO
USE dbtest02
GO
CREATE TABLE [dbo].[article] ([id] [nchar](10) NOT NULL, [type] [nchar](10) NULL, [cost] [nchar](10) NULL,
CONSTRAINT [PK_article] PRIMARY KEY CLUSTERED
(
[id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[article]
VALUES ('001', '1', '40'),
('۰۰۲', '۲', '۸۰'),
('۰۰۳', '۳', '۱۲۰'),
('۰۰۴', '۴', '۱۶۰')
GO

کد T-SQL دو جدول در پایگاه داده‌های مختلف تولید می‌کند. نام جداول یکسان است، اما جدول در پایگاه داده dbtest02 شامل یک ردیف اضافی است که در زیر نشان داده شده است:

روش‌هایی را بررسی می‌کنیم که می‌توانیم این جداول را با استفاده از روش‌های مختلف مقایسه کنیم.

مقایسه داده‌های SQL Server در جداول با استفاده از LEFT Join

با یک LEFT JOIN می‌توانیم مقادیر ستون‌های خاصی را که بین دو جدول مشترک نیستند مقایسه کنیم.به عنوان مثال، به این عبارت SELECT دقت کنید:

SELECT *
FROM dbtest02.dbo.article d2
LEFT JOIN dbtest01.dbo.article d1 ON d2.id = d1.id

مجموعه نتایج LEFT JOIN همه ردیف‌های جدول سمت چپ “dbtest02.dbo.article” را نشان می‌دهد، حتی اگر هیچ پیوندی با ردیفی در جدول “dbtest01.dbo.article” وجود نداشته باشد:در این مثال، ما دو جدول را با هم مقایسه می‌کنیم و اگر ردیف‌های منطبقی وجود نداشته باشد، NULL نمایش داده می‌شود. این روش برای تأیید ردیف‌های جدید کار می‌کند، اما اگر ستون‌های دیگر را به روز کنیم، LEFT JOIN کمکی نمی‌کند.

این روش را می‌توان با جا به جایی جداول نیز انجام داد:

SELECT *
FROM dbtest01.dbo.article d1
LEFT JOIN dbtest02.dbo.article d2 ON d1.id = d2.id

مقایسه داده‌های SQL Server در جداول با استفاده از دستور EXCEPT

Except تفاوت بین دو جدول را نشان می‌دهد (توسعه‌دهنده‌های Oracle DBMS از minus به جای Except استفاده می‌کنند. سینتکس و استفاده یکسان است). برای مقایسه تفاوت بین دو جدول استفاده می‌شود. به عنوان مثال، تفاوت بین دو جدول را در کوئری زیر با Expect نشان می‌دهیم:

SELECT * FROM dbtest02.dbo.article
EXCEPT
SELECT * FROM

Expect تفاوت بین جداول dbtest02 و dbtest01 را برمی‌گرداند:اگر جداول را در کوئری جا به جا کنیم، هیچ رکوردی نخواهیم دید، زیرا جدول در پایگاه داده dbtest02 همه رکوردها به اضافه یک رکورد اضافی را دارد. در نتیجه هیچ اختلافی را نشان نمی‌دهد.

به عنوان مثال اگر id = “۰۰۱” را در پایگاه داده dbtest01 به روز کنیم و cost را از “۴۰” به “۱” تغییر دهیم. اگر رکوردها را به روز کنیم و دوباره کوئری را اجرا کنیم، اکنون این تفاوت‌ها را خواهیم دید:

مقایسه داده‌های SQL Server در جداول با استفاده از ابزار Tablediff

Tablediff یک ابزار خط فرمان رایگان است که برای مقایسه جداول استفاده می‌شود. این ابزار را می‌توان در فولدر “C:\Program Files\Microsoft SQL Server\110\COM\” پیدا کرد. این ابزار خط فرمان برای مقایسه جداول استفاده می‌شود. همچنین یک اسکریپت با عبارات INSERT، UPDATE و DELETE برای همگام‌سازی جداول ایجاد می‌کند.

در اینجا یک دستور نمونه آورده شده است که دو جدول Table1 و Table2 را در یک پایگاه داده مقایسه می‌کند.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver server1
-sourcedatabase test -sourcetable table1 -destinationserver server1 -destinationdatabase test
-destinationtable table2

هنگامی که دستور بالا اجرا می شود، خروجی به صورت زیر است:

Microsoft (R) SQL Server Replication Diff Tool
Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
User-specified agent parameter values:
sourceserver server1
-sourcedatabase test
-sourcetable table1
-destinationserver server2
-destinationdatabase test
-destinationtable table2
Table [test].[dbo].[table1] on server1 and Table [test].[dbo].[table2]
on server1 have 3 differences.
Err PersonID
Mismatch 1
Dest. Only 2
Src. Only 3
The requested operation took 0.4375 seconds.

از این دستور اصلی می‌توانیم تفاوت‌هایی را ببینیم، اما چندان مفید نیست، بنابراین برای مفیدتر کردن این روال می‌توانیم از آرگومان «et-» برای دیدن تفاوت‌ها در جدول استفاده کنیم. پارامتر «et-» یک جدول ایجاد می‌کند که این جدول را Difference می‌نامیم، بنابراین می توانیم تفاوت‌ها را در جدول مشاهده کنیم.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver server1
-sourcedatabase test -sourcetable table1 -destinationserver server1 -destinationdatabase test
-destinationtable table2
-et Difference

گزینه دیگر استفاده از آرگومان «f-» است که یک اسکریپت T-SQL برای همگام سازی دو جدول ایجاد می کند.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver server1
-sourcedatabase test -sourcetable table1 -destinationserver server1 -destinationdatabase test
-destinationtable table2
-et Difference
-f c:\table1_differences.sql

خروجی زیر در فایل “c:\table1_differences.sql” ایجاد می شود.

-- Host: server1
-- Database: [test]
-- Table: [dbo].[table2]
UPDATE [dbo].[table2] SET [LastName]=NULL WHERE [PersonID] = 1
DELETE FROM [dbo].[table2] WHERE [PersonID] = 2
INSERT INTO [dbo].[table2] ([FirstName],[LastName],[PersonID])
VALUES ('Bob','Jones',3)

از اینجا می‌توانیم تفاوت‌های دقیق را ببینیم و همچنین اسکریپتی داریم که می‌توانیم آن را در مقابل Table2 اجرا کنیم تا محتویات آن با Table1 یکسان شود.

مقایسه داده‌های SQL Server در جداول با استفاده از CDC

این ویژگی در SQL Server 2008 به بعد موجود است. شما باید این ویژگی را فعال کنید و همچنین باید SQL Server Agent در حال اجرا باشد. به طور کلی جداول سیستمی را ایجاد می‌کند که تغییرات جداولی را که می‌خواهید نظارت کنید را دنبال می‌کند. جداول را با هم مقایسه نمی‌کند، اما تغییرات جداول را دنبال می‌کند.

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

اگر بخواهیم انواع داده‌ها را با هم مقایسه کنیم چه اتفاقی می‌افتد؟ آیا راهی برای مقایسه انواع داده‌ها وجود دارد؟

بله، می‌توانیم از نمای سیستم [INFORMATION_SCHEMA].[COLUMNS] برای تأیید و مقایسه اطلاعات استفاده کنیم. ما یک جدول جدید به نام dbo.article2 با ستونی با نوع داده متفاوت از جدول dbo.article ایجاد می‌کنیم:

USE dbtest01
GO
CREATE TABLE [dbo].[article2](
[id] [int] NOT NULL,
[type] nchar(10) NULL,
[cost] nchar(10) NULL,
CONSTRAINT [PK_article1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

تفاوت این است که id اکنون به جای nchar(10) مانند سایر جداول یک int است. کوئری برای مقایسه انواع داده جداول dbo.article و dbo.article2 به صورت زیر خواهد بود:

USE dbtest01
GO
SELECT
c1.table_name,
c1.COLUMN_NAME,
c1.DATA_TYPE,
c2.table_name,
c2.DATA_TYPE,
c2.COLUMN_NAME
FROM [INFORMATION_SCHEMA].[COLUMNS] c1
LEFT JOIN [INFORMATION_SCHEMA].[COLUMNS] c2 ON c1.COLUMN_NAME = c2.COLUMN_NAME
WHERE c1.TABLE_NAME = 'article'
AND c2.TABLE_NAME = 'article2'
AND c1.data_type <> c2.DATA_TYPE

نتایج به شرح زیر است:کوئری انواع داده‌ها را میان این دو جدول مقایسه می‌کند. تمام اطلاعات ستون‌ها را می‌توان از نمای سیستم [INFORMATION_SCHEMA].[COLUMNS] بدست آورد. ما جدول “article” را با جدول “article2” مقایسه کردیم و نشان دادیم که آیا هر یک از انواع داده‌ها متفاوت است یا خیر.

مقایسه وجود ستون‌های اضافی بین جداول پایگاه داده SQL Server

گاهی اوقات باید مطمئن شویم که دو جدول دارای تعداد یکسان ستون هستند. برای نشان دادن این موضوع، می‌خواهیم جدولی به نام «article3» با دو ستون اضافی به نام‌های extra1 و extra2 ایجاد کنیم:

USE dbtest01
GO
CREATE TABLE [dbo].[article3](
[id] [int] NOT NULL,
[type] nchar(10) NULL,
[cost] nchar(10) NULL,
extra1 int,
extra2 int
)

برای مقایسه ستون‌ها از این کوئری استفاده می‌کنم:

USE dbtest01
GO
SELECT
c2.table_name,
c2.COLUMN_NAME
FROM [INFORMATION_SCHEMA].[COLUMNS] c2
WHERE table_name = 'article3'
AND c2.COLUMN_NAME NOT IN
( SELECT column_name
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE table_name = 'article'
)

کوئری بالا ستون‌های متفاوت در جداول ” “article و “article3” را مقایسه می‌کند. ستون‌های مختلف به نام‌های extra1 و extra2 هستند. این نتیجه کوئری است:

مقایسه جداول SQL Server در پایگاه داده‌های مختلف

حالا بیایید جداول پایگاه داده dbtest01 و dbtest02 را با منطق UNION و یک زیرکوئری با منطق NOT IN مقایسه کنیم. کوئری زیر جداول مختلف در پایگاه داده dbtest01 را در مقایسه با dbtest02 نشان می‌دهد:

SELECT 'dbtest01' AS dbname, t1.table_name
FROM dbtest01.[INFORMATION_SCHEMA].[tables] t1
WHERE table_name NOT IN
( SELECT t2.table_name
FROM dbtest02.[INFORMATION_SCHEMA].[tables] t2
)
UNION
SELECT 'dbtest02' AS dbname, t1.table_name
FROM dbtest02.[INFORMATION_SCHEMA].[tables] t1
WHERE table_name NOT IN
( SELECT t2.table_name
FROM dbtest01.[INFORMATION_SCHEMA].[tables] t2
)

مقایسه شِماها با استفاده از SSDT

SQL Server Data Tools اجازه می‌دهد تا شِماهای دو جدول مختلف را با استفاده از SQL Server Database Project مقایسه کنید. می‌تواند اسکریپت‌هایی تولید کند که به شما امکان می‌دهد داده‌ها را با چند کلیک همگام‌سازی کنید. این موضوع را بررسی می‌کنیم:
1) در پروژه دیتابیس به Solution Explorer رفته و روی پایگاه داده کلیک راست کرده و گزینه Schema Compare را انتخاب کنید تا جداول را با هم مقایسه کنید:۲) در Select Target Schema، دکمه Select Connection را کلیک کنید تا جدول مقصد با جدول موجود در منبع مقایسه شود. شما می‌توانید یک اتصال موجود را انتخاب کنید یا یک اتصال جدیدی را ایجاد کنید:۳) سپس، دکمه compare را کلیک کنید تا تفاوت را نشان دهد. جداول برای افزودن یا جداول برای حذف را نشان می‌دهد:۴) این ابزار کل اسکریپت T-SQL را به شما نشان می‌دهد که می‌توانید مطابق با نیازهای خود تغییرات را روی جداول اعمال کنید:

گزینه SSIS Lookup

این گزینه در ETLها واقعاً محبوب است. Lookup یک تسک تبدیل SSIS است که امکان Lookup داده‌ها با استفاده از اتصالات با استفاده از مجموعه داده را می‌دهد. Lookup به شما امکان می‌دهد داده‌های تغییر یافته را میان دو جدول تشخیص دهید. در مثال زیر جداول را با هم مقایسه می‌کنیم:
1) در پروژه SSIS، روی Data Flow Task دابل کلیک کنید تا آن را در Control Flow ایجاد کنید تا یک دنباله Data Flow ایجاد شود:۲) در Data Flow، منبع OLE DB را با جدول منبع بکشید و رها کنید تا تسک Lookup و دو تسک OLE DB Destination را با هم مقایسه کنید:۳) در Lookup می‌توانید اطلاعات را به طور کامل در حافظه کش ذخیره کنید. ما همچنین گزینه no cache را داریم. گزینه حالت full cache داده‌ها را در کش SSIS ذخیره می‌کند. partial cache فقط در cache values ذخیره می‌شود زیرا هر مقدار متمایز در جریان داده یافت می‌شود. هر مقدار متمایز از منبع خاص به دست می‌آید:۴) این تسک به شما امکان می‌دهد کلیدهای مقایسه و ستون‌های جستجو را با نام مستعار در خروجی تعریف کنید:همان طور که می‌بینید، Lookup امکان جستجو و مقایسه داده‌ها را فراهم می‌کند. سپس می‌توانید دریافت کنید که چه داده‌هایی بین دو جدول تغییر کرده است.