November 9, 2010 at 9:19 am
I am struggling to work with an old oracle developer who does some mighty strange things and I was wondering if one or two of you gurus can clear this up.
first, when she is trying to evaluate an empty string in a where statement she will do this..
(exerp).. and s.column > ''
and I think it should go like this
and s.column <> ''
can you tell me which is better or 'right'? of course I think I'm right, but I always do.
second, since she was an oracle developer, she doesnt believe in nulls. I love them and use them... and she tries to strip them all out. she claims that nulls make writing queries harder, and we dont want any outward facing data to say 'null'. I contend that they are useful and necessary when you dont know a value...
Can you provide me with a link to a paper that proves how valuable nulls are... and how t-sql is better than oracle? I'm looking for gains in indexing space and speed with null(I hope). Please help me out if you can.
thanks for your help
c
November 9, 2010 at 9:30 am
Either way of checking for a non-empty string works. s.column > '' works because any non-empty string will be greater than ''
November 9, 2010 at 10:01 am
Thanks for that. One less thing to worry about.
thank you
November 10, 2010 at 6:56 am
Here is where I must concur with Celko. Nulls should be avoided except when a null value has real meaning to the business logic. (ex: CancellationDate, or MiddleName )
The reasons why are many and should be self evident to those with experience in designing large and/or high performance transactional systems, Celko mentioned one important one from an application standpoint.
The probability of survival is inversely proportional to the angle of arrival.
November 10, 2010 at 7:15 am
s.column <> ''
What do you want to happen if the column is null?
I agree with you about nulls being useful but you have to think about the users of a database.
A lot of people assume a 2 valued logic system so if business people are going to be given ad hoc access to the database or you want non-database developers to access it (e.g. report writers) then I would try to stay away from nulls. That almost certainly means they should be avoided in datamarts.
Database developers should be expceted to know or learn how to handle nulls.
If all access is via stored procedures and those are written or reviewed by database developers then you have a lot more flexibility and life is a lot easier.
Cursors never.
DTS - only when needed and never to control.
November 11, 2010 at 12:08 pm
Thanks to all.
1. I will only use nulls where the value is unknown. middle name is a perfect example.
2. I use .net for a front end for sql, so I'm not overly concerned about handling nulls... but if there is a caveat with .net and null, I'd like to know.
3. in the case of <> ''... it will not have null fields because the developer doesnt like nulls. I'm trying to build a case to start using them.
November 11, 2010 at 12:19 pm
The cases for and against "null" in databases is argued endlessly.
Ask the dev how he/she wants to handle known-unknowns without using nulls. If the answer is something that can be reasonably implemented, use it. If the answer is a blank stare, a brush-off, or requires solving the unified field problem, use nulls.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2010 at 12:34 pm
WHERE s.column >'' will be marginally more efficient than WHERE s.column<>''
Nulls are sometime necessary, for example if I have a DrivingConvictions table in a car insurance database I might have
DriverID
OffenceCode
PenaltyPoints
IllegalSubstanceReading
If I am done for speeding (some hope in my car) then Illegalsubstancereading could legitemately be NULL. If I am breath tested then theoretically I can get a zero reading but I believe human biology prevents it.
Recording zero would imply that there was a breath/drug test carried out.
I don't like nulls because I have to code either in the app or the DB to allow for comparisons on them.
I believe .NET also requires you to use a DBNull function.
On big systems you want your tables as efficient as possible and NULL values require a setting in the 192 byte record header.
November 11, 2010 at 12:53 pm
David.Poole (11/11/2010)
WHERE s.column >'' will be marginally more efficient than WHERE s.column<>''Nulls are sometime necessary, for example if I have a DrivingConvictions table in a car insurance database I might have
DriverID
OffenceCode
PenaltyPoints
IllegalSubstanceReading
If I am done for speeding (some hope in my car) then Illegalsubstancereading could legitemately be NULL. If I am breath tested then theoretically I can get a zero reading but I believe human biology prevents it.
Recording zero would imply that there was a breath/drug test carried out.
I don't like nulls because I have to code either in the app or the DB to allow for comparisons on them.
I believe .NET also requires you to use a DBNull function.
On big systems you want your tables as efficient as possible and NULL values require a setting in the 192 byte record header.
And that's why there need to be two different versions of null. One for "we don't know" and another for "we don't care". (Okay, really for "don't know" vs "known no value", but the other is easier to remember.) Till then, null = "unknown value or no value".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 12, 2010 at 8:52 am
2. I use .net for a front end for sql, so I'm not overly concerned about handling nulls... but if there is a caveat with .net and null, I'd like to know.
In your .NET you'll need to get into the habit of testing for NULLs before attempting to reference any value from a, say, DataRow cell. If you attempt to reference NULL as a value in .NET, an error will be thrown.
Pretty easy to work around.
For example, when binding, instead of:
MyControl.Text = Cstr(MyDataRow("Column1"))
You would say:
If Not IsDbNull(MyDataRow("Column1")) Then
MyControl.Text = Cstr(MyDataRow("Column1"))
End if
Or, as suggested by nigelrivett, make sure all data can only be returned to the DAL via Stored Procedures, and make sure all values returned by the Stored Procedures are COALESCED.
If you don't have that level of control, you'll need to trap in .NET, either in the Presenattion layer as described above, or you could do a NULL scrub in the DAL before turning data over to the Presentation layer.
November 12, 2010 at 11:54 am
If you have to build application code to scrub nulls then surely it would have been better not to have them in the first place. Surely allowing nulls just adds one more thing to have to build tests for?
If you have a DAL layer then clean on the way in to stop having to clean on the way out.
You can't avoid all instances of nulls but having sensible defaults for missing values is useful.
Just today I've run into a case where having NULL values as allowed an application to misrepresent stats because null values screwed up the calculations. We now have to face the Spanish Inquisition as to why this happened and also we now have a data trust issue we didn't have before.
November 12, 2010 at 1:04 pm
David's points definitely make sense, but as he himself mentioned earlier, there may be instances where NULLs are required to represent <unknown>. If you don't need them, scrubbing them in the DAL on the way in is probably the best way to go.
But, if you have to use them, and you're using .NET to present, you'll want to trap for DBNull-related errors. Otherwise your app will be throwing these quite a bit.
Personally, I like Gus' argument for separating <unknown> and <don't care> values.
Full disclosure: my company allows NULLs (probably more than we need to); both the .NET and SQL developers have been trained in handling them so we get by okay.
Still they are like handguns: without proper awareness and training the results can be tragic.
November 12, 2010 at 1:36 pm
David.Poole (11/12/2010)
If you have to build application code to scrub nulls then surely it would have been better not to have them in the first place. Surely allowing nulls just adds one more thing to have to build tests for?If you have a DAL layer then clean on the way in to stop having to clean on the way out.
You can't avoid all instances of nulls but having sensible defaults for missing values is useful.
Just today I've run into a case where having NULL values as allowed an application to misrepresent stats because null values screwed up the calculations. We now have to face the Spanish Inquisition as to why this happened and also we now have a data trust issue we didn't have before.
I would argue that you've had a data trust issue all along, but it's only blown up recently. Before, you were trusting data that hadn't been validated as useful, now you don't trust data because it's been shown to have problems. Either way, there's a problem.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 12, 2010 at 11:16 pm
CELKO (11/9/2010)
Do not use the old dialect != from C and Sybase.
I've been trying to get people out of the habit of using != but they're just not buying my "<> is more readable". Do you have another reason to avoid it?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2010 at 7:07 pm
Jeff Moden (11/12/2010)
CELKO (11/9/2010)
Do not use the old dialect != from C and Sybase.I've been trying to get people out of the habit of using != but they're just not buying my "<> is more readable". Do you have another reason to avoid it?
Heh... one of the few times I actually agree with the guy and I get no response. 😉 Moving on... :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply