Four Rules for NULLs

Question

Four Rules for NULLs

Viewing 3 posts - 151 through 152 (of 152 total)

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

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

bear in a box (6/24/2009)
...but this assumes one knows very field in the table has been allowed or not allowed nulls. this is not the case which is why the issue comes up during actual.

As a developer, you'd better know what the data in the table is and whether or not a column can take a NULL or not... it's part of the job. It only takes a second or two to find out.

--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)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Greg Martin-419640 (1/7/2010)
Hello and happy new year everyone.
I've looked in the thread and not managed to see an answer to this. I use SQL server.
If I have a Customers table, with a nullable field Country, and want to find all Customers except ones in Russia:
SELECT * FROM CUSTOMERS WHERE NOT Country = 'Russia'
will not show me customers with a null country. And that seems rotten to me, the query is simple and I would expect to see all except customers with the country specified Russia.
I could use ansi_nulls off, or have to do
SELECT * FROM CUSTOMERS WHERE NOT ISNULL(Country, '') = 'Russia'
both of these seem ugly, as if you must walk on eggshells if a null may be present. Is there a better way?
All the best,
Greg

Heh... Yep... proper database and table design. Perhaps the column should have the constraint of NOT NULL on it. Better yet, it should have an FK to a table of proper country names. And even better would be to store the ISO ID of a country that's in a Country table with an FK to boot (countries HAVE changed names in relatively recent history).

So far as setting ANSI_NULLS to off goes, that's not something I'd get used to. The setting isn't available in many RDBMS's and it's already been identified as something that's going away in SQL Server. Besides, NULLs are much more useful than they are a pain.

--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)

Open Minded SSCommitted Points: 1852 More actions · Answer 3

Jeff Moden (1/7/2010)
bear in a box (6/24/2009)
...but this assumes one knows very field in the table has been allowed or not allowed nulls. this is not the case which is why the issue comes up during actual.
As a developer, you'd better know what the data in the table is and whether or not a column can take a NULL or not... it's part of the job. It only takes a second or two to find out.

I am also the end user of my systems handling sales data of close to a billion in sales per year. There are 3 departments who are the internal customers of these data. But database is just 1 of my 5 hats in IT, even if I have two delegates for some of the work.

My concentration flips between making our IT close to business then being IT technical to get things going. Right now, our AV is not getting updated, part of the wealth of bugs which is the trademark of a well-known vendor. I have been batting at it all day yesterday.

Now, this morning, I will be shifting to data processing again - another ad hoc request from sales. From where I am, it is very easy to fall into the same problem solved a 2 years back.