Filtered Indexes and Dynamic SQL

Last Updated 7 years ago

I’ve been told that an attendee at the PASS Summit pre-conference event asked about using dynamic SQL to get around some of the problems with filtered indexes. I’m not entirely sure what the question was, but it did give me the opportunity to play around with filtered indexes and write some simple demo code to illustrate just the kind of shenanigans that you can get up to.

Creating a Filtered Index

The first step for our demo is to create a filtered index. Grab yourself a copy of Adventure Works and get ready for some fun. Here’s our filtered index:

CREATE INDEX jp_filters_not_offline 
    ON Sales.SalesOrderHeader (OrderDate) 
        INCLUDE (SalesOrderID)
        WHERE OnlineOrderFlag = 1 ;
GO

CREATE INDEX jp_filters_not_offline

ON Sales.SalesOrderHeader (OrderDate)

INCLUDE (SalesOrderID)

WHERE OnlineOrderFlag = 1 ;

That creates our initial filtered index. We want to search by OrderDate and find only those orders that were placed online. This is a realistic index to create in the AdventureWorks database, too – there are 27,659 online sales in my copy of AdventureWorks, but only 3,806 offline sales.

Want to prove that the index works? Run this SQL and take a look at the execution plan:

SELECT  SalesOrderID
FROM    Sales.SalesOrderHeader AS soh
WHERE   OnlineOrderFlag = 1 ;

SELECT SalesOrderID

FROM Sales.SalesOrderHeader AS soh

WHERE OnlineOrderFlag = 1 ;

There’s No Problem Here!

Let’s create a pair of stored procedures:

CREATE PROCEDURE jp_online_only_date
(
    @OrderDate DATETIME
)
AS
BEGIN
    SELECT SalesOrderID
    FROM Sales.SalesOrderHeader AS soh
    WHERE OnlineOrderFlag = 1 AND OrderDate &gt; @OrderDate
END
GO

CREATE PROCEDURE jp_online_only_date_2
(
    @OrderDate DATETIME
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'';

    SET @sql += 'SELECT SalesOrderID
FROM Sales.SalesOrderHeader AS soh
WHERE OnlineOrderFlag = 1 AND OrderDate &gt; '''
+ CAST(@OrderDate AS NVARCHAR(MAX)) + ''';'

    EXEC sp_executesql @sql;
END

CREATE PROCEDURE jp_online_only_date

(

@OrderDate DATETIME

)

BEGIN

SELECT SalesOrderID

FROM Sales.SalesOrderHeader AS soh

WHERE OnlineOrderFlag = 1 AND OrderDate > @OrderDate

END

CREATE PROCEDURE jp_online_only_date_2

(

@OrderDate DATETIME

)

BEGIN

DECLARE @sql NVARCHAR(MAX) = N'';

SET @sql += 'SELECT SalesOrderID

FROM Sales.SalesOrderHeader AS soh

WHERE OnlineOrderFlag = 1 AND OrderDate > '''

+ CAST(@OrderDate AS NVARCHAR(MAX)) + ''';'

EXEC sp_executesql @sql;

END

If you were to run both of these, you’d notice that they both make use of the filtered index. What gives?

Both of these stored procedures are able to take advantage of the filtered index because the filter condition (OnlineOrderFlag = 1) is matched by predicate in the query. In other words – SQL Server can easily make this match.

The Problem with Parameters

There’s a problem, though. When we switch over to using a parameter, SQL Server isn’t able to make effective use of the filtered index. Check it out:

CREATE PROCEDURE jp_online_only_params
(
    @OrderDate DATETIME,
    @OnlineFlag BIT
)
AS
BEGIN
    SELECT  SalesOrderID
    FROM    Sales.SalesOrderHeader AS soh
    WHERE   OnlineOrderFlag = @OnlineFlag
            AND OrderDate &gt; @OrderDate ;
END 
GO

EXEC jp_online_only_params '2007-12-12', 0 ;

CREATE PROCEDURE jp_online_only_params

(

@OrderDate DATETIME,

@OnlineFlag BIT

)

BEGIN

SELECT SalesOrderID

FROM Sales.SalesOrderHeader AS soh

WHERE OnlineOrderFlag = @OnlineFlag

AND OrderDate > @OrderDate ;

END

EXEC jp_online_only_params '2007-12-12', 0 ;

Oh no! This procedure doesn’t use our filtered index any more. There’s got to be a way to trick SQL Server into using our filtered index. We could resort to an index hint, but that would mean we have to keep using the same index or keep re-using the name of that index. I’m not a big fan of that, so let’s think of something else.

What if we were to use string concatenation? That sounds like magic, it has a lot of syllables.

Hooray for Strings!

We can solve the problem by forcing SQL Server to see the literal value that we’re passing in:

CREATE PROCEDURE jp_online_only_strings
(
    @OrderDate DATETIME,
    @OnlineFlag BIT
)
AS
BEGIN
    DECLARE @sql AS NVARCHAR(MAX) = N'';

    SET @sql += 'SELECT SalesOrderID
FROM Sales.SalesOrderHeader AS soh
WHERE OnlineOrderFlag = ' + CAST(@OnlineFlag AS NVARCHAR(1));

    SET @sql += ' AND OrderDate &gt; '''
        + CAST(@OrderDate AS NVARCHAR(MAX)) + ''';'

    PRINT @sql

    EXEC sp_executesql @sql;
END

CREATE PROCEDURE jp_online_only_strings

(

@OrderDate DATETIME,

@OnlineFlag BIT

)

BEGIN

DECLARE @sql AS NVARCHAR(MAX) = N'';

SET @sql += 'SELECT SalesOrderID

FROM Sales.SalesOrderHeader AS soh

WHERE OnlineOrderFlag = ' + CAST(@OnlineFlag AS NVARCHAR(1));

SET @sql += ' AND OrderDate > '''

+ CAST(@OrderDate AS NVARCHAR(MAX)) + ''';'

PRINT @sql

EXEC sp_executesql @sql;

END

If you were to run that stored procedure, you’d get effective use of the filtered index. SQL Server sees the OnlineOrderFlag predicate as a literal value, matches that up to the index definition, and we’re off to the races.

There is one problem, though – because we’re also using a literal value for OrderDate, there could be significant bloat in the plan cache. SQL Server will create one execution plan for every possible combination of parameters that we pass in. In AdventureWorks there are 1,124 distinct values in the OrderDate column. That’s a lot of execution plans.

Avoiding Plan Cache Bloat

We want to avoid bloat in the execution plan cache. In order to do that, we’re going to need to parameterize part of the query but still leave the filter intact. That’s really easy to do:

CREATE PROCEDURE jp_online_mixed
(
    @OrderDate DATETIME,
    @OnlineFlag BIT
)
AS
BEGIN
    DECLARE @sql AS NVARCHAR(MAX) = N'';

    SET @sql += 'SELECT SalesOrderID
FROM Sales.SalesOrderHeader AS soh
WHERE OnlineOrderFlag = ' + CAST(@OnlineFlag AS NVARCHAR(1));

    SET @sql += ' AND OrderDate &gt; @order_date;'

    PRINT @sql

    EXEC sp_executesql @sql,
         N'@order_date DATETIME',
         @OrderDate;
END

CREATE PROCEDURE jp_online_mixed

(

@OrderDate DATETIME,

@OnlineFlag BIT

)

BEGIN

DECLARE @sql AS NVARCHAR(MAX) = N'';

SET @sql += 'SELECT SalesOrderID

FROM Sales.SalesOrderHeader AS soh

WHERE OnlineOrderFlag = ' + CAST(@OnlineFlag AS NVARCHAR(1));

SET @sql += ' AND OrderDate > @order_date;'

PRINT @sql

EXEC sp_executesql @sql,

N'@order_date DATETIME',

@OrderDate;

END

By adding the @OnlineFlag as just another string, but still parameterizing the @order_date we end up with only two execution plans. One plan will be created for each value of@OnlineFlag, but we’ll still get use of the filtered index. This is all but impossible to accomplish with forced parameterization and difficult to accomplish with regular parameteriziation (usually you have to resort to index hints).

Summing It Up

Dynamic SQL and filtered indexes are a great match. By carefully making use of dynamic SQL, you can coerce SQL Server into behaving well and providing you with an execution plan that uses the filtered index. Through fully parameterized dynamic SQL you can get good plan re-use and avoid SQL injection attacks. By making careful use of dynamic SQL and filtered indexes, you should be able to get performance out of SQL Server without having to resort to tricks like index hints or plan guides.

What You Can (and Can’t) Do With Filtered Indexes

How to Winterize Your Database

21 Comments. Leave new

Bhuvnesh
November 15, 2013 7:13 am

Why the proc uses filtered index in case of Dynamic sql.

Reply
- Jeremiah Peschka
  November 15, 2013 7:18 am
  
  By concatenating in the literal value of @OnlinrFlag, the optimizer sees the constant and is able to match it with the filtered index. Parameters and variables are like a cardboard box for values – they’re handy for moving a value around, but they make it impossible to see the actual value.
  
  Reply
Geri Reshef
November 18, 2013 4:53 am

I guess the point is that one of the benefits of dynamic SQL is to override the parameter sniffing problem, as well as by using With Recompile hint.

Reply
- Jeremiah Peschka
  November 18, 2013 8:20 am
  
  That’s exactly it. I like using dynamic SQL before I resort to a recompile hint. If I end up with a few variations of the plan, that’s better to me than having a large number of recompiles going on.
  
  It doesn’t suit all use cases, though.
  
  Reply
dave wentzel
November 18, 2013 9:42 am

Hey, awesome article. Another “issue” I found with filtered indexes is that they may not work with your data access technology. For instance, if your ORM uses non-default ANSI settings then you will actually see that not only is the index not used, but INSERT statements start throwing errors everywhere. I did a write up on this (http://www.davewentzel.com/content/gotcha-sql-server-filtered-indexes) with a repro script.

Reply
José
April 25, 2014 8:51 am

Excuse my literal English (Google translator gives what he can: P) …. My native language is Spanish, but I probably understand better the mash ….

Before performing the SP direct variables without using the dynamic or sp_executesql within the SP’s.

Also, I have used the techniques for the girl or Mrs. published as to recompile and go further in the high security provided by me and that before risking me for some SQL injection, I tested the plan optimized, and the use of cache, use filtered indexes, and boy did that cost reduction, run the CPU and agility in bringing the data has improved me.

Publication and clearly appreciate the attention to detail you better explain what at first glance appears to be something useful or understood in real life.

Best regards.

Reply
- wqw
  February 12, 2015 9:07 am
  
  That sucks big time! I thought that only indexed views were such a beast.
  
  Unfortunately the error message clearly lists all the problematic objects: “indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index”
  
  Reply
Susan Pace
November 19, 2014 8:47 am

I attended your Dynamic SQL session at SQL PASS and it was great! I am trying to find your demo examples.

Susan Pace

Reply
- Jeremiah Peschka
  November 19, 2014 9:10 am
  
  You can download the resources at http://www.sqlpass.org/EventDownload.aspx?suid=2798
  
  Reply
Tom Marshall
November 21, 2014 9:18 am

Great Post.

Reply
- Jeremiah Peschka
  November 21, 2014 9:26 am
  
  Thanks!
  
  Reply
Karthik
February 25, 2015 7:43 am

Hi brentozar team,

I really found this article very useful. Thanks.
Luckily, I am using literals inside SPs for Filtered indexes. 🙂

Karthik.

Reply
johan sebastian
August 11, 2015 10:56 am

I have gigantic tables. If my understanding about statistics is correct this could lead to performance reduction. If i create filtering index should i create same filtering statistics base on same predicate ? Do query optimizer reconizer this filtering statistics ? Thank you and please pardon me for my rude taste of word.

Reply
- Jeremiah Peschka
  August 11, 2015 12:12 pm
  
  If you create a filtered index, it will have a filtered statistics. There’s no need to create another. The optimizer will consider the filtered index and use the underlying filtered statistic during query optimization.
  
  Reply
Adam Gojdas
August 19, 2015 4:48 pm

Unfortunately using this type of pattern to concatenate variable data into the query string and create the query now opens us up to SQL injection. However unlikely in the scenario provided I could see the basis of this idea used and introduce such an attack surface. So take care in how and where you might use this.

Reply
- Jeremiah Peschka
  August 19, 2015 4:58 pm
  
  I’m curious how you plan to convert a BIT parameter into a a vector for a SQL injection attack. Can you share how you’d imagine that to work out?
  
  Reply
  - Adam Gojdas
    August 20, 2015 9:55 am
    
    Sorry, my comment misled you from my intended meaning. When I said “However unlikely in the scenario provided”, I meant that for your given example it was unlikely to be at risk.
    
    But if people apply the “pattern” or idea of concatenating variable data to create the query string without concern for the variable types and what data they may contain then it opens you up for sql injection. That is why I mentioned “So take care in how and where you might use this.”
    
    Hopefully this clears up what i was trying to convey.
    
    Reply
    - Kristen
      September 2, 2015 6:02 am
      
      It makes me nervous too … my immediate reaction whilst reading the article was that I would prefer to code
      
      SET @sql += ‘WHERE OnlineOrderFlag = ‘ + CAST(@OnlineFlag AS NVARCHAR(1));’
      
      as
      
      IF @OnlineFlag = 1
      SET @sql += ‘WHERE OnlineOrderFlag = 1;’
      ELSE
      SET @sql += ‘WHERE OnlineOrderFlag = 0;’
      
      (or some variation of that 🙂 )
      
      We do use deliberate, hard-coded, variations like this (even if they are just in comments) to differentiate query variations so they each get their own plan. But I’m no expert …
      
      Reply
Lucas
December 3, 2015 9:10 am

this article just saved me half a day of fruitless trial and error. Thanks!

Reply
DataSic
January 3, 2016 3:49 pm

Good explanation. One small bug – there’s:
EXEC jp_online_only_params ‘2007-12-12’, 0;
and above never uses designed index (WHERE OnlineOrderFlag = 1).

Reply
Clement Huge
October 12, 2018 7:17 am

You can even go to the next step with dynamically managing a filtered index 😉
For a client this was a real light saver!
http://clementhuge.over-blog.com/2017/02/the-dynamic-filtered-indexes.html

Reply