Post reply

Option(Recompile) with indexed Persisted columns and non-sargable predicate does a seek.

  • November 2, 2016 at 6:12 am

    #400977

    Here is something I found this morning.

    Since indexed views are used in queries that do not explicitly reference them, I wanted to see if the same was true for indexed persisted columns.

    What I was hoping for was a solution where we did not have to modify the code, but seems like we have to include option(recompile) in the code for it to work.

    The query references the persisted formula and not the persisted column name.

    There are now so many exception to the rule that [operations on columns in a where clause is not sargable] that I am scared to make any claims about the sargabililty of a query.

    Experiment below.

    btw, no question here, just a tidbit of info I assume many people don't know.

    USE [AdventureWorks2016CTP3]

    GO

    ALTER TABLE [Sales].[OrderTracking] ADD TenTimesOrderTracking

    AS 10*[OrderTrackingID] persisted

    GO

    create index IX_TenTimesOrderTracking on [Sales].[OrderTracking](TenTimesOrderTracking)

    GO

    set statistics io on

    GO

    select OrderTrackingID from [Sales].[OrderTracking]

    where 10*[OrderTrackingID] = 3210 --SCAN 330 reads

    --Compare above and below.

    --I think I need to add a connect to get the SQL Devs an increase in their salary cos this is awesome stuff.

    select OrderTrackingID from [Sales].[OrderTracking]

    where 10*[OrderTrackingID] = 3210 option(recompile) --SEEK 2 reads

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • November 2, 2016 at 5:41 pm

    #1910258

    Same kind of trick SQL can do with indexed views. With the views it can use the indexed view even if it isn't mentioned in the query, if the query form is the same.

    Sargability rules haven't changed, it's just here that the QO can now figure out that function(column) is indexed (doesn't have to be persisted) and switch names behind the scenes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply