Functions as predicates and SARGable queries

  • Uwe Ricken

    Hall of Fame

    Points: 3097

    Comments posted to this topic are about the item Functions as predicates and SARGable queries

    Microsoft Certified Master: SQL Server 2008
    MVP - Data Platform (2013 - ...)
    my blog: http://www.sqlmaster.de (german only!)

  • rishi-445055

    SSC Veteran

    Points: 274

    Hi,

    So if we make Code Nullable should not the query become non-sargeable, thereby meaning it does not use the Index ix_CCode, but making the column as Null still uses the index. Why is this so?

    Thanks

  • Uwe Ricken

    Hall of Fame

    Points: 3097

    rishi-445055 (8/25/2014)


    Hi,

    So if we make Code Nullable should not the query become non-sargeable, thereby meaning it does not use the Index ix_CCode, but making the column as Null still uses the index. Why is this so?

    Thanks

    Hallo Rishi,

    hopefully I understand your question correct. If the attribute [CCode] will be a NULLable one the SARGabe query will turn into a NONSARGable query because ISNULL has to pass every single row to check whether [CCode] IS NULL or IS NOT NULL. It is like

    [font="Courier New"]CASE WHEN [CCode] IS NULL

    .....THEN 1

    .....ELSE 0

    END = 1[/font]

    Whatever decision is made by the Query Optimizer - it will always use the index [ix_CCode] because this index will produce less IO than the clustered index (which contains the ZIP as additional attribute. The record length in the CI is 23 bytes whereas the record length in the NCI is 13 bytes! So ix_CCode is quite smaller than the CI and will produce less output than the CI.

    Microsoft Certified Master: SQL Server 2008
    MVP - Data Platform (2013 - ...)
    my blog: http://www.sqlmaster.de (german only!)

  • rishi-445055

    SSC Veteran

    Points: 274

    Hi Uwe,

    Yes you understood my question correctly and thanks for the explanation.

    One further question, how does one calculate the no. of bytes used up by CI/NCI. In our case CI=13 bytes and NCI=23 bytes, how do you arrive at this?

    Thanks

  • Uwe Ricken

    Hall of Fame

    Points: 3097

    Hi Rishi,

    a good (the best) starting point is "SQL Server Internals" from Kalen Delaney. On Page 309 you find a pretty cool explanation of the row structure. Furthermore you will get a good information about row structures from Paul Randal here:

    www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/[/url]

    For my example the calculation is as follows:

    Clustered Index

    4 Bytes for the record header

    4 bytes for the ID column (int)

    3 Bytes for the CCode column

    10 Bytes for the ZIP column

    2 Bytes for the number of columns

    1 Byte for the NULL bitmap

    ----------------------------------

    24 Bytes for the record (uups i calculated in my previous answer wrong - thought 4 bytes for CCode 🙁 )

    Due to the fact that no variable length column is present no additional bytes are used for them!

    NonClustered Index

    EVERY record has the same structure so you only have to subtract 10 bytes for the ZIP column from the above calculated size and you get the length of the record.

    NOTE: This is a simplified example because I don't take into consideration a UNIQUE constraint for the indexe(s). That would burst this thread 🙂

    Microsoft Certified Master: SQL Server 2008
    MVP - Data Platform (2013 - ...)
    my blog: http://www.sqlmaster.de (german only!)

  • rishi-445055

    SSC Veteran

    Points: 274

    Thanks Uwe.

  • Koen Verbeeck

    SSC Guru

    Points: 258928

    Great question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • TomThomson

    SSC Guru

    Points: 104767

    Uwe Ricken (8/25/2014)


    24 Bytes for the record (uups i calculated in my previous answer wrong - thought 4 bytes for CCode 🙁 )

    4?? Surely 2 ? 4 would have given 25, not 23.

    Nice question, anyway.

    Tom

  • Uwe Ricken

    Hall of Fame

    Points: 3097

    TomThomson (8/26/2014)


    4?? Surely 2 ? 4 would have given 25, not 23.

    Nice question, anyway.

    AAARGH - I'm a scatterbrain.

    You are right - it should be 2 not 4!

    To early in the morning 🙂

    Microsoft Certified Master: SQL Server 2008
    MVP - Data Platform (2013 - ...)
    my blog: http://www.sqlmaster.de (german only!)

  • Gazareth

    One Orange Chip

    Points: 27737

    Nice question, thanks

  • Iulian -207023

    SSCertifiable

    Points: 7507

    yes, nice question, thanks for it.

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Iulian -207023 (8/26/2014)


    yes, nice question, thanks for it.

    +1 - Nice explanation too

    Thanks

  • twin.devil

    SSC-Insane

    Points: 22208

    Very nice question as well as the explanation. Thanks for sharing

  • This was removed by the editor as SPAM

  • Ed Wagner

    SSC Guru

    Points: 286958

    Nice question. And you presented the answer clearly. Thanks.

Viewing 15 posts - 1 through 15 (of 28 total)

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