AssertEqualsTable

 

Syntax

tSQLt.AssertEqualsTable [@Expected = ] 'expected table name'
                      , [@Actual = ] 'actual table name'
                     [, [@FailMsg = ] 'message' ]

Arguments

[@Expected = ] expected table name
The name of a table which contains the expected results for the test. @Expected is NVARCHAR(MAX) with no default.
[@Actual = ] actual table name
The name of a table which contains the results from processing during the test. @Actual is NVARCHAR(MAX) with no default.
[@FailMsg = ] ‘message’
Optional. String containing an additional failure message to be used if the expected and actual values are not equal. @FailMsg is NVARCHAR(MAX) with a default of ‘unexpected/missing resultset rows!’.

Return Code Values

Returns 0

Errors Raised

Raises a ‘failure’ error if the contents of the expected table and the actual table are not equal.

Certain datatypes cannot be compared with AssertEqualsTable. If the tables being compared contain an unsupported datatype, the following error will be raised:

The table contains a datatype that is not supported for tSQLt.AssertEqualsTable. 

The following datatypes are known to be unsupported by AssertEqualsTable: text, ntext, image, xml, geography, geometry, rowversion and CLR datatypes that are not marked comparable and byte ordered.

Result Sets

None

Overview

AssertEqualsTable compares the contents of two tables for equality. It does this by comparing each row of the tables for an exact match on all columns. If the tables do not contain the same data, the failure message displays which rows could not be matched.

Examples

Example: AssertEqualsTable to check the results of a view
This test case uses AssertEqualsTable to compare the data returned by a view to an expected data set.

CREATE PROCEDURE testFinancialApp.[test that Report gets sales data with converted currency]
AS
BEGIN
    IF OBJECT_ID('actual') IS NOT NULL DROP TABLE actual;
    IF OBJECT_ID('expected') IS NOT NULL DROP TABLE expected;

------Fake Table
    EXEC tSQLt.FakeTable 'FinancialApp', 'CurrencyConversion';
    EXEC tSQLt.FakeTable 'FinancialApp', 'Sales';

    INSERT INTO FinancialApp.CurrencyConversion (id, SourceCurrency, DestCurrency, ConversionRate)
                                         VALUES (1, 'EUR', 'USD', 1.6);
    INSERT INTO FinancialApp.CurrencyConversion (id, SourceCurrency, DestCurrency, ConversionRate)
                                         VALUES (2, 'GBP', 'USD', 1.2);

    INSERT INTO FinancialApp.Sales (id, amount, currency, customerId, employeeId, itemId, date)
                                         VALUES (1, '1050.00', 'GBP', 1000, 7, 34, '1/1/2007');
    INSERT INTO FinancialApp.Sales (id, amount, currency, customerId, employeeId, itemId, date)
                                         VALUES (2, '4500.00', 'EUR', 2000, 19, 24, '1/1/2008');

------Execution
    SELECT amount, currency, customerId, employeeId, itemId, date
      INTO actual
      FROM FinancialApp.Report('USD');

------Assertion
    CREATE TABLE expected (
	    amount MONEY,
	    currency CHAR(3),
	    customerId INT,
	    employeeId INT,
	    itemId INT,
	    date DATETIME
    );

	INSERT INTO expected (amount, currency, customerId, employeeId, itemId, date) SELECT 1260.00, 'USD', 1000, 7, 34, '2007-01-01';
	INSERT INTO expected (amount, currency, customerId, employeeId, itemId, date) SELECT 7200.00, 'USD', 2000, 19, 24, '2008-01-01';

	EXEC tSQLt.AssertEqualsTable 'expected', 'actual';
END;
GO

Understanding the Output

When the two tables being compared contain different data, the results are displayed as a text table in the failure message. The first column of this text table (_m_) describes the result of the comparison. The symbol “<” indicates that the row was found in the Expected table but did not match anything in the Actual table. The symbol “>” indicates that the row was found in the Actual table but not in the Expected table. Finally, the symbol “=” indicates that the row was matched between the Expected and Actual tables.

For example, consider the following Expected and Actual tables:

Expected
col1 col2 col3
1 A a
2 B b
3 C c
Actual
col1 col2 col3
1 A a
3 X c

These tables would result in the following failure message:

failed: unexpected/missing resultset rows!
|_m_|col1|col2|col3|
+---+----+----+----+
|<  |2   |B   |b   |
|<  |3   |C   |c   |
|=  |1   |A   |a   |
|>  |3   |X   |c   |

See Also
AssertEqualsString
AssertEquals

 Posted by at 5:19 pm
7 comments
Ben Sala
Ben Sala

I've also been bit by the missing column. Thinking about it, it does make sense to do it this way, and a lot of my unit tests I can simplify because i'm attempting to make the same number/name of columns on my expected/actual. I knew metadata wasn't going to match (data types could be different), but I didn't know a missing column would still pass the test. It might be a good idea to just make a note of this functionality on the instructions page.

Danny
Danny

I have 2 tables with exact same metadata. I want to compare the data in it but i receive an out of memory exception .net error. Both tables are 220 Mb in size, and the AssertEqualsTable function eats about 5 GB of memory and then collapses. The system has 6 GB of memory. Does it really need *THAT* amount of memory?

Gary
Gary

alter procedure Gtest.[test tablecompare] as begin select 'a' tstr,123 tint into #expected select 'a' tstr into #actual -----------here 1 column less bt nbr of rows r same exec tsqlt.AssertEqualsTable '#expected', '#actual' end; go exec tsqlt.Run 'Gtest.[test tablecompare]' ---------------------so the result is--------------------------- +----------------------+ |Test Execution Summary| +----------------------+ |No|Test Case Name |Result | +--+---------------------------+-------+ |1 |[Gtest].[test tablecompare]|Success| ----------------------------------------------------------------------------- Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored. ----------------------------------------------------------------------------- My question is how is that success possible if we are really comparing two different tables???

Sebastian
Sebastian

tSQLt is a Unit Testing Framework and as such not designed to handle Huge amounts of data. AssertEqualsTable is one of the few table compare algorithms out there that do not require you to have a primary key specified. This is very helpful during unit testing. However, it comes at a price: The algorithm to make this work is fairly expensive. It takes a significant amount of memory and even more so time. In unit testing you should strive to write small fast tests that are based on just the data they need to prove their point. tSQLt provides the mechanisms necessary to make that approach feasible (e.g. tSQLt.FakeTable). So please write your unit tests with less data. That being said, 5GB sounds a little excessive. I will look into it.

Sebastian
Sebastian

I believe it was a conscious decision for AssertEqualsTable to not compare the metadata of the two tables. There is an AssertMetaDataEquals method for that purpose. I will look into it and post an update here.

Danny
Danny

Dear Sebastian, Thank you for your reply, i splitted the tables into smaller chuncks and only test a small amount of sample data of it. Thank you.

Andrew Russell
Andrew Russell

I have been caught out by this same issue before. I was writing a test to prove a column had been deleted from an existing table. I modified an existing test that used AssertEqualsTable and removed the column from the expected table and was a little distressed to notice that the test didn't fail as I had expected. On reflection this behaviour makes some good sense. If AssertTableEquals was to ensure that all columns in the actual table were required to be present in the expected table, then every time the table schema of the actual table changed all tests comparing expected and actual tables would fail.