Functions as predicates and SARGable queries

Question

Functions as predicates and SARGable queries

Uwe Ricken

Hall of Fame

Points: 3142
More actions
August 25, 2014 at 9:37 pm

#298855

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!)
August 26, 2014 at 4:58 am

This was removed by the editor as SPAM

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

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

rishi-445055 SSC Veteran Points: 274 More actions · Answer 1

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: 3142 More actions · Answer 2

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 More actions · Answer 3

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: 3142 More actions · Answer 4

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 More actions · Answer 5

rishi-445055

SSC Veteran

Points: 274

August 26, 2014 at 12:20 am

#1740921

Thanks Uwe.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 6

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: 104773 More actions · Answer 7

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: 3142 More actions · Answer 8

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 More actions · Answer 9

Gazareth

One Orange Chip

Points: 27737

August 26, 2014 at 2:59 am

#1740950

Nice question, thanks

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 10

Iulian -207023

SSCertifiable

Points: 7639

August 26, 2014 at 3:02 am

#1740951

yes, nice question, thanks for it.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 11

Iulian -207023 (8/26/2014)
yes, nice question, thanks for it.

+1 - Nice explanation too

Thanks

Umer Akram SSC-Insane Points: 22209 More actions · Answer 12

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

Ed Wagner SSC Guru Points: 287024 More actions · Answer 13

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

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions