tSQLt.AssertEqualsTable [@Expected = ] 'expected table name' , [@Actual = ] 'actual table name' [, [@FailMsg = ] 'message' ]
[@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
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.
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.
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:
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 |
Is there a way to test a table with a column that represents the date and time the row was last updated?
In most of the stored procs in our application GETDATE() is used to update the date and time last updated.
As this will be set at runtime I can't see how I can set a value for the column in the 'Expected' table.
Does anyone have any suggestions?
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.
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?
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???
There are two ways to test timestamps.
The cleaner solution is to not (ever) call getdate directly, but instead call your own function that returns the current date and time. If you make that an inline-table-valued-function the performance impact is minimal. In your tests you can then just fake that function to return a fixed timestamp.
The second option is to take the current time directly before and after the execution of your tested code (inside the test). Then you can just confirm that the set time is between those two timestamps. This is less accurate but gets you pretty close. It is also the only way to test that wrapper function that you need if you go with the first option.
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.
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.
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.
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.