How To Get The Differences Of Two Databases With SQL Server?

How To Get The Differences Of Two Databases With SQL Server?

It’s important to know the distinctions between two databases in the realm of database administration. SQL Server provides strong tools and approaches to assist you accomplish this work, whether you’re moving data, doing data validation, or just making sure consistency. With code examples, we will examine how to use SQL Server to obtain the differences between two databases in this blog article.


Before we dive into the code examples, make sure you have the following in place:

  1. SQL Server Management Studio (SSMS) installed.
  2. Access to the two databases you want to compare.

Method 1: Using JOINs and UNIONs

We’ll start with a classic method using JOINs and UNIONs to compare two tables. This method helps identify new records and deleted records.

Step 1: Find New Records

SELECT * FROM Database2.dbo.YourTable
WHERE PrimaryKeyColumn NOT IN (SELECT PrimaryKeyColumn FROM Database1.dbo.YourTable)

Step 2: Find Deleted Records

SELECT * FROM Database1.dbo.YourTable
WHERE PrimaryKeyColumn NOT IN (SELECT PrimaryKeyColumn FROM Database2.dbo.YourTable)

Method 2: Using EXCEPT and INTERSECT

Another powerful way to compare two sets of data in SQL Server is by using the EXCEPT and INTERSECT operators. These operators help you find differences and commonalities between two result sets.

Step 1: Find New Records

SELECT * FROM Database2.dbo.YourTable
SELECT * FROM Database1.dbo.YourTable

Step 2: Find Deleted Records

SELECT * FROM Database1.dbo.YourTable
SELECT * FROM Database2.dbo.YourTable

Method 3: Using SQL Server Data Tools (SSDT)

SQL Server Data Tools (SSDT) is an integrated development environment that provides a database project for managing database schema and data. You can use it to perform schema and data comparisons between databases.

  1. Open SQL Server Data Tools.
  2. Create or open a database project.
  3. Right-click on the project, select “Schema Compare.”
  4. Configure the source and target databases.
  5. Click the “Compare” button to view differences.


For database administrators and developers, comparing two databases in SQL Server is a basic activity. The techniques described in this blog article provide many angles for locating newly added entries, records that have been removed, and other variations between databases. Select the approach that best satisfies your requirements and the degree of difficulty of your data comparison assignment.

Consider that these are only the fundamentals. SQL Server is a flexible tool for managing your database activities since it provides much more sophisticated tools for database comparison and synchronization.

Share this post

Comment (1)

  • Konstantin Semenenkov Reply


    Thank you for the article!

    Let me add my 2 cents..

    1) Method 1 SQL works only for single-column primary key. For composite key we need to use JOIN (like it is mentioned in the method title)
    2) Method 2 SQL has couple of issues:
    a) Usually we identify row by its primary key value, as it was done for the Method 1. EXCEPT compares data by all columns and if we have same ID in both tables with some changed value of non-ID column, then such a row is returned by EXCEPT but it should be counted as changed, not as new
    b) Both tables should have the same columns, in the same order and with compatible data types. Otherwise we may need to select explicit column list and cast/convert data type if necessary
    3) Both methods 1 and 2 SQL examples are applicable only for databases located on the same server, to make them work for cross-server scenarios we need to setup linked servers
    4) There is a little kind of inconsistency between these methods, methods 1 and 2 are about the DATA and method 3 is about the SCHEMA. To compare Data using SSDT, we need to choose Tools -> SQL Server -> New Data Comparison (for VS 2019 and 2022). What’s good is that SSDT data compare has no limitations I mentioned for methods 1 and 2.

    March 25, 2024 at 12:07 PM

Leave a Reply

Your email address will not be published. Required fields are marked *