TSQL Execution Plan Operator - Filters

The Filter operator in the execution plan scans the input table and returns only those rows that satisfy the filter expression specified in the predicate. In general, the predicate is evaluated at the storage engine level, but sometimes the predicate in your query can’t be pushed at the storage engine level and then you’ll see a filter operator instead. In this video, we’ll be going to learn about how this Filter operator works.

In this session, we’ll be going to learn about the next operator of the T-SQL execution plan called a Filter. The Filter operator in the execution plan scans the input table and returns only those rows that satisfy the filter expression specified in the predicate. For example, if you have some predicates in the WHERE clause, then it can be evaluated at the leaf level of your table or at the index access operations.

Essentially the predicate is related to a distorted engine level. But sometimes the predicate in your query can’t be pushed at the storage engine level and then you will see a filter operator instead. Filter operators are often used for non-sargable predicates or when the predicates are too complicated to be pushed effectively to the index excess operations. Sometimes you can’t prevent it. If you have a Group By Having clause in your query and you cannot actually apply that Having clause until the aggregation has occurred.

The query optimizer uses the filter operator to return the records that match the Having clause predicate at the last stage. If you see a filter in your execution plan, then it might be for a good reason, like calculating things you don’t currently know the answer to. Sometimes you will see this operator because of your less efficient query.

For the Filter operator demo, let’s start off with this query where I joined two tables, product and product category with the product Category ID column. Here, I want to find a product whose category is Bikes. Now execute the query.

SELECT category Name, product Name AS ProductName

FROM Production.Product

INNER JOIN Production.ProductCategory AS Category ON product.ProductSubcategoryID = category.ProductCategoryID

WHERE category.Name = ‘BIKES’

T SQL Filters

When we look at the execution plan, we can see an index seek operation for the product category table and an Index scan operation for the product table. You might not see the additional filter operator because this predicate gets pushed to the operator at the leaf level itself. We have an index seek for the product category table and an index scan for the product table.

In the next query, I want to retrieve the count of products by their category. For this reason, I have to use the Group By clause by its category name. Execute this query. In the execution plan, now this time you can see that we have a slightly different execution plan than the above query. This time we have a stream aggregate operator but we still didn’t have a filter operator.

T SQL Filters

Uncommented the Having clause. This Having clause will retrieve only those categories whose product category count is greater than two. Execute the query again.

In the execution plan here, we can see a Filter operator. We have this Filter operator because we have a Group By clause. Once the results are aggregated, we have a predicate against those group results. When I hover over this Filter operator, you can see our predicate which is evaluated against those group results.

For more videos and articles visit:

Useful Links:

TSQL Execution Plan Operator - Filters

For more videos and articles visit:

Useful Links:

👉 GET YOUR FREE API KEY for PDF.co

✅ ON-PREMISE SDK FREE TRIAL:

🔎 SDK DOCUMENTATION:

🔑 WEB API DOCUMENTATION :