Below are some instructions on using the app. If you have questions not answered here, feel free to open an issue.
As a simple example, lets create two tables to compare. As you can see these have the same structure, but have some deliberatly discrepant data.
To generate a comparison script:
- Create these tables and data in a test database.
-- Sample Data Source
Create Table Books
( Id int not null
,Author varchar(50) not null
,Title varchar(50) not null)
Create Table SciFiBooks
( Id int not null
,Author varchar(50) not null
,Title varchar(50) not null)
Insert into Books (Id, Author, Title)
Values (1, 'Martha Wells', 'All Systems Red')
,(2, 'Terry Mancour', 'Spellmonger')
,(3, 'Yahtzee Croshaw', 'Will Save the Galaxy for Food')
,(4, 'Andrew Rowe', 'Sufficiently Advanced Magic')
,(5, 'Brandon Sanderson', 'The Emperor''s Soul')
,(7, 'Lois McMaster Bujold', 'The Vor Game')
Insert into SciFiBooks (Id, Author, Title)
Values (1, 'Martha Wells', 'All Systems Red')
,(3, 'Yahtzee Croshaw', 'Will Save the Galaxy for Food')
,(4, 'Andrew Rowe', 'Sufficiently Advanced Magic')
,(5, 'Lois McMaster Bujold', 'The Warrior''s Apprentice')
,(6, 'Larry Niven', 'Ringworld')
,(7, 'Lois McMaster Bujold', 'Ceteganda')- Open the web app.
- Then paste the
AssertandTestsql into the app.
-- Assert
Select Id, Author, Title
From Books
-- Test
Select Id, Author, Title
From SciFiBooks- Select the
Idcolumn as a key.
- Copy the comparison script.
- Run the query in the test database. The output below will be produced.
As we can see, the query has correctly found the following:
- 3 rows are identical.
- Spellmonger is in
Booksbut notSciFiBooks. - Ringworld is in
SciFiBooksbut notBooks. - Two rows have discrepancies. The column specific discrepancy tables
Discrepant__AuthorandDiscrepant__Titleshow one and two discrepancies respectivly.
Here are a few more advanced considerations.
When choosing keys, you must pick at least one key and the key(s) you choose must be uniquely represent a row of the results. Once you met this requirement you can still select more columns as keys. 'Over defining' the keys can substantially alter the results of the comparison. This is particularly true when working on queries with complex joins. If we take the book query above, and compare using Id, or Auhtor and ID we get different results. As discussed below, adding the extra key is equivelent to comparing the results of querying for each author one at a time.
In the results, we can see that we now have more missing and extra columns. Discrepancies only show up where the author matches, as well as the key.
| Keys: Id | Keys: Author, Id |
|---|---|
|
 |
So long as you have the appropriate permissions, you can compare results from multiple databases. To do this, use three part naming for all of the tables in the query. For example [DatabaseA].[dbo].[TableA].
You can compare data across servers, so long as you can query both from the same connection. This will require a linked server connection. If the servers are not already linked, and especially if either is a production server, think very carefully before adding a link just to do a data comparison. If servers are not linked, a better option may be to create links to both servers from a local SQLExpress instance running on your own machine.
Cross server comparison requires four part naming for all of the tables in the query. For example [ServerA].[DatabaseA].[dbo].[TableA].
Queries with CTEs work just fine.
If your queries produce intermediate results in temp tables, this is perfectly fine. The only consideration is that the two queries cannot create temp tables with the same names. If this is an issue, here are two options.
- Drop the temp table(s) at the end of the
Assertor the beginning of theTestsql. - If you want to share the temp table between the two queries. You can create the table only in the
Assertquery. Or you can paste it into the template after it is created. There is a section at the top of the template for this purpose.
Several temp table names are reserved for use by the app.
- #Assert
- #Test
- #Matched
- #Discrepant
- #Discrepant__{TableName}
- #Extra
- #Missing
If paramaterized queries share the same parameter names, you can paste the parameter declarations into the template after it is created. There is a section at the top of the template for this purpose. This makes it easier to find and change the parameter as needed, than if you include it in the Assert or Test sql.
If you need to compare the results from the query, with many different parameter inputs, there is a simpler option than running the query over and over with different inputs.
Consider the book queries in the getting started section above. We might parameterize the queries like this to get results by author. We would compare the results using Id as the key.
Declare @Author Varchar(50) = 'Brandon Sanderson';
-- Assert
Select Id, Title
From Books
Where Author = @Author
-- Test
Select Id, Title
From SciFiBooks
Where Author = @AuthorWe can modify the queries to allow us to simulate the output of this query for every value of @Author that returns results. We do this by removing the parameter(s) and instead adding them to the select ed colums. We then also add the parameters to the beginning of the list of keys, so we now have Author, Id as keys. If your tables are very large you may want to be cautious about taking this approach from a performance perspective. A more performant option might be to create a temp table with a subset of authors to test, and add where Author in (Select Author from #TestAuthors) to the query.
-- Assert
Select Author, Id, Title
From Books
-- Test
Select Author, Id, Title
From SciFiBooks
This yields quite a different result from the Getting Started example above, where only Id is key. See the output in Key Selection
This tool does not directly support stored procedures. However, you can take the code from the stored procedures and run it as a paramaterized query.