All The Wrong Ways To Search Numbers In SQL Server Queries

Patternistic

---

I see this mistake quite a bit! Golly do I. And then I cry and drink and drink and cry and why do you people insist on ruining my makeup?

Most of the time, it’s some query that looks like this:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE p.OwnerUserId LIKE '2265%'

The cruddy thing here is that… SQL Server doesn’t handle this well.

Even with this index on OwnerUserId, bad things happen in the form of a full scan instead of a seek, because of the implicit conversion function.

CREATE INDEX
    p0
ON dbo.Posts
(
    OwnerUserId
);

Because the OwnerUserId column has to be converted to varchar(12) to accommodate the wildcard search, we’re unable to directly seek to data we care about.

Many Other Wrongs

---

As if LIKE weren’t bad enough, I often see other patterns that attempt to mimic the behavior with more typing involved:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE LEFT(p.OwnerUserId, 4) = '2265'; 

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE SUBSTRING
      (
          CONVERT(varchar(12), 
          p.OwnerUserId), 
          0, 
          5
      ) = '2265'; 

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE PATINDEX
      (
          '2265%', 
          CONVERT(varchar(12), p.OwnerUserId)
      ) > 0;

And even this, which doesn’t even work correctly because it’s more like saying ‘%2265%’.

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE CHARINDEX('2265', p.OwnerUserId) > 0;

These will have all the same problems as the original LIKE search pattern shown above, where we get a scan instead of a seek and a conversion of the OwnerUserId column from an integer to a varchar(12).

Is there a way to fix this? You betcha.

Computered

---

Adding a computed column does the job for us:

ALTER TABLE 
    dbo.Posts 
ADD OwnerUserId_varchar 
AS  CONVERT(varchar(12), OwnerUserId);

CREATE INDEX 
    pv 
ON dbo.Posts
(
    OwnerUserId_varchar
);

And without changing the original LIKE query, the optimizer will match it with our indexed computed column.

Note that none of the other attempts at rewrites using left, substring, or patindex will automatically match with new indexed computed column.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.