IsNumeric with NULL and Empty String

Question

Post reply

IsNumeric with NULL and Empty String

Viewing 8 posts - 31 through 37 (of 37 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

SanDroid (8/4/2011)
"Code to enforce Business Rules should not be in SQL!"

Some days, I would go further:

"Code ~~to enforce Business Rules~~ should not be in SQL!"

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SanDroid SSChampion Points: 10068 More actions · Answer 2

SQLkiwi (8/4/2011)
SanDroid (8/4/2011)
"Code to enforce Business Rules should not be in SQL!"
Some days, I would go further:
"Code ~~to enforce Business Rules~~ should not be in SQL!"

Nice... 😛

TomThomson SSC Guru Points: 104773 More actions · Answer 3

SanDroid (8/4/2011)
I have to agree. Every time I look at things like this my brain starts repeating the following mantra:
"Code to enforce Business Rules should not be in SQL!"
Validation of user interfact data is best done by application Functions or Subroutines, not DML. 😎

The trouble with that approach is that when misinterpreted by certain people it leads to stupidities like "uniqueness should be forced by application code, not database constraints" and "since all normalisation except 1NF is about enforcing business rules by schema structure no normalisation beyond 1NF should ever be done". I've heard both of those too often from people who have misunderstood the perfectly simple rule that the application should ensure the data being fed to the database is correct and think that it means that the database should not do it.

The countervailing mantra is that everything, including the databse, should validate every parameter that is fed to it and also ensure that everything it passes to another component is valid. Ideally most of this is done by a decent type system that allows strong domain constraints to be specified. Unfortunately (T-)SQL doesn't (yet) have such a type system, so we get badly thrown together bandaids (like IsNumeric) to patch it up with; TRY_CONVERT is a big improvement, but not perfect - and there may be performance considerations working against a nice clean test delivering 1 for "can convert and is not null", 2 for "can convert and is null", and 0 for "can't covert" (that should include the case where destination type doesn't permit null but the parameter is null, but SQL support for not null types outside of tables is non-existent) since if it delivers a non-zero result the next thing to happen will probably be the conversion and that was probably already done once for the test (a type system catering properly for pairs would of course allow something both efficient and clean, but we don't have even that much of a decent type system).

Tom

Paul White SSC Guru Points: 150467 More actions · Answer 4

Tom.Thomson (8/4/2011)
...if it delivers a non-zero result the next thing to happen will probably be the conversion and that was probably already done once...

Indeed. Worse, people might write the test and the convert in the same statement in such a way that the convert could be reordered by the optimizer to happen before the test...

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

TomThomson SSC Guru Points: 104773 More actions · Answer 5

SQLkiwi (8/4/2011)
Tom.Thomson (8/4/2011)
...if it delivers a non-zero result the next thing to happen will probably be the conversion and that was probably already done once...
Indeed. Worse, people might write the test and the convert in the same statement in such a way that the convert could be reordered by the optimizer to happen before the test...

Yes indeed. I've seen similar things. I wouldn't have seen them if people didn't tend to think that things are evaluated in the "obvious" order, but this would be another chance for people to make that assumption and get burnt by it.

Tom