Will SQL Server Always Do Index Seeks on Numbers?

I often get the question of “Will SQL Server do an index seek with ____?”

Another way to phrase it is, “Is this query sargable?” We throw the term “sargable” around to mean that SQL Server can do an index seek to satisfy your search arguments. (We’re specifically talking about index seeks here, not scans – not that either one is necessarily better than others in all cases.)

The best way to show it is with a series of queries. I’m going to use the Stack Overflow Users table from the SQL Server data dump download, the same table I use in my free How to Think Like the Engine class.

StackOverflow.dbo.Users

The StackOverflow.Users table is really simple – it holds exactly what you think it holds – and it has fields with a few different data types:

  • Age – int
  • CreationDate – datetime
  • Location – nvarchar

We’ll create indexes on each of those fields to start with. This way, when I’m searching for those specific fields, I may be able to get an index seek:

Now let’s try a few queries and see what happens.

Simple integer comparisons

When we do an equality seek on Age, like WHERE Age = 21, of course we get an index seek. But what about when we CAST Age as a tinyint or bigint?

Casting Age as Other Numbers

All three of those get an index seek on our IX_Age index. Yay!

Let’s make things a little more difficult: let’s look for Age = 3 * 7.

This one would have stumped 1977 Brent

AWESOME. SQL Server is smart enough to do the math first, and even comes up with the right row estimates! Love it.

What if we use a function that isn’t exactly predictable, like getting the number of minutes after the hour:

Put 21 minutes on the clock

SQL Server still does an index seek. Isn’t that awesome? I think it’s awesome. I’m easily impressed, though.

But what if we pass in a string?

When we pass in a different data type, SQL Server does implicit conversion. It guesses what it should convert.

Implicit conversion in your favor, collect $200

SQL Server is smart enough to realize that it can just convert your string over to a number. If you hover your mouse over the index seek in the plan, it’s also got the right number of estimated number of rows, too.

The same thing happens if you pass in a string variable, too:

Int vs string variable

It turns out that SQL Server is really good with integers. On this monster data type conversion chart in Books Online, almost everything converts beautifully when you’re comparing it against an integer field:

Looks like a really bad game of Othello.

What if we pass in…a date?

Things start to fall apart in the blue squares, though. Check this out:

Believe it or not, that query is technically, morally, and legally valid:

You got your datetime in my peanut butter

Yes, we’re comparing a datetime to an integer.

And ironically – this returns no rows, but it does an index scan rather than a seek. SQL Server upconverts everyone’s age into a datetime! If you hover your mouse over the SELECT, surprisingly, the warning doesn’t say anything about your lack of sobriety.

I know what you’re thinking: how could you possibly convert an integer to a date? Well, it’s quite easy:

Casting ages as datetimes

 

Yes, for SQL Server, the number 26 is the same as January 27th of 1900.

So let’s say – just theoretically – that you had a valid reason to pass in a datetime from end users, and compare that to an integer field. Could you make this query do an index seek rather than an index scan? Absolutely – we just have to do our own explicit conversion rather than relying on SQL Server’s implicit conversion. The highlighted part of the query is the key:

Explicit in more ways than one

Voila – now I get an index seek, which is good. However, hovering my mouse over the index seek returns something curious – SQL Server expected tens of thousands of rows to come back:

Expected number of rows

But no rows are returned because Methuselah doesn’t have a Stack account. YET.

Casting GETDATE() as INT

Which brings up an interesting point.

There’s two parts to this sargability thing.

First, can SQL Server take your search arguments and turn them into an index seek rather than a scan?

Second, can SQL Server use your search arguments combined with statistics to make a good estimate on how many rows will come back? To learn more about that, watch our video class Statistics: SQL Server’s Guessing Game. Enjoy!

Previous Post
Group Post: If I Took Another DBA Job, My First Question Would Be…
Next Post
SQL Server 2017: Statistics Information Comes To Query Plans

3 Comments. Leave new

  • The datetime datatype coerces with an int because the date half of the data is stored like a 4-byte integer. (This is the same reason that getdate() + 1 evaluates to the same time tomorrow.)
    Type coercion is the spawn of the devil. Fortunately, someone in Redmond saw fit to break this with date/datetime2/datetimeoffset.

    • The downside of killing type coercion on the new date objects is that altering columns is unsafe, because some lazy DBA used @date+1 instead of dateadd(day,1,@date).

      • That’s true Brian, on my Environment I have a lot of developers and they insist on using @ date + 1. Every week I verify they queries to identify this issues.

Menu
{"cart_token":"","hash":"","cart_data":""}