February 9, 2010 at 3:14 pm
I had a query that wasn't using an appropriate index because of an implicit cast, and although I identified and fixed it, it's raised a number of questions about what's actually going on.
First of all, what's the rule for whether an implicit cast is SARGEable*? Tests with comparing char and nchar values to a varchar seem to indicate that it's based on data type precedence. However smalldatetime is comparable to datetime and visa versa without breaking index usage. (the grammar of sargability escapes me here...)
Also, I have a table-valued function that returns a substring of a varchar as a column. That substring column is not sargable to a varchar index column, but is if cast to a varchar. Even weirder, a substring of a varchar without a udf is sargable. What's up with that?
*for people new to the term, SARG (Search ARGument) is a concept relating to index usage for the filters in a given query, so if you have an index on a table on column a (varchar) the where clause "a = '1234'" is SARGable, but "a = 1234" or "b = 'foo'" are not. I'm sure someone else has a link to a better explanation than that.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
February 9, 2010 at 9:06 pm
On your first question of the datetime/smalldatetime... you need to look at how the underlying data is stored. Both are stored as a real number... for instance,
select convert(datetime, 12345.785215)
returns 1933-10-20 18:50:42.573
So, since these are actually a real data type, they can both use the same index.
About the TVF... this is interesting. I'm wondering if this is dealing more with the fact that table variables cannot have statistics, so the optimizer always assumes one row, which can frequently produce a bad execution plan. What do you have that implies that the TVF is producing results that are not sargable with the existing indexes?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 9, 2010 at 10:08 pm
weitzera (2/9/2010)
First of all, what's the rule for whether an implicit cast is SARGEable*?
Pretty much the same as explicit casts.
Can you post the code of that TVF and the exec plans both showing the index usage and the no-index usage. Hard to say what's happening without the exec plan.
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
February 10, 2010 at 9:40 am
Thanks for looking at this,
That's an interesting suggestion about the statistics on the tvf, I just assumed that because the query plan looked like the problem was with SARGability, that it was.
I'll post the specific code and query plans in a bit. Got some actual work to do first 🙂
To step back a bit, what I'm looking at are cases where letting the compiler implicitly cast the arguments results in an index scan instead of an index seek.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
February 10, 2010 at 11:17 am
weitzera (2/10/2010)
To step back a bit, what I'm looking at are cases where letting the compiler implicitly cast the arguments results in an index scan instead of an index seek.
Most cases where it's the column that gets the conversion applied to it. Same as if it was an explicit cast.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply