Using EXCEPT and INTERSECT to Validate SQL Server Query Tuning

This article explains how the EXCEPT and INTERSECT T-SQL operators can help compare query results when tuning SQL Server queries, stored procedures, and views.

Recently, a customer requested that I optimize several SSRS financial reports that frequently took between one and three minutes to complete.

These reports were driven by complex code involving user-defined functions, multiple stored procedures, and deeply nested views—some up to six levels deep.  Several views employed the UNION ALL operator to combine queries, which often led to inconsistent results once the queries were executed individually.  This was necessary because some queries had to be moved outside critical paths to reduce complexity and improve performance.

For example, one of the original views combined four queries using UNION ALL.  When I saved each query’s output to temporary tables, I discovered inconsistent data types and precisions among columns.  This was especially problematic for decimal and float values, where rounding differences produced subtle discrepancies that caused IF statements to execute in unexpected ways and led to altered values.  Inconsistent numeric precision across permanent and temporary tables further compounded the issue.

Execution paths varied.  Some reused the same stored procedures and views, while others traversed entirely different paths.  These inconsistencies made it difficult to identify which components were responsible for the poor performance.

To diagnose the performance problems, I used Extended Events to capture completed query events and identify which stored procedures and functions consumed the most resources.  I also created custom scripts to analyze object dependencies, replicating the functionality of SSMS’s View Dependencies feature.

Given the complexity, I realized that validating all intermediate and final result sets was essential to ensure that tuning changes did not alter any report results.  To support this validation, I saved interim and final result sets into tables for direct comparison.

For these comparisons, the EXCEPT and INTERSECT operators proved invaluable.  As Microsoft’s documentation explains, EXCEPT returns rows from the first query that do not exist in the second.

For simple result sets, the following comparison worked well:

SELECT * FROM Table1
EXCEPT
SELECT * FROM Table2

However, in many cases, EXCEPT returned all rows or produced extra rows that existed only in one table.

The issue involved precision differences. Small variations—such as 10.599998 vs. 10.59999900001—caused EXCEPT to treat values as unequal, even though they were functionally identical for reporting purposes.  This problem was especially common when comparing decimal, float, or real columns with different definitions across tables.

To resolve this, I normalized the precisions used in the queries input to the EXCEPT operator.  The primary database table used decimal(38,10) for key monetary columns, while others used decimal(38,20), REAL, or FLOAT.  Casting values to decimal(38,10) aligned precision and eliminated false mismatches:

SELECT CAST(ColumnA AS decimal(38,10)) AS ColumnARounded, ColumnB, ColumnC
FROM Table1
EXCEPT
SELECT CAST(ColumnA AS decimal(38,10)) AS ColumnARounded, ColumnB, ColumnC
FROM Table2

This ensured accurate comparisons when all the non-numeric columns were equal.

While troubleshooting, I also used INTERSECT to confirm which rows were identical.  This helped isolate discrepancies by identifying the few rows that did match.

Conclusion

EXCEPT and INTERSECT are powerful tools for validating that SQL Server code changes do not affect query results.  However, developers must account for data type and precision differences to ensure reliable comparisons.  Normalizing values—particularly numeric precision—is essential when using these operators to verify consistency.

Using these techniques, I reduced execution times significantly, as shown in the table below.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!