When I was thinking up names for this blog, I had a hard time not using the name my husband uses for his fantasy baseball team: The Stellato Heels. It’s great, isn’t it? I can boast about how fabulous it is because I didn’t think of it. I had great visions for the logo. Just imagine a glamorous high heel with some sparklies and a splash of color. Unfortunately, I’m not one for heels (or sparklies if we’re being honest). Don’t get me wrong, I like shoes. I have a lot of pairs, but I don’t wear heels unless I’m pretty dressed up, and that only happens about twice a year. So, as much as I loved the name, it didn’t really represent me.
The name I chose in the end, The SQL Sequel, I loved for two reasons. First, I love movies. When I was a kid we had one of the first VCRs in the neighborhood, and my dad used to record movies on tape (usually three on a tape) that he numbered and labeled. Then he had this flip-top address book that listed each movie on the appropriate letter page, along with the tape number. The tapes were stored on shelves in alphabetical order. I now realize these were my first clustered and non-clustered indexes.
The second reason the blog title is very “me” is because I am never done with what I learn. There is always another detail to learn for any concept. I knew it wouldn’t take a long for a sequel blog post to come up, and I was right; this one’s the first.
In September’s TSQL Tuesday post I blogged about a non-sargable where clause, which forced a full index scan. My solution was to create a new computed column with the function, along with a non-clustered index, and use that. I wasn’t thrilled with that solution because I thought I then had to change my query to use the computed column in my where clause.
I was wrong.
If you read through the comments of that post, you’ll see the great exchange I had with Rob Farley ( blog | Twitter ) and Paul White ( blog | Twitter ). Paul is my new favorite Kiwi (why was I not following him on Twitter?) and he also did a quick review of this post for me (thank you Paul!). In my initial testing, after I created my computed column and its index, I re-ran the last query:
{code}SELECT LastName
FROM Person.Contact
WHERE RTRIM(LastName) LIKE ‘Smith%'{/code}
And it did a full scan on the IX_LastName index:
I didn’t test my original query, because I thought I knew what it would do. But Paul pointed out that if I ran my original query:
{code}SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE RTRIM(LastName) LIKE ‘Smith%’;{/code}
it actually used the IX_LastName_Computed index:
Those peeps that write the Optimizer code know what they’re doing. Even though I’m querying LastName in the where clause, the Optimizer knows the information I really need (because of the RTRIM) is in the IX_LastName_Computed index, and it seeks it. So why did my query with only LastName as the selected column use IX_LastName? It’s all about I/O.
The original query (all three columns selected) is 228 reads. The modified query (LastName only) takes only 57 reads; it’s doing the full scan because it’s fewer reads than the seek. If I try to force the first query with the regular index:
{code}SELECT FirstName, LastName, EmailAddress
FROM Person.Contact WITH (INDEX(IX_LastName))
WHERE RTRIM(LastName) LIKE ‘Smith%’;{/code}
It’s 282 reads. To quote Paul,
“…what you’re seeing is normal cost-based optimization. With both IX_LastName and IX_LastName_Computed available, the costing model produces a lower cost for the scan of IX_LastName, because it avoids a key lookup.”
And as Paul also pointed out to me, this query:
{code}SELECT LastName_Computed
FROM Person.Contact
WHERE RTRIM(LastName) LIKE ‘Smith%’;{/code}
will also use the IX_LastName_Computed index:
Aside from learning more about the optimizer, the biggest thing that I took away from this is to try every permutation, even if you think you know what’s going to happen. I think you learn more when what you’re expecting doesn’t happen.
A huge thank you to Paul and Rob for their help in producing the first true SQL Sequel on my blog! I love that I am learning something new all the time. I am constantly reminded of how much I don’t know. Some days it frustrates me, other days I remember that it’s just life, and it’s about the journey, not the destination.
