SARGABLE way for this search

Question

SARGABLE way for this search

Khades

SSCrazy

Points: 2067
More actions
October 22, 2011 at 1:50 pm

#261859

CREATE TABLE dbo.Search
(
ColumnA VARCHAR(10)
)
I need to query this table and get records in which ColumnA's length is exactly 10, are numeric. I know you can use
WHERE IsNumeric(ColumnA) = 1 AND LEN(ColumnA) = 10. But I am pretty sure that's not SARGABLE.
Will BETWEEN work for this? Or perhaps another data type?
I don't think I can use INT because we need to keep preceding 0s.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

In the presence of the correct index, the following over comes the problem with trying to use ISNUMERIC as an IsAllDigits function (which you shouldn't do), checks for the proper length, and does it all with an INDEX SEEK...

SELECT ColumnA

FROM #TestTable

WHERE ColumnA LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

The INDEX SEEK, in this example, buys you approximately 30% in performance gains but you won't actually be able to tell unless you use something like a million row test table because they both quite fast. The important part is that ISNUMERIC can allow the wrong information to slip through because it was never meant to operate as an IsAllDigits function. Please see the following article for more on that.

http://www.sqlservercentral.com/articles/IsNumeric/71512/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ben Teraberry SSChampion Points: 11845 More actions · Answer 2

Some things you might want to consider:

You could use an int and add leading zeros at the presentation layer. Generally, this would be done within the application that is consuming the data, but you could even do it in a select statement. Or, if you have a requirement to maintain the data in a particular way, you could create a user defined data type and implement it however you wish. Another possibility, depending on the particulars of the environment, would be to create a persisted calculated column and create an index on that.

I'm very much guessing, but it sounds like your system is storing values as character data because someone wanted to take the quick route of being able to throw formatted values into the table. Generally I've seen this approach lead to significant problems, including the issue you mention. Really it comes down to how flexible (or restrictive) with the data input you need to be and how well you need your queries to perform.

Good luck.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Considering that the OP is trying to find those things that only have numeric digits, it sounds a whole lot worse than that. It sounds like there may be non-numeric data stored in the same column as the 10 character numerics and that the column is being used to store more than one type of information like one might do with a "One True Lookup Table".

I absolutely agree that the design of this column of data and the table it resides in should be reconsidered sooner than later.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Khades SSCrazy Points: 2067 More actions · Answer 4

This is only a staging table. The bigger problem is the database where we get the information to stage in this table. The whole database is full of EVP/NVP tables with VARCHAR(1500) columns that can take just about anything from the UI.

Thank you both for your help!