POLL Using COALESCE on NOT NULL columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

POLL Using COALESCE on NOT NULL columns

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-12-15 : 05:30:37

I was just looking at a piece of code which has


WHERE COALESCE(QtyShipped, 0) > 1

in one place, and then a bit later on


WHERE QtyShipped = 0

so I checked ... and QtyShipped is indeed set as NOT NULL

Normally when I write code like this I don't bother to check if the column is defined as NOT NULL and just go ahead and use COALESCE anyway - Belt & Braces. There is, no doubt, a slim chance that the constraint gets dropped in the future / by accident.

Only time I don't do that is on the PK columns as I don't need to do any checking to know what they are, and that they will always be NOT NULL.

What's your view on this?

1. Always check and never use COALESCE if the column is defined as NOT NULL
2. Always use COALESCE for "Belt & Braces"
3. Don't care. Any mix of methods is fine
4. Other

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-12-15 : 07:22:39

I prefer method 2. Better safe than sorry.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-15 : 07:37:49

1) If you use Coalesce where not necessary you're killing any chance of good index usage if now or at some point in the future there's an index on QtyShipped

Removing unnecessary functions and ensuring predicates are SARGable is always one of the first things I look at when optimising client code. UPPER in a non-case sensitive database, RTRIM on string columns, ISNULL on non-nullable columns, etc

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-12-15 : 08:46:59

Yes, good point on Indexes Gail. Whilst not relevant in this particular example, I would definitely not be using a function if there was an index available (indexes are relatively rare, so I generally know what is available on a table), but your point about adding an index in the future is well made - shame to have an instant cure to a performance problem, "just add an index", and then discover that all the code has to be changed to remove a redundant function!

... Hmmm ... I use a little SProc to list the columns for me (in a suitable format for Cut & Paste to my code). I think I should add something to that to indicate if the column is a) NOT NULL and b) Included in any indexes - so that I am more likely to avoid using any functions on it.

Where is that SQL Lint tool which I hanker after that could tell me I'm being a Twit using COALESCE on a column that is defined as NOT NULL? [;.)]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-12-15 : 12:05:40

4. Other

As long as I can make sure that SQL's 3-valued logic doesn't trip me up, I tend to write it the long way as in

where (QtyShipped is not null and QtyShipped > 1)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-15 : 12:46:31

Where performance matters, I don't think it's even debatable. STRONGLY agree with Gail.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-12-15 : 13:02:06

1. Always check and never use COALESCE if the column is defined as NOT NULL

Of course! Why screw up the optimizer? But I also would have DDL with extra info for the optimizer as my "belts & braces" code.

CREATE TABLE Shipments
(..
shipment_qty INTEGER DEFAULT 0 NOT NULL
CHECK(shipment_qty >= 0),
..);

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-15 : 13:04:40

quote:
Originally posted by sunitabeck

4. Other

As long as I can make sure that SQL's 3-valued logic doesn't trip me up, I tend to write it the long way as in
where (QtyShipped is not null and QtyShipped > 1)

Maybe I'm missing the point here, but WHY?

If QtyShipped IS NULL, then be default it's not > 1. So, why bother doing that?

Kristen
Test

22859 Posts

Posted - 2010-12-15 : 13:23:55

Yes, sorry, rubbish example.

WHERE COALESCE(QtyShipped, 0) = 0

or

WHERE QtyShipped IS NULL OR QtyShipped = 0

would have been a better example (i.e. but where the column is actually defined as NOT NULL so the NULL test is irrelevant)

Maybe the optimiser is smart enough to "optimise out" the NULL test from

WHERE QtyShipped IS NULL OR QtyShipped = 0

so that it actually plays no part at all (unless the NOT NULL constraint had gone missing)?

"CREATE TABLE Shipments
(..
shipment_qty INTEGER DEFAULT 0 NOT NULL
CHECK(shipment_qty >= 0),
..);"

Thanks Joe.

That seems like a sensible Belt & Braces to me

However, I do wonder how much "loading" it adds to inserts and updates? and whether I should be slimming down such CHECK CONSTRAINTS when we get out of DEV & QA and into PRODUCTION. Sure, they will catch some possible future bug, but maybe we are wasting CPU cycles having CHECK Constraints in there if they will have zero chance of earning their keep?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-15 : 13:34:12

quote:
Originally posted by Kristen

<snip>However, I do wonder how much "loading" it adds to inserts and updates? and whether I should be slimming down such CHECK CONSTRAINTS when we get out of DEV & QA and into PRODUCTION. Sure, they will catch some possible future bug, but maybe we are wasting CPU cycles having CHECK Constraints in there if they will have zero chance of earning their keep?

I'm not following you. If a constraint is of no use, drop it. If it is used to constrain your data, then you keep it.

You can save all the CPU cycles you want. But, if you have an improperly constrained database and you get bad data then all that means is you can get bad data faster. And if you are going to do that, then you had better carry malpractice insurance.

Kristen
Test

22859 Posts

Posted - 2010-12-15 : 13:49:06

I can have

CHECK(shipment_qty >= 0)

and it never find a record to reject.

I have that CHECK constraint in DEV / QA systems to help find any rogue data. Do I still want it in a Production environment where it is adding to CPU cycles every time there is an INSERT or UPDATE?

For example, in my own system the Application would catch a negative QtyShipped if it ever encountered one - that doesn't stop a DBA having an off-day and doing an adhoc update that sets QtyShipped negative ... but I would prefer not to waste the client's server's CPU on that CHECK just to keep my malpractice insurance premiums down

TimSman
Posting Yak Master

127 Posts

Posted - 2010-12-15 : 14:13:00

The reason to do the check would be if you are unsure the table is going to change. But, if the table changes, wouldn't you be going through and looking at what those changes will impact anyway?

Kristen
Test

22859 Posts

Posted - 2010-12-16 : 09:52:04

Yes, indeed we would. In our case the greater risk is that a rollout failed to implement a change (which is unlikely, the "update script" either runs error-free, or not), or something else happened which caused the constraint to be dropped - maybe we needed to remove a constraint to import some data and then it was not reinstated.

We had a recent case where an index was missing which caused severe performance problems and took us a lot of "support time" to diagnose.

Maybe the answer, for me, would be to compare the database against a "known good database" checking if there are any indexes, or constraints, missing (or added) unexpectedly. We could have this as part of our rollout processes (we don't currently, instead relying on the update script working properly, but the update script is only making "new changes" not, for example, attempting to recreated indexes and constraints that have not changed).

So ... its probably just me being over-cautious. I think I need to be more certain that the Constraints on the database CANNOT be wrong (or if they are that that fact will get picked up by a routine periodic check). I shall add that to our procedures going forwards. It will be interesting to see what has gone missing, or anything adhoc that has been added, to our clients' systems over the years ...

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-12-16 : 11:33:04

quote:
Originally posted by Kristen
[However, I do wonder how much "loading" it adds to inserts and updates? and whether I should be slimming down such CHECK CONSTRAINTS when we get out of DEV & QA and into PRODUCTION. Sure, they will catch some possible future bug, but maybe we are wasting CPU cycles having CHECK Constraints in there if they will have zero chance of earning their keep?

That is like practicing life boat drills with your flotation jacket, then taking it off when the ship is really sinking.

What actually happens is that the CHECK() predicates are picked up by the optimizer. This takes virtually no time, since it is done in main storage. But the boost you can get can be huge. Consider something like "foobar INTEGER NOT NULL CHECK (foobar BETWEEN 1 AND 25)" versus just "foobar INTEGER NOT NULL" when the optimizer has to estimate a row count.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Kristen
Test

22859 Posts

Posted - 2010-12-16 : 11:55:49

OK, I'm sold!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-16 : 12:30:46

quote:
Originally posted by Kristen

but maybe we are wasting CPU cycles having CHECK Constraints in there if they will have zero chance of earning their keep?

I have a simple answer to this...

"Premature optimisation is the root of all evils"

If you've tested and know it's a problem, then fine. If you're just guessing that it may be a problem without any testing, you are wasting time and possibly making bad decisions.

--
Gail Shaw
SQL Server MVP

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-12-20 : 20:02:18

1. Always check and never use COALESCE if the column is defined as NOT NULL

But more to the point, I try to minimize the use of nullable columns. Minimize, in this case, aims at zero.

There are few data items that can't be defaulted to a known value (empty string, zero, +/- infinity, etc.). In the cases where you need to have a null entry, have at it but start from the premise that the data is not nullable unless proven otherwise. Performance improves and code simplifies; win/win.

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources