Mark differences in AssertEqualsTable result

[mvomhau]

to find the differences in the AssertEqualsTable result table it could be helpful to mark the different value with a special sign.

in this original result set are three differences, which are hard to find.

|ContraAccountType                   |ContraAccount                       |ReferenceNumber|Amount  |
+------------------------------------+------------------------------------+---------------+--------+
|00000000-0000-0000-0000-000000020093|00000044-0000-0000-0000-000000007990|FG-RG 001      |105,0000|
|00000000-0000-0000-0000-000000020093|00000044-0000-0000-0000-000000007990|FG-RG 001      |100,0000|
|00000000-0000-0000-0000-000000020094|00000044-0000-0000-0000-000000007990|FG-RG 002      |100,0000|
|00000000-0000-0000-0000-000000020093|00000044-0000-0000-0000-000000007990|FG-RG 002      |100,0000|
|00000000-0000-0000-0000-000000020093|00000044-0000-0000-0000-000000007991|FG-RG 003      |100,0000|
|00000000-0000-0000-0000-000000020093|00000044-0000-0000-0000-000000007990|FG-RG 003      |100,0000|

It may be easier to spot the differences, when, for example, the left sign of a value pair is a ~ instead if a |

|ContraAccountType                   |ContraAccount                       |ReferenceNumber|Amount  |
+------------------------------------+------------------------------------+---------------+--------+
|00000000-0000-0000-0000-000000020093|00000044-0000-0000-0000-000000007990|FG-RG 001      ~105,0000|
|00000000-0000-0000-0000-000000020093|00000044-0000-0000-0000-000000007990|FG-RG 001      ~100,0000|
~00000000-0000-0000-0000-000000020094|00000044-0000-0000-0000-000000007990|FG-RG 002      |100,0000|
~00000000-0000-0000-0000-000000020093|00000044-0000-0000-0000-000000007990|FG-RG 002      |100,0000|
|00000000-0000-0000-0000-000000020093~00000044-0000-0000-0000-000000007991|FG-RG 003      |100,0000|
|00000000-0000-0000-0000-000000020093~00000044-0000-0000-0000-000000007990|FG-RG 003      |100,0000|

if the table structure is important, then this could also be a possibility and place a ~ between | and the values

+-------------------------------------+-------------------------------------+---------------+---------+
| 00000000-0000-0000-0000-000000020093| 00000044-0000-0000-0000-000000007990|FG-RG 001      |~105,0000|
| 00000000-0000-0000-0000-000000020093| 00000044-0000-0000-0000-000000007990|FG-RG 001      |~100,0000|
|~00000000-0000-0000-0000-000000020094| 00000044-0000-0000-0000-000000007990|FG-RG 002      | 100,0000|
|~00000000-0000-0000-0000-000000020093| 00000044-0000-0000-0000-000000007990|FG-RG 002      | 100,0000|
| 00000000-0000-0000-0000-000000020093|~00000044-0000-0000-0000-000000007991|FG-RG 003      | 100,0000|
| 00000000-0000-0000-0000-000000020093|~00000044-0000-0000-0000-000000007990|FG-RG 003      | 100,0000|

[mbt1]

Without going too much into the technical details, the way tSQLt.AssertEqualsTable is written, attempting this will become very quickly too expensive computationally and sometimes straight out impossible. For example, if you expect one row with the values 'A', 'B' and get two rows 'A','C' and 'C','B'.

Currently, tSQLt is using the GROUP BY functionality to achieve this task, but with that is getting only information about the full row, not individual columns.

Do you maybe have suggestions on how to implement this while maintaining performance?

[mvomhau]

Possible solution:

• Add a new parameter "@UniqueKey" to AssertEqualsTable, where you can pass a column list whose values should be unique. Without a unique key it is not possible to say which rows should be equal. In my example above I implicitly add a unique key on column ReferenceNumber. I think you can often define one.
• For each non unique key column in the table you can check with the lead/lag window function partitioned by the unique key columns, if a value of two or more rows are equal or not
• instead of a new parameter we could also add a real unique index at least to one of the tables and get the column list from the meta data. But this could be tricky but still doable with temp tables.
• Performance could be OK as long as you don't have too many rows

(this solution is only possible with SQL Server 2012+)

[mbt1]

This is a good idea and could be implemented going back before 2012, too, I believe (not with window functions). However, I am not sure this is providing an appropriate amount of value for the amount of work that would have to go into it. In my experience, if you are having trouble finding the differences, you might have too much going on in your test. If you test only a single piece of functionality, you rarely need to compare more than 3 or 4 columns.

I'll leave this open to see if others comment on it with a different opinion.