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

FilterGrphPlan

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.

HashMatchFilter

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

2 Comments on “SQL Server execution plan Filter Operator”

Leave a Reply

Your email address will not be published. Required fields are marked *