tSQLt中AssertEqualsTable的工作原理是什么？

Great question! Let me clear up the confusion: AssertEqualsTable does NOT use the first column as a join condition to validate remaining columns. Instead, it operates by comparing two tables as entire sets of rows, following these key steps:

• First, validate schema matching: Before checking row content, it verifies that both tables have identical column counts, column names, data types (including precision, scale, and length), and nullability. If the schemas don't match exactly, the test fails immediately with a schema mismatch error.
• Compare row sets as whole units: It treats each row as a single, complete entity. To find differences, it uses SQL's EXCEPT and INTERSECT operators:
  • It checks for rows that exist in the expected table but not in the actual table
  • It also checks for rows that exist in the actual table but not in the expected table
  • If both of these result sets are empty, the tables are considered a match.
• Row order doesn't matter (by default): Since SQL tables are inherently unordered sets, AssertEqualsTable doesn't care about the physical order of rows in either table. If you need to enforce row order in your test, use AssertEqualsTableOrdered instead—this command requires rows to appear in the exact same sequence in both tables (you'll usually want to sort both tables before passing them to this command).

Quick Example to Illustrate #

Suppose you have two tables:

• ExpectedTable: Has rows (1, 'Apple'), (2, 'Banana')
• ActualTable: Has rows (2, 'Banana'), (1, 'Apple')

AssertEqualsTable will pass, because the set of rows is identical—even though the order is reversed. If ActualTable had a row (1, 'Orange') instead, the test would fail, because that complete row doesn't exist in ExpectedTable.

内容的提问来源于stack exchange，提问作者variable