Take the 2-minute tour ×

I noticed the other day that you can write a test where there are more columns in the Actual table that in the Expected table and the test will still pass if the the data matches in the columns that exist in both.

Here is an example:

if exists(select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA='UnitTests_FirstTry' and ROUTINE_NAME='test_AssertEqualsTable_IgnoresExtraColumnsInActual')
    drop procedure  UnitTests_FirstTry.test_AssertEqualsTable_IgnoresExtraColumnsInActual

create procedure UnitTests_FirstTry.test_AssertEqualsTable_IgnoresExtraColumnsInActual

    IF OBJECT_ID(N'tempdb..#Expected') > 0 DROP TABLE [#Expected];
    IF OBJECT_ID(N'tempdb..#Actual') > 0 DROP TABLE [#Actual];

create table #expected( a int null) --, b int null, c varchar(10) null)
create table #actual(a int, x money null)
insert #expected (a) values (1)
insert #actual (a, x) values (1, 22.51)
--insert #expected (a, b, c) values (1,2,'test')
--insert #actual (a, x) values (1, 22.51)

exec tSQLt.AssertEqualsTable '#expected', '#actual'


exec tSQLt.Run 'UnitTests_FirstTry.test_AssertEqualsTable_IgnoresExtraColumnsInActual'

I noticed this when I removed some extra columns from the Expected table of a test that no longer needed those columns, but I forgot to remove the same columns from the Actual table and my test still passed, which to me was a bit off putting. This only happens when the Actual table has more columns. If the expected has more columns an error is generated. Is this correct? Does anyone know what the reasoning is behind this behavior?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Although not particularly well documented in this respect, the AssertEqualsTable routine only looks at the data in the table - not that the columns are the same. To check whether the table structures are the same, use AssertResultSetsHaveSameMetaData. I wrote a bit about this in this article.

You can of course run both in the same test, and the test will only pass if both checks pass.

I guess the reason for the split would be because there may be rare instances where you care about either the data or the metadata being consistent for your test, but not both.

share|improve this answer
Thanks for the reply Dave. I was aware of AssertResultSetsHAveSameMetaData and had considered writing my own AssertEqualsTableExact that calls this proc. before doing AssertEqualsTable. Thanks for the link to your article as well. I have read it in the past and found some of your tips to be very helpful. –  Andrew Jul 19 '13 at 6:06
Thanks Andrew, I'm glad you found the article helpful. –  DaveGreen Jul 19 '13 at 10:49
When I mentioned this to one of the guys I worked with, I got what I suspect is a more accurate answer to this question. I was looking at the problem the wrong way. I basically got stumped by the fact that I was removing columns from the #Expected table. A more likely answer as to why you don't need to have all the columns in the #Expected table is: if you have a test that uses AssertEqualsTable to compare #expected and myTable at a point in time and then some time later you add a new column to myTable, you would end up with a failing test if AssertEqualsTable compared columns as well –  Andrew Aug 15 '13 at 12:13
add comment

Your Answer


