T-SQL Tuesday #25 - Tips and Tricks

13 December 2011

It is T-SQL Tuesday once again, hosted by Allen White (b / t). The theme for this T-SQL Tuesday is Tricks.

One of my favorite tricks is to use INTERSECT when I have to rewrite a SELECT query, to make sure I am returning the same data.

Let’s say I have a slow query that returns 1640 rows:

I refactor it for speed, and make sure it still returns 1640 rows:

How am I sure my new query returns the same data? I use INTERSECT, and make sure the intersect-ed query returns the same number of rows as the original query:

“Success is concrete. Just like the Mafia.”

Now I know my changes have not affected what data is returned. Success!

I also have a more concrete example, using the AdventureWorks2008 R2 database, and Example.sql .