On Comparing Tables in SQL Server

How do you compare two SQL tables? Every SQL Developer or DBA knows the answer, which is 'it depends'. It is not just the size of the table or the type of data in it but what you want to achieve. Phil sets about to cover the basics and point out some snags and advantages to the various techniques.

Introduction

There are several reasons why you might need to compare tables or results.

  •  Sometimes, one just needs to know whether the tables contain data that is the same or different;  No details: just yes or no.  This is typical with test assertions, where you just need to know whether your routine or batch produces a result with the right data in it. when provided with particular vales for the parameters. It is either wrong or right
  • Occasionally, you need to know what rows have changed without, maybe, being particular about which columns changed and how.
  • There are times when you have a large table in terms of both columns and rows, and you need something that shows you specifically the column(s) that changed their value. You might also want this when tracking down a bug in a routine that might otherwise require you to wasting time scanning ‘by eye’.

We’ll be tackling these three rather different tasks in SQL

If two tables have a different number of rows, they can’t of course be the same. However, there are times when you need to know whether Table_B contains all the rows of Table_A, without differences. If  you wish more detail, you might even wish to know the rows in either table that aren’t in common, or the common rows, as indicated by the primary key,  that were different.  Why stick at comparing just two tables? There are ways of comparing as many as you need. (as, for example, when you’re comparing the metadata in several database snapshots). Yes, there are many variations

You’ve got tools and features to do this stuff, surely?

There is always a place for tools like SQL Data Compare,  TableDiff, tSQLt  or  Change Data Capture. A lot depends on circumstances and the type of task.  The problem of doing audits on changes to data in a live system  is a separate topic, as is the synchronization of tables and databases.  Comparison of XML documents are also out of scope. We are going to deal purely with the routine comparison of the data in tables

I’m most likely to use TSQL techniques to compare tables when:

Developing…

In the course of developing a database, a lot of tables get compared. It’s not just the big stuff: Every table-valued function, for example, needs a test harness in the build script that makes sure it does what you think it should do  under all conceivable test circumstances, and incorporating all the nasty edge cases where it has been caught by the testers in the past.  Every stored procedure needs a test to make sure that the process that it executes does exactly what is intended and nothing else.

There was a time that the build activity was rather leisurely, but when you’ve got a nightly build and integration test, it is best to automate it entirely and be rid of the chore.

ETL

When you are automating the loading of data into a system, you often need to test various conditions. Do you need to update existing versions of the rows as well as inserting the new ones? Do you need a trap to prevent duplicate entries, or even  delete  existing entries?

Setting up the test data.

The scripts in this article all use a table from the venerable PUBS database. We’re going to use the authors table, but will beef up the number of rows a bit to 5000 in order to get a size that is a bit more realistic. I’ve provided the source for the table with the article.

I then created a copy of the table …

And then altered some of the rows.

So now the two tables should be predominately the same with a few minor changes in the address field

Testing to see if tables are different.

Sometimes you just want to know if tables are the same. An example of this would be checking that a TVF is working properly by comparing its result to that of an existing table with the correct results. The usual way to do this is with the CHECKSUM()group of functions in SQL Server, because they are very quick.

Using Checksums

You can use the BINARY_CHECKSUM function to check whether tables are the same: well, roughly the same. It is fast, but it is not perfect, as I’ll demonstrate in a moment. If you have a series of tests, for example it is generally sufficient.

For this to work, your table must not have TEXT, NTEXT, IMAGE or CURSOR (or a SQL_VARIANT with any of these types) as its base type. Nowadays, this is increasingly rare, but If you have any sort of complication, you can coerce any column with one of the unsupported types into a supported type. In practice, I generally use a routine that checks the metadata and does this automatically, but it isn’t pretty.

In a working version you would probably want to specify the list of columns, especially if you are having to do an explicit coercion of datatypes, or if you are checking  just certain columns,

Neither BINARY_CHECKSUM() nor its plain sister CHECKSUM() are completely accurate in telling you if something has changed in a row or table. We’ll show this by looking at the common words of the English language, contained in a table called CommonWords.. You’d expect them all to have a different checksum, but that’s not the case.

… giving the result …

Armed with this information, we can quickly demonstrate that different strings can have the same checksum

All these will; have the same checksum, as would …

….whereas…

… gives you different checksums like this…

The sister function CHECKSUM()

… finds them to be all the same, because it is using the current collation and my collation for the database is case-insensitive. CHECKSUM() aims to find strings equal in checksum if they are equal in a string comparison.

So, the best you can say is that there is a strong likelihood that the tables will be the same but if you need to be absolutely certain, then use another algorithm.

If you don’t mind difference in case in text strings, then you can use CHECKSUM() instead of BINARY_CHECKSUM()

The great value of this technique is that, once you’ve calculated the checksum that you need, you can store it as a value in the column of  a table instead of needing the original table and therefore you can make the whole process even faster, and take less time. If you are storing the checksum value returned by CHECKSUM() make sure you  check against the live table with a checksum generated with the same collation.

Here is a simple example of a ‘what’s changed’ routine.

…which gives…

And then we just tidy up.

Of course, you could use a trigger but sometimes you might want just a daily or weekly report of changes without the intrusion of a trigger into a table.

Using XML

One general possibility is to compare the XML version of the two tables, since this does the datatype translation into strings for you. It is slower than the Checksum approach but more reliable.

Here, you can specify the type of comparison by specifying the collation.

or you can do this, comparing data in tables ..

… by calculating a checksum of the XML version of the table. This allows you to store the checksum of the table you are comparing to.

Finding where the differences are in a table

The simplest task is where the tables have an identical number of rows, and an identical table structure. Sometimes you want to know which rows are different, and which are missing.  You have, of course, to specify what you mean by ‘the same’, particularly if the two tables have different columns. The method you choose to do the comparison is generally determined by these details.

The UNION ALL … GROUP BY technique

The classic approach to comparing tables is to use a  UNION ALL for the SELECT statements that include the columns you want to compare, and then GROUP BY those columns. Obviously, for this to work, there must be a column with unique values in the GROUP BY, and the primary key is ideal for this. Neither table are allowed duplicates. If they have different numbers of rows, these will show up as differences.

If one of the tables has a duplicate, then it will give you a false result, as here, where you have two tables that are very different  and the result tells you that they are the same! For this reason, it is a good idea to include the column(s) that  constitute the primary key, and only include the rows  once!

… giving …

The technique can be used for comparing more than two tables.  You’d just need to UNION ALL the tables you need to compare and change the HAVING clause to filter just the rows that aren’t in all the tables.

Using EXCEPT

You can now use the much cleaner and slightly faster EXCEPT.

This shows all the rows in authors that are not found  in authorsCopy.  If they are the same, it would return no rows

I’m only using SELECT * to keep things simple for the article. You’d normally itemize all the columns you want to compare.

This will only work for tables with the same number of rows because, if authors had extra rows, it would still say that they were different since the rows in Authors that weren’t in authorsCopy would be returned. This is because EXCEPT returns any distinct values from the query to the left of the EXCEPT operand that are not also found from the query on the right

This, hopefully shows what I mean

…yields …

…whereas …

..results in …

This feature of EXCEPT could be used to advantage if you particularly wish to check that TableA is contained within TableB. So where the tables have a different number of rows you can still compare them.

You might not want to compare all columns.  You should always specify those columns you wish to compare to determine ‘sameness’. If you only wanted to compare the Address for example, you’d use …

The Outer Join technique

There is also the technique of the outer join.  This is a more general technique that give you additional facilities. If, for example, you use the full outer join then  you can get the unmatched rows in either table. This gives you a ‘before’ and ‘after’ view of alterations in the data.  It is used more generally in synchronisation to tell you what rows to delete, insert and update.

We’ll just use the technique to get the altered rows in authorsCopy

As you can see, there are difficulties with null columns with this approach, but it is as fast as the others and it gives you rather more versatility for your comparisons.

Locating the differences between tables

You may need a quick way of seeing what column and row has changed. A very ingenious way of doing this was published recently. It used XML. ‘Compare Tables And Report The Differences By Using Xml To Pivot The Data‘. It is clever, but too slow. The same thing can be done purely in SQL.  Basically, you perform a column by column comparison of data based on the primary key, using a key/value pair. If you do the entire table at once it is rather slow: The best trick is to do this only on those rows where you know there is a difference.

in our example, this would give:

This technique rotates the rows of the tables that have differences into an Entity-attribute-value (EAV) table so that differences within a row can be compared and displayed.  It does this rotation by UNIONing the name and string-value of each column.  This technique works best where there are not a large number of differences.

Conclusions

There is no single ideal method of comparing the data in tables or results. One of a number of techniques will be the most relevant  for any particular task. It is all down to precisely the answers you need and the type of task. Do you need a quick check that a table hasn’t changed, or do you need to know precisely what the changes are?  SQL is naturally fast at doing this task and comparisons of tables and results is a familiar task to many database developers.

If there is a general rule, I’d say that  exploratory or ad-hoc work  needs a tool such as SQL Data Compare, whereas  a routine process within the database  requires a hand-cut SQL technique.

The source to the table, and the insert-statements to fill it to 5000 rows is in the link below.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.

Downloads

Tags: , , ,

  • 92851 views

  • Rate
    [Total: 50    Average: 4.5/5]
  • Anonymous

    Using intersection and cardinality
    If the tables are true sets (i.e. not multisets or bags. uniqueness enforced by PK or unique index), they are equal if |A| = |B| = |A ∩ B|.

    That is:

    IF (SELECT COUNT(*) FROM A) = (SELECT COUNT(*) FROM B)
    AND (SELECT COUNT(*) FROM (SELECT * FROM A INTERSECT SELECT * FROM B)

    THEN … — tables are equal

  • Anonymous

    oops — hit enter to soon!
    F (SELECT COUNT(*) FROM A) = (SELECT COUNT(*) FROM B)
    AND (SELECT COUNT(*) FROM (SELECT * FROM A INTERSECT SELECT * FROM B) = (SELECT COUNT(*) FROM A)

    THEN …

    • Phil Factor

      Subject: Using intersection and cardinality

  • Anonymous

    Comparing tables
    I have used a different technique for years that seems to perform ok and is easy to code, customize and extend. I have tables in development, acceptance and production. I want to show the rows where anything is different between the environments. In this case I want to check constraints across databases:

    print ‘– 1. Show differences in information_schema.CHECK_CONSTRAINTS across databases’

    In my database this results in a case insensitive matching. The internal select statements can be subsets of columns or * for all. The list of columns must be in the top most select and group by. It has the advantage of comparing NULLs as the same.

  • Steve Long

    The Outer Join technique
    As Phil said, a problem with the Outer Join technique is dealing with null values. The query shown will treat null values as identical to empty strings. If you want to preserve null values, then the query needs to be more complicated, with two tests for each field that can have a null value:

    SELECT authors.au_id, authors.au_lname, authors.au_fname, authors.phone, authors.address, authors.city, authors.state, authors.zip, authors.contract
    FROM authors
    LEFT OUTER JOIN authorsCopy
    ON authors.au_ID = AuthorsCopy.au_ID
    AND authors.au_lname =authorsCopy.au_lname
    AND authors.au_fname =authorsCopy.au_fname
    AND authors.phone =authorsCopy.phone
    AND COALESCE(authors.address,’a’)=COALESCE(authorsCopy.address,’a’)
    AND COALESCE(authors.address,’b’)=COALESCE(authorsCopy.address,’b’)
    AND COALESCE(authors.city,’a’) =COALESCE(authorsCopy.city,’a’)
    AND COALESCE(authors.city,’b’) =COALESCE(authorsCopy.city,’b’)
    AND COALESCE(authors.state,’a’) =COALESCE(authorsCopy.state,’a’)
    AND COALESCE(authors.state,’b’) =COALESCE(authorsCopy.state,’b’)
    AND COALESCE(authors.zip,’a’) =COALESCE(authorsCopy.zip,’a’)
    AND COALESCE(authors.zip,’b’) =COALESCE(authorsCopy.zip,’b’)
    AND authors.contract =authorsCopy.contract
    WHERE authorsCopy.au_ID IS NULL

  • Anonymous

    Thanks!
    Just used this information today to compare tables in replicated databases on two servers to find mismatched rows.

  • Mister Magoo

    Using XML without ORDER BY
    First let me say that this is a nice overview of the various techniques, thanks.

    As far as using XML to compare, are you sure that method is reliable without an ORDER BY clause ?

    I cannot see any SORT operations in an execution plan for SELECT…FOR XML PATH() without an ORDER BY.

    Of course, it may use the clustered index on a single thread but that is not guaranteed is it?

    Even if it were guaranteed, there is no guarantee that the two tables would have the same CI, so I would definitely include an ORDER BY in that XML example.

    ED: The article has been updated to correct this. Many thanks for the spot!

    • Phil Factor

      Re: Using XML without ORDER BY
      Oops. Yes, it would only have worked if they’d had the same clustered index, and then, as you say, not ultra-reliably. The editor has now fixed the article. Apologies!

  • manasa

    Comparing tables from different databses
    Hello,
    I have a requirement to compare tables from 2 different databases and i have to use sql server scripts (ot stored procedures).. I SHOULD NOT USE ANY THIRD PARTY TOOL. Kindly help me to do schema level comparision like column name, column size, column datatype of two same tables from different databases. I wanna compare all columns at a time between two tables….