tSQLt.当涉及大数据集时，AssertEqualsTable需要数小时才能完成

Question

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

我正在比较预期的表格和实际的表格。这个表有100多万条记录，如果80万条记录与20万条记录不匹配，那么tsqlt将在每个失败的行下面绘制，需要数小时才能完成。如何避免tsqlt在输出中显示不匹配的记录？

这些表格将导致以下故障消息：

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

Answer 1

100000行远远超出了单元测试的合理要求。

您应该能够用更少的行来测试所有场景。

但是，如果tSQLt.AssertEqualsTable打印出潜在的巨大字符串时不能满足您的需求，您可以自己进行检查和断言，例如如下所示(假设Actual和Expected具有相同的列模式定义(。

DECLARE @expectedRows INT,
@actualRows   INT,
@expectedChk  INT,
@actualChk    INT;
SELECT @expectedRows = COUNT(*),
@expectedChk = CHECKSUM_AGG(binary_checksum(*))
FROM   Expected
SELECT @actualRows = COUNT(*),
@actualChk = CHECKSUM_AGG(binary_checksum(*))
FROM   Actual
EXEC tSQLt.AssertEquals
@expectedRows,
@actualRows,
'Mismatched rowcount between expected and actual'
EXEC tSQLt.AssertEquals
@expectedChk,
@actualChk,
'Mismatched checksum between expected and actual'
--Row count the same and checksum the same. Do more rigorous check.
IF EXISTS(SELECT *
FROM   (SELECT 1 AS [🗑️], *
FROM   Expected) E
FULL JOIN (SELECT 1 AS [🗑️], *
FROM   Actual) A
ON EXISTS(SELECT A.*
INTERSECT
SELECT E.*)
WHERE  A.[🗑️] IS NULL
OR E.[🗑️] IS NULL)
EXEC tSQLt.Fail
'Mismatched row content between expected and actual';