{"id":1814,"date":"2014-05-29T00:00:00","date_gmt":"2014-05-29T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/on-comparing-tables-in-sql-server\/"},"modified":"2021-09-29T16:21:37","modified_gmt":"2021-09-29T16:21:37","slug":"on-comparing-tables-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/on-comparing-tables-in-sql-server\/","title":{"rendered":"On Comparing Tables in SQL Server"},"content":{"rendered":"<div id=\"pretty\">\n<h2>Introduction<\/h2>\n<p>There are several reasons why you might need to compare tables or results.<\/p>\n<ul>\n<li>\u00a0Sometimes, one just needs to know whether the tables contain data that is the same or different; \u00a0No details: just yes or no.\u00a0 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<\/li>\n<li>Occasionally, you need to know what rows have changed without, maybe, being particular about which columns changed and how.<\/li>\n<li>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 &#8216;by eye&#8217;.<\/li>\n<\/ul>\n<p>We&#8217;ll be tackling these three rather different tasks in SQL<\/p>\n<p>If two tables have a different number of rows, they can&#8217;t of course be the same. However, there are times when you need to know whether <b>Table_B<\/b> contains all the rows of <b>Table_A<\/b>, without differences. If \u00a0you wish more detail, you might even wish to know the rows in either table that aren&#8217;t in common, or the common rows, as indicated by the primary key, \u00a0that were different. \u00a0Why stick at comparing just two tables? There are ways of comparing as many as you need. (as, for example, when you&#8217;re comparing the metadata in several database snapshots). Yes, there are many variations<\/p>\n<h2>You&#8217;ve got tools and features to do this stuff, surely?<\/h2>\n<p>There is always a place for tools like SQL Data Compare, \u00a0TableDiff, tSQLt \u00a0or\u00a0 Change Data Capture. A lot depends on circumstances and the type of task.\u00a0 The problem of doing audits on changes to data in a live system\u00a0 is a separate topic, as is the synchronization of tables and databases. \u00a0Comparison of XML documents are also out of scope. We are going to deal purely with the routine comparison of the data in tables<\/p>\n<p>I&#8217;m most likely to use TSQL techniques to compare tables when:<\/p>\n<h3>Developing&#8230;<\/h3>\n<p>In the course of developing a database, a lot of tables get compared. It&#8217;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 \u00a0under all conceivable test circumstances, and incorporating all the nasty edge cases where it has been caught by the testers in the past.\u00a0 Every stored procedure needs a test to make sure that the process that it executes does exactly what is intended and nothing else.<\/p>\n<p>There was a time that the build activity was rather leisurely, but when you&#8217;ve got a nightly build and integration test, it is best to automate it entirely and be rid of the chore.<\/p>\n<h3>ETL<\/h3>\n<p>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\u00a0 delete \u00a0existing entries?<\/p>\n<h1>Setting up the test data.<\/h1>\n<p>The scripts in this article all use a table from the venerable PUBS database. We&#8217;re going to use the <b>authors<\/b> 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&#8217;ve provided the source for the table with the article.<\/p>\n<p>I then created a copy of the table &#8230;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0*\u00a0INTO\u00a0authorsCopy\u00a0\r\n\u00a0\u00a0\u00a0\u00a0FROM\u00a0authors\r\nGO\r\nALTER TABLE\u00a0dbo.authorsCopy\u00a0ADD CONSTRAINT\u00a0PK_authorsCopy\u00a0PRIMARY KEY CLUSTERED\r\n\u00a0\u00a0\u00a0\u00a0(au_id)\u00a0\u00a0ON PRIMARY\r\nGO<\/pre>\n<p>And then altered some of the rows.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">UPDATE\u00a0authorsCopy\u00a0SET\u00a0address=STUFF(address,1,1,'')\u00a0\r\n\u00a0\u00a0\u00a0\u00a0WHERE\u00a0au_ID\u00a0IN\u00a0(\r\n\u00a0\u00a0\u00a0\u00a0SELECT TOP\u00a010 au_id\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0authorsCopy f\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ORDER BY\u00a0phone)\r\n<\/pre>\n<p>So now the two tables should be predominately the same with a few minor changes in the address field<\/p>\n<h2>Testing to see if tables are different.<\/h2>\n<p>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 <code>CHECKSUM()<\/code>group of functions in SQL Server, because they are very quick.<\/p>\n<h3>Using Checksums<\/h3>\n<p>You can use the <code>BINARY_CHECKSUM <\/code>function to check whether tables are the same: well, roughly the same. It is fast, but it is not perfect, as I&#8217;ll demonstrate in a moment. If you have a series of tests, for example it is generally sufficient.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">IF (\r\n\u00a0\u00a0\u00a0 SELECT\u00a0\u00a0 CHECKSUM_AGG(BINARY_CHECKSUM(*)) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 authors)=(\r\n\u00a0\u00a0\u00a0 SELECT\u00a0\u00a0 CHECKSUM_AGG(BINARY_CHECKSUM(*)) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 authorsCopy)\r\nSELECT 'they are probably the same'\r\nELSE\r\nSELECT 'they are different'\r\n\u00a0\r\n<\/pre>\n<p>For this to work, your table must not have <code>TEXT, NTEXT, IMAGE or CURSOR <\/code>(or a <code>SQL_VARIANT<\/code> 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&#8217;t pretty.<\/p>\n<p>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\u00a0 just certain columns,<\/p>\n<p>Neither<code> BINARY_CHECKSUM() <\/code>nor its plain sister <code>CHECKSUM()<\/code> are completely accurate in telling you if something has changed in a row or table. We&#8217;ll show this by looking at the common words of the English language, contained in a table called <code>CommonWords<\/code>.. You&#8217;d expect them all to have a different checksum, but that&#8217;s not the case.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT string, BINARY_CHECKSUM(string) AS \"Checksum\"\r\n\u00a0\u00a0\u00a0 FROM commonWords \r\n\u00a0\u00a0\u00a0 WHERE BINARY_CHECKSUM(string) IN\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0 SELECT BINARY_CHECKSUM(string)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM commonwords\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GROUP BY BINARY_CHECKSUM(string)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HAVING COUNT(*) &gt; 2)\r\n\u00a0\u00a0\u00a0 ORDER BY BINARY_CHECKSUM(string) \r\n<\/pre>\n<p>&#8230; giving the result &#8230;<\/p>\n<pre>\u00a0\r\nstring\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Checksum\r\n------------------------------ -----------\r\nnerd\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 426564\r\nnest\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 426564\r\noust\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 426564\r\nreed\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 475956\r\nstud\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 475956\r\nsued\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0475956\r\nousts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6825011\r\nnests\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6825011\r\nnerds\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6825011\r\n<\/pre>\n<p>Armed with this information, we can quickly demonstrate that different strings can have the same checksum<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT BINARY_CHECKSUM('reed the nerd'), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0BINARY_CHECKSUM('sued the nest'), \r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0BINARY_CHECKSUM('stud the oust')\u00a0 \r\n<\/pre>\n<p>All these will; have the same checksum, as would &#8230;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT \r\n\u00a0\u00a0\u00a0\u00a0 BINARY_CHECKSUM('accosted guards'), \r\n\u00a0\u00a0\u00a0\u00a0 BINARY_CHECKSUM('accorded feasts')\r\n<\/pre>\n<p>&#8230;.whereas&#8230;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT BINARY_CHECKSUM('This looks very much like the next'), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BINARY_CHECKSUM('this looks very much like the next'), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BINARY_CHECKSUM('This looks very much like the Next')\u00a0 \r\n<\/pre>\n<p>&#8230; gives you different checksums like this&#8230;<\/p>\n<pre>----------- ----------- -----------\r\n-447523377\u00a0 -447522865\u00a0 -447654449\r\n<\/pre>\n<p>The sister function <code>CHECKSUM() <\/code>&#8230;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\r\nSELECT CHECKSUM('This looks very much like the next'), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHECKSUM('this looks very much like the next'), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHECKSUM('This looks very much like the Next')\u00a0 \r\n<\/pre>\n<p>&#8230; finds them to be all the same, because it is using the current collation and my collation for the database is case-insensitive. <code>CHECKSUM()<\/code> aims to find strings equal in checksum if they are equal in a string comparison.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">----------- ----------- -----------\r\n-943581052\u00a0 -943581052\u00a0 -943581052\r\n<\/pre>\n<p>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.<\/p>\n<p>If you don&#8217;t mind difference in case in text strings, then you can use <code>CHECKSUM()<\/code> instead of <code>BINARY_CHECKSUM<\/code>()<\/p>\n<p>The great value of this technique is that, once you&#8217;ve calculated the checksum that you need, you can store it as a value in the column of \u00a0a 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 <code>CHECKSUM()<\/code> make sure you\u00a0 check against the live table with a checksum generated with the same collation.<\/p>\n<p>Here is a simple example of a &#8216;what&#8217;s changed&#8217; routine.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">--we'll create a 'checksum' table 'on the fly' using a SELECT INTO.\r\nSELECT\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 au_ID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 BINARY_CHECKSUM(au_id, au_lname, au_fname, phone, [address], city, [state], zip, [contract]) AS [checksum]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 INTO auchk\r\n\u00a0 FROM authorscopy \r\n\u00a0  ORDER BY au_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \/* now we'll put in a constraint just to check that we haven't won the lottery (very unlikely but not completely impossible that we have two rows with the same checksum) *\/\r\nALTER TABLE AuChk ADD CONSTRAINT IsItUnique UNIQUE ([checksum])\r\nUPDATE authorscopy SET au_fname='Arthur' \r\n\u00a0  WHERE au_ID='327-89-2366'\r\nSELECT authorscopy.* \r\n\u00a0 FROM authorscopy\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN AuChk ON authorscopy.au_ID=AuChk.au_ID\r\n\u00a0  WHERE [checksum]&lt;&gt;BINARY_CHECKSUM(authorscopy.au_id, au_lname, au_fname, phone, [address], city, [state], zip, [contract])\r\n<\/pre>\n<p>&#8230;which gives&#8230;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">au_id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 au_lname\u00a0 au_fname\u00a0 phone\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 address\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 city\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 state zip\u00a0\u00a0 contract\r\n----------- --------- --------- ------------ --------------- ------------- ----- ----- --------\r\n327-89-2366 Mendoza\u00a0\u00a0 Arthur\u00a0\u00a0\u00a0 529275-5757\u00a0 15 Hague Blvd.\u00a0 Little Rock\u00a0\u00a0 DE\u00a0\u00a0\u00a0 98949 1\r\n<\/pre>\n<p>And then we just tidy up.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\/* and we just pop it back to what it was, as part of the teardown *\/\r\nUPDATE authorscopy SET au_fname='Arnold' \r\n\u00a0  WHERE au_ID='327-89-2366'\r\n<\/pre>\n<p>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.<\/p>\n<h3>Using XML<\/h3>\n<p>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.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">IF CONVERT(VARCHAR(MAX),(\r\n\u00a0\u00a0\u00a0 SELECT * \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM authors ORDER BY au_id\u00a0FOR XML path, root))\r\n\u00a0 =\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 CONVERT(VARCHAR(MAX),(\r\n\u00a0\u00a0\u00a0 SELECT * \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM authorscopy\u00a0ORDER BY au_id\u00a0FOR XMLpath, root))\r\nSELECT 'they are\u00a0 the same'\r\nELSE\r\nSELECT 'they are different'\r\n<\/pre>\n<p>Here, you can specify the type of comparison by specifying the collation.<\/p>\n<p>or you can do this, comparing data in tables ..<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">IF BINARY_CHECKSUM(CONVERT(VARCHAR(MAX),(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT * \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM authors ORDER BY au_id\u00a0FOR XML path, root)))\r\n\u00a0\u00a0\u00a0 =\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BINARY_CHECKSUM (CONVERT(VARCHAR(MAX),(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT * \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM authorscopy ORDER BY au_id\u00a0FOR XML path, root)))\r\n\u00a0  SELECT 'they are pretty much the same'\r\nELSE\r\nSELECT 'they are different'\u00a0 SELECT 'they are different'\r\n<\/pre>\n<p>&#8230; 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.<\/p>\n<h2>Finding where the differences are in a table<\/h2>\n<p>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.\u00a0 You have, of course, to specify what you mean by &#8216;the same&#8217;, particularly if the two tables have different columns. The method you choose to do the comparison is generally determined by these details.<\/p>\n<h3>The UNION ALL &#8230; GROUP BY technique<\/h3>\n<p>The classic approach to comparing tables is to use a\u00a0 <code>UNION ALL<\/code> for the <code>SELECT<\/code> statements that include the columns you want to compare, and then <code>GROUP BY<\/code> those columns. Obviously, for this to work, there must be a column with unique values in the <code>GROUP BY<\/code>, 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.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT DISTINCT\u00a0au_ID\u00a0\r\n\u00a0\u00a0FROM\r\n\u00a0\u00a0(\r\n\u00a0\u00a0SELECT\u00a0au_ID\u00a0\r\n\u00a0\u00a0\u00a0\u00a0FROM\r\n\u00a0\u00a0\u00a0\u00a0(\r\n\u00a0\u00a0\u00a0\u00a0SELECT\u00a0au_id,\u00a0au_lname,\u00a0au_fname,\u00a0phone,\u00a0address,\u00a0city,\u00a0state,\u00a0zip,\u00a0contract\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0authors\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0UNION\u00a0ALL\r\n\u00a0\u00a0\u00a0\u00a0SELECT\u00a0au_id,\u00a0au_lname,\u00a0au_fname,\u00a0phone,\u00a0address,\u00a0city,\u00a0state,\u00a0zip,\u00a0contract\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0authorsCopy)\u00a0BothOfEm\r\n\u00a0\u00a0\u00a0\u00a0GROUP BY\u00a0au_id,\u00a0au_lname,\u00a0au_fname,\u00a0phone,\u00a0address,\u00a0city,\u00a0state,\u00a0zip,\u00a0contract\r\n\u00a0\u00a0\u00a0\u00a0HAVING\u00a0COUNT(*)&lt;2)\u00a0f<\/pre>\n<p>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\u00a0 and the result tells you that they are the same! For this reason, it is a good idea to include the column(s) that\u00a0 constitute the primary key, and only include the rows \u00a0once!<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT COUNT(*), Address_ID,TheAddress,ThePostCode \r\n\u00a0 FROM\r\n\u00a0 (\r\n\u00a0  SELECT Address_ID,TheAddress,ThePostCode\r\n\u00a0\u00a0\u00a0 FROM\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 VALUES\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (9, '929 Augustine lane,\u00a0 Staple Hill Ward\u00a0 South Gloucestershire\u00a0 UK','BS16 4LL'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (10, '45 Bradfield road, Parwich \u00a0Derbyshire\u00a0 UK','DE6 1QN')\r\n\u00a0\u00a0\u00a0 ) TableA(Address_ID,TheAddress,ThePostCode)\r\n\u00a0\u00a0\u00a0 UNION ALL\r\n\u00a0  SELECT Address_ID,TheAddress,ThePostCode \r\n\u00a0\u00a0\u00a0 FROM\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 VALUES\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (8, '''The Pippins'', 20 Gloucester Pl, Chirton Ward,\u00a0 Tyne &amp;amp; Wear\u00a0 UK','NE29 7AD'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (8, '''The Pippins'', 20 Gloucester Pl, Chirton Ward,\u00a0 Tyne &amp;amp; Wear\u00a0 UK','NE29 7AD'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (9, '929 Augustine lane,\u00a0 Staple Hill Ward\u00a0 South Gloucestershire\u00a0 UK','BS16 4LL'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (10, '45 Bradfield road, Parwich\u00a0 Derbyshire\u00a0 UK','DE6 1QN')\r\n\u00a0\u00a0\u00a0 ) TableB(Address_ID,TheAddress,ThePostCode)\r\n\u00a0 )f\r\n\u00a0  GROUP BY Address_ID,TheAddress,ThePostCode\r\n\u00a0  HAVING COUNT(*)&lt;2\r\n<\/pre>\n<p>&#8230; giving &#8230;<\/p>\n<pre>TheCount\u00a0\u00a0\u00a0 Address_ID\u00a0 TheAddress\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ThePostCode\r\n----------- ----------- ------------------------- ------------\r\n\u00a0\r\n(0 row(s) affected) \r\n<\/pre>\n<p>The technique can be used for comparing more than two tables. \u00a0You&#8217;d just need to <code>UNION ALL<\/code> the tables you need to compare and change the <code>HAVING<\/code> clause to filter just the rows that aren&#8217;t in all the tables.<\/p>\n<h3>Using EXCEPT<\/h3>\n<p>You can now use the much cleaner and slightly faster <code>EXCEPT<\/code>.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT *\u00a0 from authors\r\nEXCEPT\r\nSELECT * from authorsCopy\r\n<\/pre>\n<p>This shows all the rows in <b>authors<\/b> that are not found \u00a0in <b> authorsCopy<\/b>.\u00a0 If they are the same, it would return no rows<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">au_id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 au_lname\u00a0\u00a0\u00a0 au_fname\u00a0 phone\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 address\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 city\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 state zip\u00a0\u00a0 contract\r\n----------- ----------- --------- ------------ -------------------------- ----------- ----- ----- --------\r\n041-76-1076 Sosa\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0Sonja\u00a0\u00a0\u00a0\u00a0 000-198-8753 29 Second Avenue\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Omaha\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CT\u00a0\u00a0\u00a0 23243 0\r\n187-42-2491 Mc Connell\u00a0 Trenton\u00a0\u00a0 0003090766\u00a0\u00a0 279 Hague Way\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 San Diego\u00a0\u00a0 NY\u00a0\u00a0\u00a0 94940 1\r\n220-43-7067 Fox\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Judith\u00a0\u00a0\u00a0 000-137-9418 269 East Hague Street\u00a0\u00a0\u00a0\u00a0\u00a0 Richmond\u00a0\u00a0\u00a0 VA\u00a0\u00a0\u00a0 55027 0\r\n505-28-2848 Hardy\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Mitchell\u00a0 001-2479822\u00a0 73 Green Milton Drive\u00a0\u00a0\u00a0\u00a0\u00a0 Norfolk\u00a0\u00a0\u00a0\u00a0 WA\u00a0\u00a0\u00a0 69949 1\r\n697-84-0401 Montes\u00a0\u00a0\u00a0\u00a0\u00a0 Leanne\u00a0\u00a0\u00a0 000-018-0454 441 East Oak Parkway\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 San Antonio MD\u00a0\u00a0\u00a0 38169 1\r\n727-35-9948 Long\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Jonathon\u00a0 000-8761152\u00a0 280 Nobel Avenue\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Anchorage\u00a0\u00a0 LA\u00a0\u00a0\u00a0 NULL\u00a0 1\r\n875-54-8676 Stone\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Keisha\u00a0\u00a0\u00a0 000-107-1947 763 White Fabien Way\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Fremont\u00a0\u00a0\u00a0\u00a0 ND\u00a0\u00a0\u00a0 08520 0\r\n884-64-5876 Keller\u00a0\u00a0\u00a0\u00a0\u00a0 Steven\u00a0\u00a0\u00a0 000-2787554\u00a0 45 White Nobel Boulevard\u00a0\u00a0 Milwaukee\u00a0\u00a0 NY\u00a0\u00a0\u00a0 29108 1\r\n886-75-9197 Ellis\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Marie\u00a0\u00a0\u00a0\u00a0 001032-5109\u00a0 35 East Second Boulevard\u00a0\u00a0 Chicago\u00a0\u00a0\u00a0\u00a0 IL\u00a0\u00a0\u00a0 32390 1\r\n975-80-3567 Salazar\u00a0\u00a0\u00a0\u00a0 Johanna\u00a0\u00a0 001-028-0716 17 New Boulevard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Jackson\u00a0\u00a0\u00a0\u00a0 ND\u00a0\u00a0\u00a0 71625 0\r\n\u00a0\r\n(10 row(s) affected)\r\n<\/pre>\n<p>I&#8217;m only using <b>SELECT *<\/b> to keep things simple for the article. You&#8217;d normally itemize all the columns you want to compare.<\/p>\n<p>This will only work for tables with the same number of rows because, if <b>authors<\/b> had extra rows, it would still say that they were different since the rows in Authors that weren&#8217;t in <b>authorsCopy<\/b> would be returned. This is because <code>EXCEPT<\/code> returns any distinct values from the query to the left of the <code>EXCEPT<\/code> operand that are not also found from the query on the right<\/p>\n<p>This, hopefully shows what I mean<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Address_ID,TheAddress,ThePostCode\r\n\u00a0\u00a0\u00a0 FROM\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (VALUES\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (9, '929 Augustine lane, Staple Hill Ward\u00a0\u00a0\u00a0\u00a0 South Gloucestershire\u00a0\u00a0\u00a0\u00a0\u00a0 UK','BS16 4LL'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (10, '45 Bradfield road, Parwich\u00a0\u00a0\u00a0\u00a0\u00a0 Derbyshire\u00a0\u00a0\u00a0 UK','DE6 1QN')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) TableA(Address_ID,TheAddress,ThePostCode)\r\nEXCEPT\r\n\u00a0\u00a0 SELECT Address_ID,TheAddress,ThePostCode from\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (VALUES\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (8, '''The Pippins'', 20 Gloucester Pl, Chirton Ward,\u00a0\u00a0\u00a0\u00a0\u00a0 Tyne &amp; Wear\u00a0\u00a0 UK','NE29 7AD'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (8, '''The Pippins'', 20 Gloucester Pl, Chirton Ward,\u00a0\u00a0\u00a0\u00a0\u00a0 Tyne &amp; Wear\u00a0\u00a0 UK','NE29 7AD'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (9, '929 Augustine lane, Staple Hill Ward\u00a0\u00a0\u00a0\u00a0 South Gloucestershire\u00a0\u00a0\u00a0\u00a0\u00a0 UK','BS16 4LL'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (10, '45 Bradfield road, Parwich\u00a0\u00a0\u00a0\u00a0\u00a0 Derbyshire\u00a0\u00a0\u00a0 UK','DE6 1QN')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) TableB(Address_ID,TheAddress,ThePostCode)\r\n<\/pre>\n<p>&#8230;yields &#8230;<\/p>\n<pre>\u00a0Address_ID\u00a0 TheAddress\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ThePostCode\r\n----------- ---------------------------------------------- -----------\r\n\u00a0\r\n(0 row(s) affected)\r\n<\/pre>\n<p>&#8230;whereas &#8230;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0  SELECT Address_ID,TheAddress,ThePostCode FROM\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (VALUES\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (8, '''The Pippins'', 20 Gloucester Pl, Chirton Ward,\u00a0\u00a0\u00a0\u00a0\u00a0 Tyne &amp; Wear\u00a0\u00a0 UK','NE29 7AD'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (8, '''The Pippins'', 20 Gloucester Pl, Chirton Ward,\u00a0\u00a0\u00a0\u00a0\u00a0 Tyne &amp; Wear\u00a0\u00a0 UK','NE29 7AD'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (9, '929 Augustine lane, Staple Hill Ward\u00a0\u00a0\u00a0\u00a0 South Gloucestershire\u00a0\u00a0\u00a0\u00a0\u00a0 UK','BS16 4LL'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (10, '45 Bradfield road, Parwich\u00a0\u00a0\u00a0\u00a0\u00a0 Derbyshire\u00a0\u00a0\u00a0 UK','DE6 1QN')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) TableB(Address_ID,TheAddress,ThePostCode)\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nEXCEPT\r\n\u00a0\u00a0\u00a0\u00a0 SELECT Address_ID,TheAddress,ThePostCode\r\n\u00a0\u00a0\u00a0 FROM\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (VALUES\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (9, '929 Augustine lane, Staple Hill Ward\u00a0\u00a0\u00a0\u00a0 South Gloucestershire\u00a0\u00a0\u00a0\u00a0\u00a0 UK','BS16 4LL'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (10, '45 Bradfield road, Parwich\u00a0\u00a0\u00a0\u00a0\u00a0 Derbyshire\u00a0\u00a0\u00a0 UK','DE6 1QN')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) TableA(Address_ID,TheAddress,ThePostCode)\r\n<\/pre>\n<p>..results in &#8230;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Address_ID\u00a0 TheAddress\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ThePostCode\r\n----------- ------------------------------------------------------------- -----------\r\n8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'The Pippins', 20 Gloucester Pl, Chirton Ward, Tyne &amp; Wear UK NE29 7AD\r\n\u00a0\r\n(1 row(s) affected)\r\n<\/pre>\n<p>This feature of <code>EXCEPT<\/code> could be used to advantage if you particularly wish to check that <code>TableA<\/code> is contained within <code>TableB<\/code>. So where the tables have a different number of rows you can still compare them.<\/p>\n<p>You might not want to compare all columns. \u00a0You should always specify those columns you wish to compare to determine &#8216;sameness&#8217;. If you only wanted to compare the Address for example, you&#8217;d use &#8230;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT address\u00a0 FROM authors\r\nEXCEPT\r\nSELECT address FROM authorsCopy\r\n<\/pre>\n<h3>The Outer Join technique<\/h3>\n<p>There is also the technique of the outer join. \u00a0This is a more general technique that give you additional facilities. If, for example, you use the full outer join then \u00a0you can get the unmatched rows in either table. This gives you a &#8216;before&#8217; and &#8216;after&#8217; view of alterations in the data.\u00a0 It is used more generally in synchronisation to tell you what rows to delete, insert and update.<\/p>\n<p>We&#8217;ll just use the technique to get the altered rows in <b> authorsCopy<\/b><\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 authors.au_id, authors.au_lname, authors.au_fname, authors.phone, authors.address, authors.city, authors.state, authors.zip, authors.contract\r\n\u00a0 FROM authors\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 LEFT OUTER JOIN authorsCopy\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ON authors.au_ID = AuthorsCopy.au_ID\r\n\u00a0\u00a0\u00a0 AND authors.au_lname =authorsCopy.au_lname\r\n\u00a0\u00a0\u00a0 AND\u00a0 authors.au_fname =authorsCopy.au_fname\r\n\u00a0\u00a0\u00a0 AND\u00a0 authors.phone\u00a0\u00a0\u00a0 =authorsCopy.phone\r\n\u00a0\u00a0\u00a0 AND\u00a0 COALESCE(authors.address,'')=COALESCE(authorsCopy.address,'')\r\n\u00a0\u00a0\u00a0 AND\u00a0 COALESCE(authors.city,'')\u00a0\u00a0\u00a0=COALESCE(authorsCopy.city,'')\r\n\u00a0\u00a0\u00a0 AND\u00a0 COALESCE(authors.state,'')\u00a0 =COALESCE(authorsCopy.state,'')\r\n\u00a0\u00a0\u00a0 AND\u00a0 COALESCE(authors.zip,'')\u00a0\u00a0\u00a0 =COALESCE(authorsCopy.zip,'')\r\n\u00a0\u00a0\u00a0 AND\u00a0 authors.contract =authorsCopy.contract\r\n\u00a0 WHERE authorsCopy.au_ID IS NULL\r\n<\/pre>\n<p>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.<\/p>\n<h2>Locating the differences between tables<\/h2>\n<p>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. &#8216;Compare Tables And Report The Differences By Using Xml To Pivot The Data&#8217; (<em>editor&#8217;s note: link deprecated<\/em>). It is clever, but too slow. The same thing can be done purely in SQL.\u00a0 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.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\r\nDECLARE\u00a0 @temp TABLE(au_id VARCHAR(11) PRIMARY KEY) \/*this holds the primary keys of rows that have changed *\/\r\nINSERT INTO @Temp(au_ID) --determine which rows have changed\r\n\u00a0  SELECT au_ID \r\n\u00a0 FROM --use the EXCEPT technique qhich is the quickest in our tests\r\n\u00a0 (\r\n\u00a0  SELECT au_id, au_lname, au_fname, phone, [address], city, state, zip, [contract] \r\n\u00a0\u00a0\u00a0 FROM authors\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXCEPT\r\n\u00a0  SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract \r\n\u00a0\u00a0\u00a0 FROM authorsCopy\r\n\u00a0 )f--now we just SELECT those columns that have changed\r\nSELECT lefthand.au_id,lefthand.name,lefthand.value AS original,Righthand.value AS changed\r\n\u00a0 FROM (--now we just lay out the two tables as key value pairs, using the string versions of the data\r\n\u00a0  SELECT authors.au_id, 'au_lname' AS 'name',au_lname AS 'value'\r\n\u00a0\u00a0\u00a0 FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id\r\n\u00a0\u00a0\u00a0 UNION\r\n\u00a0  SELECT authors.au_id, 'au_fname' AS 'name',au_fname AS 'value'\r\n\u00a0\u00a0\u00a0 FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id\r\n\u00a0\u00a0\u00a0 UNION\r\n\u00a0  SELECT authors.au_id, 'phone',phone\r\n\u00a0\u00a0\u00a0 FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id\r\n\u00a0\u00a0\u00a0 UNION\r\n\u00a0  SELECT authors.au_id, 'address',address\r\n\u00a0\u00a0\u00a0 FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id\r\n\u00a0\u00a0\u00a0 UNION\r\n\u00a0  SELECT authors.au_id, 'City' AS 'name',City AS 'value'\r\n\u00a0\u00a0\u00a0 FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id\r\n\u00a0\u00a0\u00a0 UNION\r\n\u00a0  SELECT authors.au_id, 'State',state\r\n\u00a0\u00a0\u00a0 FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id\r\n\u00a0\u00a0\u00a0 UNION\r\n\u00a0  SELECT authors.au_id, 'zip',zip\r\n\u00a0\u00a0\u00a0 FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id\r\n\u00a0\u00a0\u00a0 UNION\r\n\u00a0  SELECT authors.au_id, 'contract',CONVERT(CHAR(1),contract)\r\n\u00a0\u00a0\u00a0 FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id) LeftHand\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN (\r\n\u00a0  SELECT authorsCopy.au_id, 'au_lname' AS 'name',au_lname AS 'value'\r\n\u00a0\u00a0\u00a0 FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id\r\n\u00a0\u00a0\u00a0 UNION\r\n\u00a0  SELECT authorsCopy.au_id, 'au_fname',au_fname\r\n\u00a0\u00a0\u00a0 FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id\r\n\u00a0\u00a0\u00a0 UNION\r\n\u00a0  SELECT authorsCopy.au_id, 'phone',phone\r\n\u00a0\u00a0\u00a0 FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id\r\n\u00a0\u00a0\u00a0 UNION\r\n\u00a0  SELECT authorsCopy.au_id, 'address',address\r\n\u00a0\u00a0\u00a0 FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id\r\n\u00a0\u00a0\u00a0 UNION\r\n\u00a0  SELECT authorsCopy.au_id, 'City' AS 'name',City AS 'value'\r\n\u00a0\u00a0\u00a0 FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id\r\n\u00a0\u00a0\u00a0 UNION\r\n\u00a0  SELECT authorsCopy.au_id, 'State',state\r\n\u00a0\u00a0\u00a0 FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id\r\n\u00a0\u00a0\u00a0 UNION\r\n\u00a0  SELECT authorsCopy.au_id, 'zip',zip\r\n\u00a0\u00a0\u00a0 FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id\r\n\u00a0\u00a0\u00a0 UNION\r\n\u00a0  SELECT authorsCopy.au_id, 'contract',CONVERT(CHAR(1),contract)\r\n\u00a0\u00a0\u00a0 FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id) rightHand\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ON lefthand.au_ID=righthand.au_ID\r\n\u00a0\u00a0\u00a0 AND lefthand.name=righthand.name\r\n\u00a0 WHERE lefthand.value&lt;&gt;righthand.value\r\n<\/pre>\n<p>in our example, this would give:<\/p>\n<pre>au_id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name\u00a0\u00a0\u00a0\u00a0 original\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 changed\r\n----------- -------- ---------------------------- ------------------------------------\r\n041-76-1076 address\u00a0 29 Second Avenue\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9 Second Avenue\r\n187-42-2491 address\u00a0 279 Hague Way\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 79 Hague Way\r\n220-43-7067 address\u00a0 269 East Hague Street\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 69 East Hague Street\r\n505-28-2848 address\u00a0 73 Green Milton Drive\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 Green Milton Drive\r\n697-84-0401 address\u00a0 441 East Oak Parkway\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 41 East Oak Parkway\r\n727-35-9948 address\u00a0 280 Nobel Avenue\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 80 Nobel Avenue\r\n875-54-8676 address\u00a0 763 White Fabien Way\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 63 White Fabien Way\r\n884-64-5876 address\u00a0 45 White Nobel Boulevard\u00a0\u00a0\u00a0\u00a0\u00a0 5 White Nobel Boulevard\r\n886-75-9197 address\u00a0 35 East Second Boulevard\u00a0\u00a0\u00a0\u00a0\u00a0 5 East Second Boulevard\r\n975-80-3567 address\u00a0 17 New Boulevard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7 New Boulevard\r\n<\/pre>\n<p>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.\u00a0 It does this rotation by <code>UNION<\/code>ing the name and string-value of each column.\u00a0 This technique works best where there are not a large number of differences.<\/p>\n<h2>Conclusions<\/h2>\n<p>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\u00a0 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&#8217;t changed, or do you need to know precisely what the changes are?\u00a0 SQL is naturally fast at doing this task and comparisons of tables and results is a familiar task to many database developers.<\/p>\n<p>If there is a general rule, I&#8217;d say that \u00a0exploratory or ad-hoc work \u00a0needs a tool such as SQL Data Compare, whereas\u00a0 a routine process within the database \u00a0requires a hand-cut SQL technique.<\/p>\n<div class=\"note\">\n<p class=\"note\">The source to the table, and the insert-statements to fill it to 5000 rows is in the link below.<\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>How do you compare two SQL tables?  Every SQL Developer or DBA knows the answer, which is &#8216;it depends&#8217;. 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.&hellip;<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4242,4150,4151,4252],"coauthors":[6813],"class_list":["post-1814","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-basics","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1814","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1814"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1814\/revisions"}],"predecessor-version":[{"id":74812,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1814\/revisions\/74812"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1814"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1814"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1814"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1814"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}