Sometimes you have to update a query, a complex one, checking for data on a quite large database. The query is right, it is just performing poorly. How can you know if the new one you are developing returns the same results as the old one (but faster)?
SHORT ANSWER
There is a simple way in SQL Server (and in almost every database) to check if two queries returns the same result: command EXCEPT.
Obviously you can use this command to debug only if your query is not SO slow...Let's say, 30 seconds or one minute, and you want to reduce it to one second or less. If you have a 3-hour query, maybe you'd better use an ad-hoc tool. Unless you want to wait 3 hours between every test ;) .
If you already know command UNION, syntax is exactly the same:
SELECT [FIELDS] FROM [TABLE-1]
EXCEPT
SELECT [FIELDS] FROM [TABLE-2]
The result of this query is a table containing every row that is in TABLE-1 but not in TABLE-2 . This in my opinion is invaluable for debugging purposes. You can quickly find differences between tables and adjust your query accordingly. For more informations: MSDN - Except .
Some tips:
- The two queries must have the same number of columns, otherwise the command will return an error
- For the query to be useful, fields must be of the same datatype (Varchar, Datetime, ecc) and especially in the same order
- NULL is a valid compare type! I spent half an hour having as a result the entire source table, not noticing that in one table I had 0s and in the other I had NULLs . (Well, maybe I was a bit dumb, but hey, maybe this helps someone else)
MORE IN DEPTH
In fact,
EXCEPT is SQL Server equivalent of Complement operation in Set Theory. For those who don't know - or don't remember - what I'm talking about, here is a very simple and basic explanation:
Set Theory.
EXCEPT is one of the three basic commands to operate with tables, with INTERSECT and UNION .
From a mathematical point of view, a table is a set. If you have to do some operation between two tables, basic rules and operations are the same as in Set Theory. And at the same time, basic operations of sets are the same as in Boolean Algebra.
On these assumptions, here is a simple equivalence between SQL Commands, Set Operators and Boolean Operator:
- EXCEPT - Complement - NOT
- UNION - Union - OR
- INTERSECT - Intersection - AND
If you feel more comfortable to reason using Set Operator or Boolean Algebra in comparison to SQL commands, now you know how to do it.
As a post scriptum of this post, just remember that almost anything in Computer Science derives from Math, one field or another. You can learn how to program and how to manage a database nonetheless, but knowing at least something about the maths underlying makes everything easier...and more interesting!
USEFUL LINKS