SQL Server execution plan Filter Operator

Hi Geeks,

SQL Server execution plan filter operator scans the input and return only those rows that satisfy filter expression specified in predicate. Let’s see this operator in action.

USE [AdventureWorks2012]

SELECT Address.PostalCode, COUNT(Address.PostalCode) AS PostCodeCount
FROM Person.Address
GROUP BY Address.PostalCode
HAVING COUNT(Address.PostalCode) > 1

HAVING clause resulted a FILTER operator in execution plan, and this helps to limit the output to those values of the column PostalCode that has higher value than 1. The query plan shows very clearly that FILTER operator is applied last to filter out record set.

By looking at the Hash Match operator, actual number of rows are 661 and in the Filter operator it is 403.

Though we are able to limit record set by adding a HAVING clause in query statement but this adds to the resources needed to produce query results as HAVING clause comes into play only after aggregation and this hurts performance.

Happy Learning!

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook