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.

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?

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.

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:

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.

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:

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:

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:

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:

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:

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

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!