[dba-SQLServer] ANSI null compliance

Arthur Fuller artful at rogers.com
Sun May 8 17:17:46 CDT 2005


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.

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.

Just my $.02.

Susan Harkins wrote:

>Well, here's the problem in a nutshell -- the default isn't in compliance
>with SQL-92, which is Okay. However, Query Analyzer IS -- which would tend
>to lead one astray. Does anyone know if Enterprise Manager is too because
>even though the Pubs default returns 0, in EM, if I create a new
>table/column, it automatically checks Allow Nulls -- which is the opposite
>of what you'd expect considering the inner non-compliance. But, my thinking
>is -- if Query Analyzer is in compliance, perhaps EM is too. 
>
>Or else I'm just terribly baffled by the entire exchange. 
>
>Susan H. 
>
>The Rules from the ANSI-92 standard:
><quote>
>Every column has a nullability characteristic of known not nullable
>         or possibly nullable, defined as follows:
>
>         A column has a nullability characteristic that indicates whether
>         any attempt to store a null value into that column will inevitably
>         raise an exception, or whether any attempt to retrieve a value
>         from that column can ever result in a null value. A column C with
>         <column name> CN of a base table T has a nullability characteristic
>that is known 
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>  
>



More information about the dba-SQLServer mailing list