Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1213141516

Four Rules for NULLs Expand / Collapse
Author
Message
Posted Thursday, January 7, 2010 7:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:35 PM
Points: 36,944, Visits: 31,445
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #844053
Posted Thursday, January 7, 2010 7:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:35 PM
Points: 36,944, Visits: 31,445
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #844056
Posted Thursday, January 7, 2010 8:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:04 AM
Points: 176, Visits: 567
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.
Post #844064
« Prev Topic | Next Topic »

Add to briefcase «««1213141516

Permissions Expand / Collapse