Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Ways to compare and find differences for SQL Server tables and data

MSSQLTips author Daniel Calbimonte By:   |   Read Comments (16)   |   Related Tips: More > Comparison - Data and Objects
Problem

Sometimes we need to compare tables and/or data to know what was changed. This tip shows you different ways to compare data, datatypes and tables.

Solution

I will show you different methods to identify changes. Let's say that we have two similar tables in different databases and we want to know what is different:

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'),
('002','2','80'),
('003','3','120')
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'),
('002','2','80'),
('003','3','120'),
('004','4','160')
GO

The T-SQL code generates 2 tables in different databases. The table names are the same, but the table in database dbtest02 contains an extra row:

The tables

Let's look at ways we can compare these tables using different methods.

Compare Tables Using a LEFT JOIN

With the left join we can compare values of specific columns that are not common between two tables.

For example:

select * 
from dbtest02.dbo.article d2
left join dbtest01.dbo.article d1 on d2.id=d1.id

The left join shows all rows from the left table "dbtest02.dbo.article", even if there are no matches in the "dbtest01.dbo.article":

Left join

In this example, we are comparing 2 tables and the values of NULL are displayed if there are no matching rows. This method works to verify new rows, but if we update other columns, the left join does not help. Is there another method to compare tables?  Let's use the Except clause to see what we can find.

Compare Tables Using the EXCEPT Clause

The Except method shows the difference between two tables (the Oracle guys use minus instead of except and the syntax and use is the same). It is used to compare the differences between two tables. For example, let's see the differences between the two tables:

Now let's run a query using except:

select * from dbtest02.dbo.article
except
select * from dbtest01.dbo.article

The except returns the difference between the tables from dbtest02 and dbtest01:

Except

If we flip the tables around in the query we won't see any records, because the table in database dbtest02 has all of the records plus one extra.

This method is better than the first one, because if we change values for other columns like the type and cost, the except will notice the difference. Here is an example if we update id "001" in database dbtest01 and change the cost from "40" to "1".  If we update the records and then run the query again we will see these differentness now:

except clause differences

Unfortunately it does not create a script to synchronize the tables. Is there a way to compare tables and synchronize results?

Compare Tables Using the Tablediff Tool

There is a nice command line tool used to compare tables.  This can be found in "C:\Program Files\Microsoft SQL Server\110\COM\" folder. This command line tool is used to compare tables. It also generates a script with the insert, update and delete statements to synchronize the tables. For more details, refer to this tablediff article.

Compare Changes in a Table Using the Change Data Capture CDC

This feature was added in SQL Server 2008. You need to enable this feature and you also need to have SQL Server Agent running. Basically it creates system tables that track the changes in your tables that you want to monitor. It does not compare tables, but it tracks the changes in tables.

For more information, refer to this article: Using Change Data Capture (CDC) in SQL Server 2008.

Compare Data Types Between Two Tables

What happen if we want to compare the data types? Is there a way to compare the datatypes?

Yes, we can use the [INFORMATION_SCHEMA].[COLUMNS] system views to verify and compare the information. We are going to create a new table named dbo.article2 with a column with different data type than the dbo.article table:

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

The difference is that the id is now an int instead of nchar(10) like our other tables.

The query to compare data types of the article and article 1 would be:

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
  

The results are as follows:

Except

The query compares the data types from these two tables. All the information of the columns can be obtained from the [INFORMATION_SCHEMA].[COLUMNS] system view. We are comparing the table "article" with the table "article2" and showing if any of the datatypes are different.

Compare if there are Extra Columns Between Tables

Sometimes we need to make sure that two tables contain the same number of columns. To illustrate this we are going to create a table named "article3" with 2 extra columns named extra1 and extra2:

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

In order to compare the columns I will use this query:

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')

The query compares the different columns between table "article" and "article3". The different columns are extra1 and extra2. This is the result of the query:

compare extra columns

Compare Tables in Different Databases

Now let's compare the tables in database dbtest01 and dbtest02. The following query will show the different tables in dbtest01 compared with 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  
    )
compare tables between databases

Third Party Tools

There are great tools to compare tables including data and schemas. You can use Visual Studio or use other SQL Server Comparison tools.

Next Steps


Last Update: 10/22/2012


About the author
MSSQLTips author Daniel Calbimonte
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Monday, October 22, 2012 - 8:23:56 AM - Martin Borgars Read The Tip

Why not just use Red Gate's SQL Data Compare tool (http://www.red-gate.com/products/sql-development/sql-data-compare/)?


Monday, October 22, 2012 - 10:14:23 AM - Boris Tyukin Read The Tip

Thanks for the interesting article!

Red Gate's SQL Data Compare tool is nice indeed and we use it at work but it is not free so it is great to see alternatives.

Some time ago I bookmarked very fast and cool method, described by Lee Everest here

The idea is to use query like that:

SELECT MIN(id) AS id, col1, col2, col3 FROM (SELECT id, col1, col2, col3 FROM tbl1 UNION ALL SELECT id, col1, col2, col3 FROM tbl2) X GROUP BY col1, col2, col3 HAVING COUNT(1)=1

 

 

 


Monday, October 22, 2012 - 2:02:42 PM - TimothyAWiseman Read The Tip

Great article, thank you for providing it.

 

I somewhat agree wtih Marting Borgars.  I am a big fan of Red Gate's SQL Data Compare and Red Gate's SQL Compare when looking at schemas, I have been extremely happy with them.  

But there are times when those won't be available.  As Boris points out, they aren't free (well worth the price though), so you may not always have them, especially when working for a small client on a small project.  Some institutions also have very lengthy validation procedures for installing software due to security concerns. 

It is also good to be able to do it "by hand" just for the sake of having a thorough knowledge base and understanding of how things work, even if you will use the tools most of the time. 


Monday, October 22, 2012 - 3:56:53 PM - John Fox Read The Tip
A simple method that I use that works quite well and is free is to create a SQL query that looks like the following. If one is familiar with tools that use regular expressions, it can be done quite quickly by starting with the output of 'script table as select' using SMS. Assuming the keys are the primary keys, it is only necessary to check one of them for each table to null to detect a 'missing row' condition. And it is amazing what data types compare quite well using '-1' as the default if null. Of course, one has to be smart and if '-1' is a valid data condition then either some other value of checking specifically for 'is null' should be used. After running the SQL, I usually load it into excel, cut all of the data from 'Test' to the last column and last row, then paste it after the 'prod' data. A quick sort by key1/key2 then makes it pretty easy to walk through the data.


select 'Prod',t1.key1, t1.key2, t1.field3, t1.fiel4, 'Test',t2.key1, t2.key2, t2.field3, t2.fiel4
from ProductionTable t1
full join TestingTable t2 on t1.key1 = t2.key1 and t2.key2 = t2.key2
where t1.key1 is null
or t2.key1 is null
or isnull(t1.field3,-1) != isnull(t2.field3,-1)
or isnull(t1.field4,-1) != isnull(t2.field4,-1)

Tuesday, October 23, 2012 - 9:04:26 AM - Daniel Calbimonte Read The Tip

Yes,

There is already a pretty nice article related to third party tools to compare data

http://www.mssqltips.com/products.asp?catid=25

Personally I love SQL Data Compare from Redgate and SQL Data Compare from devart.

My original article included some demos about 3 third party tools, but the editors decided not to promote third party tools.


Tuesday, October 23, 2012 - 10:58:06 AM - David McKinney Read The Tip

@Boris I couldn't get your link to work (for Lee Everest's article).

I have a method using UNION as well but not UNION ALL

It relies on the fact that

select 'alpha' UNION select 'alpha' will only return 1 row (and not 2).  Hence if you UNION 2 tables with 1000 rows each together, if they're identical, the result will have 1000 rows also.  You can then look for duplicates on the PKey field(s) to identify those rows which are different.


Tuesday, October 23, 2012 - 11:27:52 AM - Boris Tyukin Read The Tip

@David McKinney

sorry, looks his site is down...but when I tested this query below, it was the fastest:

 

SELECT MIN(id) AS id, col1, col2, col3 FROM (SELECT id, col1, col2, col3 FROM tbl1 UNION ALL SELECT id, col1, col2, col3 FROM tbl2) X GROUP BY col1, col2, col3 HAVING COUNT(1)=1

It also works well with NULLs. He does Union ALL, but HAVING COUNT(1)=1 will output only records not found in one of the tables, so matches will be suppressed


Sunday, March 17, 2013 - 3:54:11 PM - Ari Mäenpää Read The Tip

Excellent page, I have found this already twice when needed this! Maybe I should now bookmark this.

Thank you!


Tuesday, March 26, 2013 - 7:35:23 AM - Satish Read The Tip

Thank you Daniel for your good article. 


Thursday, May 23, 2013 - 6:13:57 AM - SergeiK Read The Tip

Why not just use Red Gate's SQL Data Compare tool (http://www.red-gate.com/products/sql-development/sql-data-compare/)?

Answer: May be because Red Gate's Tool costs about 500 $ ?

I choosed Devart's products for my needs which are much cheaper and the same proffesional.

For this need you can use SQL Data Compare from Devart (http://www.devart.com/dbforge/sql/datacompare/)


Thursday, August 29, 2013 - 2:50:55 AM - Geoffrey Read The Tip

I recomment SQL Negotiator Pro from Aphilen software www.aphilen.com they offer a free version which may suffice your needs

The full blown version comes with the ability to

Compare unlimited databases, generate management reports, graphically display database dependencies, SQL server monitor etc etc


Wednesday, November 06, 2013 - 5:06:40 PM - Salvador Nava Read The Tip

Thanks a lot!!! Very useful!!!


Tuesday, June 10, 2014 - 6:46:58 PM - Arun kumar (SQL Developer) Read The Tip

I liked this post, but this will not full fill for the dependent table. I have faced an issue when accidently 1 master table updated in source table that has updated multiple table on multiple server, in some table it uses foreign key relation ship. Now we have to identify all the changes related to this one. This changes also replicated to diffrent server. We were thinking can we do with this approach but did not found solution on the spot. We have worked 1 by one table then we sync the table.

If you developer think it can also generate script for dependent table please share. Thanks a lot !!!!

 

 


Thursday, July 10, 2014 - 4:45:38 AM - Nitin Sharma Read The Tip

why do we use procedure


Tuesday, July 29, 2014 - 8:05:10 AM - Jayesh Read The Tip

 

Helllo please read this instead of above,

i want to compare two tables from different database, suppose

both tables have 5 raw

in which

table 1 and table 2 has 4 raw exactly same,

but fifth raw has minor change in one column value

 

if i want to to see only that value not entire raw then

what to do?

 

red gate can't give specic difffrent column value


Tuesday, July 29, 2014 - 10:38:59 AM - Greg Robidoux Read The Tip

Hi Jayesh, this can be done but it takes a lot of coding to figure out which rows are different and then which columns are different.  You could use the EXCEPT method shown above to show records that are different, but then you will need to take the results from that and write a process to compare column by column.  I don't think we have a tip that does this, so maybe someone could write a tip.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.