So you've learned all there is to know about SQL Server and you are tuning queries left and right and you stumble upon a query that is not running very hot, no matter what you try...
You add an index that you think should work, but the damn thing just keeps scanning the entire index instead of seeking into a specific row. You are even giving it a very specific value to seek on, but it just keeps telling you "Nope, screw you. I'm going to scan the entire index."
What data type are you using? Because if you use a different data type when searching than the actual column is of, you might have a pretty bad time.
In StackOverflow's database, the table posts have a column called Score. If I just copy that data over to a varchar column and index it...
...and then try to search for a very specific value, both as a varchar value, and as an int value...
We find that the first one runs instantly, but the second one takes over a minute to run.
The reason is that in the first query, the data type of the search value matches the data type in the column we are filtering on. We instantly know what that value is and can seek directly to that value in our index.
The second one, however...
Oh, yay. We had to scan the entire clustered index, converting every single "scorevariable" value to an int in order to compare it. Wouldn't it just seem more logical to convert the value we send in to whatever we are comparing it to? Nope.
In the beginning of this blog post, I said that you "MIGHT" have a bad time. This is because it depends on what you are comparing.
If we move this around, and instead query a varchar value on an int column, like the one we just copied this data from...
No implicit conversion is required and we all have a merry day, no matter how we search.
To be honest, I'm actually not sure which comparisons will cause an implicit conversion and which will not (but it can easily be googled), but as long as you don't compare stuff with another datatype, you won't need to know!
I found this the hard way when I had been trying to tune a query for hours and then found I was comparing a varchar to an nvarchar...