Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Validate Your Refactored SQL Queries with a New SQL Server 2005 Operator : Page 2

Prove that your refactored SQL query returns the expected results using the EXCEPT operator, new in SQL Server 2005.


advertisement
Meet the EXCEPT Operator
The EXCEPT operator is a SQL language enhancement introduced in SQL Server 2005. Like the UNION operator, EXCEPT combines two SELECT statements into a single results set. The same rules about number of columns and compatible data types apply equally to UNION and EXCEPT. EXCEPT takes the output from the first (or left-hand side—LHS) SELECT and compares it with the second (or right-hand side—RHS) SELECT and returns distinct rows from the LHS SELECT that are not present in the RHS SELECT. Think of this processing as similar to the way that a UNION ignores duplicate rows.

When I first read about the EXCEPT operator, I thought it was cool but I could not actually find a valid reason to use it in production code. In most cases, you can obtain the same results using joins and WHERE clauses. However, EXCEPT becomes very useful when validating a refactored query.

Validate Your Refactored Query
In this example, you use EXCEPT to verify that your refactored query returns the same results, by checking whether the query using EXCEPT returns no rows. If it does, you will know that the queries return the same results:



SELECT * FROM TableA WHERE EXISTS (SELECT * FROM TableB WHERE TableA.id = TableB.refid) EXCEPT SELECT TableA.* FROM TableA LEFT OUTER JOIN TableB ON TableA.id = TableB.refid

Here are the results:

id textfield ----------- -------------------- (0 row(s) affected)

Great, this returns no rows. That must mean the SQL queries are returning the same results, right? Wrong! EXCEPT works uni-directionally, so it will display only rows from the LHS query that are not in the RHS query. If the RHS query is returning rows that are not in the LHS query, EXCEPT won't return these rows. Therefore, you can test fully only by running the query reversed as well:

SELECT TableA.* FROM TableA LEFT OUTER JOIN TableB ON TableA.id = TableB.refid EXCEPT SELECT * FROM TableA WHERE EXISTS (SELECT * FROM TableB WHERE TableA.id = TableB.refid)

Here are the results:

id textfield ----------- -------------------- 3 Row three 5 Row five (2 row(s) affected)

This query produces a list of results indicating that when you refactored the query, you changed the results. It also tells you which extra rows are being returned.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap