[dba-SQLServer] ANSI null compliance

Arthur Fuller artful at rogers.com
Mon May 9 16:49:50 CDT 2005


You have just cited the exceptions which IMO prove my point. Those 
columns should be nullable for exactly the reasons you mention. My point 
was that an employee without a surname is a problem, and similarly 
without an SSN or SIN or their international equivalents. Certain data 
must be there. Other data is optional. But in the case that you cited 
(missing home phone) I would try to devise a system (stored proc or 
whatever) that assigned this row a status of "missing important data" 
and automatically revisit these faulty rows so they can be corrected.

Stuart McLachlan wrote:

>On 8 May 2005 at 18:17, Arthur Fuller wrote:
>
>  
>
>>Frankly, I'm not up to chapter on verse on SQL 92, although I read it 
>>religiously :)
>>
>>My knee-jerk reaction would be that all columns of whatever type should 
>>default to NOT NULL, because IMO, Null columns represent a weakness in 
>>our design model. I hate Null columns, unless the client can express a 
>>clearly articulated set of circumstances in which said column should be 
>>null. For example, we hire a new employee and are not sure sure in which 
>>department to place her. Given this objection, I would say, why hire her 
>>then? Client could answer, Because her qualifications are so good we 
>>don't want her to go elsewhere, we'll find a place for her.
>>
>>    
>>
>When the new employee says  "I've just moved here to start this job. I 
>don't know my home phone number yet, the telco are currently installing 
>it", do you then say "In that case, we can't create an employee record for 
>you and you won't get paid"?
>
>  
>
>>Now JWC would slide into his zero'th column argument here, which I 
>>flatly reject, but that's another topic.
>>
>>I think all column declarations should default to NOT NULL and force you 
>>to do extra work to make them nullable. As I see it, if you can do 
>>without the data, then do without it. Conversely, if you need the data 
>>then demand it.
>>
>>    
>>
>
>You must live in a very comfortable world where every question has an 
>answer.  In my world, data is frequently incomplete and  the availability 
>of a null value ("A special value, or mark, that is used to indicate the 
>absence of any data value.") is essential.
>
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 5/6/2005




More information about the dba-SQLServer mailing list