wetmatter nonsense

Today I was working on a SELECT query, nothing special or anything to get excited about. Looking through a single table for a few pieces of data. For this example I will refer to the Northwind database.

 
SELECT [ProductID] 
       ,[ProductName] 
       ,[SupplierID] 
       ,[CategoryID] 
       ,[QuantityPerUnit] 
       ,[UnitPrice]
       ,[UnitsInStock]
       ,[UnitsOnOrder]
       ,[ReorderLevel]
       ,[Discontinued]
 FROM [Northwind].[dbo].[Products]
 WHERE (CategoryID = 3) OR (UnitPrice = 14.00) OR (UnitsInStock > 100) 

Looks fairly straight forward right and it is. I mean there is nothing wrong with it. I can execute it and it returns 25 rows exactly what I was searching for. Well I decided to check out the Estimated Execution Plans (CTRL+L) while you are in SSMS to see what it looked like.

One thing I did not notice until now was the fact I can place the cursor over the lines or data flow arrows and get a little more detail about what is being returned. I knew that hovering over the icons returned some information about each, but I really didn't know what the data represented.

Then I remembered reading a post a while back that said it is better in this particular scenario to use a UNION ALL instead. Matter of fact here is the link: donetheaven.com. I love books marks! So the re-write would look like the following.

 
SELECT [ProductID]
       ,[ProductName]
       ,[SupplierID]
       ,[CategoryID]
       ,[QuantityPerUnit]
       ,[UnitPrice]
       ,[UnitsInStock]
       ,[UnitsOnOrder]
       ,[ReorderLevel]
       ,[Discontinued]
 FROM [Northwind].[dbo].[Products]
 WHERE (CategoryID = 3)
 UNION ALL
 SELECT [ProductID]
       ,[ProductName]
       ,[SupplierID]
       ,[CategoryID]
       ,[QuantityPerUnit]
       ,[UnitPrice]
       ,[UnitsInStock]
       ,[UnitsOnOrder]
       ,[ReorderLevel]
       ,[Discontinued]
 FROM [Northwind].[dbo].[Products]
 WHERE (UnitPrice = 14.00)
 UNION ALL
 SELECT [ProductID]
       ,[ProductName]
       ,[SupplierID]
       ,[CategoryID]
       ,[QuantityPerUnit]
       ,[UnitPrice]
       ,[UnitsInStock]
       ,[UnitsOnOrder]
       ,[ReorderLevel]
       ,[Discontinued]
 FROM [Northwind].[dbo].[Products]
 WHERE (UnitsInStock > 100)  

This query returned 27 rows and the execution plans for this turned out quite different.Take a look.