June 1, 2016 at 11:17 am
Dennis Q Miller (6/1/2016)
Stacey Decker (10/30/2014)
I agree, why allow two values to mean the same thing? I handle this by allowing optional fields such as AddressLine2 to be NULL and then implement a constraint to prevent an empty string (AddressLine2 <> ''). Thoughts on this approach?Interesting that you regard NULL to be a value. That aside, for strings, I prefer the simpler approach: use empty string to represent your "special meaning" and a NULL constraint to prevent a second mechanism for the same thing.
A NULL means something very different to the SQL Server engine than a place holder value even if you think they're the same. And the behavior of NULL values is consistent, whatever place holder value you pick will have to be hard coded for everywhere it's referenced.
June 1, 2016 at 10:13 pm
ZZartin (6/1/2016)
Dennis Q Miller (6/1/2016)
Stacey Decker (10/30/2014)
I agree, why allow two values to mean the same thing? I handle this by allowing optional fields such as AddressLine2 to be NULL and then implement a constraint to prevent an empty string (AddressLine2 <> ''). Thoughts on this approach?Interesting that you regard NULL to be a value. That aside, for strings, I prefer the simpler approach: use empty string to represent your "special meaning" and a NULL constraint to prevent a second mechanism for the same thing.
A NULL means something very different to the SQL Server engine than a place holder value even if you think they're the same. And the behavior of NULL values is consistent, whatever place holder value you pick will have to be hard coded for everywhere it's referenced.
Indeed. I think we agree that NULL is different than a value. It's different to the DB engine, different to most programming languages, and different in a relational sense. NULL indicates the absence of a value. I submit that it is not a good substitute for a special (place holder?) value precisely because it is not a value to begin with.
NULLs are far less reference-friendly than values and they generally increase the amount of hard-coding required. For one example, comparison of NULL to any value is meaningless, thus requiring extra logic to accommodate the possibility of NULL in either or both operands of the comparison. For another example, NULL cannot be associated to a reference table that enables table-driven, rather than hard-coded, logic.
June 2, 2016 at 8:28 am
In the old days, [font="Courier New"]NULL[/font] was meant as "does not apply". For instance, in a common table [font="Courier New"]Entities[/font] shared by both persons and legal persons (companies) the row labelled [font="Courier New"]Gender[/font] did not apply to a company hence the cell was left at [font="Courier New"]NULL[/font].
T-SQL developers will be delighted that the International Organization for Standardization has published ISO 5218 Information technology — Codes for the representation of human sexes.
Essentially: [font="Comic Sans MS"]The four codes specified in ISO/IEC 5218 are:
0 = not known,
1 = male,
2 = female,
9 = not applicable.[/font]
A T-SQL novice can now safely handle the Gender without any risk of being ambushed by comparing a value with [font="Courier New"]NULL[/font]. Why, we can even enforce data integrity by protecting the column with a constraint allowing only these 4 values.
(It seems the possibility than an individual might just refuse to be identified by gender altogether has not been considered. There are also extremely rare instances of hermaphrodism - the individual is morphologically female and male both. And "not known" is somewhat ambiguous - who does not know ? the clerk who entered the data or the individual having his/her data entered in the DB ?)
I can hardly wait for the next standard, which will issue all the possible codes for the incorporation forms (French SARL, German GMBh, British PLC, ...) throughout the world - along with the [font="Comic Sans MS"]not applicable[/font] code....
Viewing 3 posts - 136 through 137 (of 137 total)
You must be logged in to reply to this topic. Login to reply