[dba-SQLServer] ANSI null compliance

On 7 May 2005 at 10:54, Susan Harkins wrote:

> Looking at the DATABASEPROPERTYEX function's ISANSINULLDEFAULT property, I
> am assuming the following:
>  
> 1 (True) -- the database's null default meets ANSI compliance
> 0 (False) -- the database's null default does not meet ANSI compliance
>  
> Neither of which really tells me what the ANSI compliance is. Since Pubs
> property is 0, and Pubs does not allow nulls by default, I'm going out on a
> limb here and deduce that the SQL-92 ANSI null default is to allow nulls. Is
> this correct? 
>  

Here's more than you every wanted to know :-)


http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/tsqlref/ts_sp_da-di_8c32.asp
<quote>
ANSI null default 	When true, CREATE TABLE follows the SQL-92 rules to 
determine if a column allows null values.
</quote>

See below for the actual rules. MS's "simple" explanation of the rules are:

http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/tsqlref/ts_set-set_7g32.asp
</quote>
This setting only affects the nullability of new columns when the 
nullability of the column is not specified in the CREATE TABLE and ALTER 
TABLE statements. When SET ANSI_NULL_DFLT_OFF is ON, new columns created 
with the ALTER TABLE and CREATE TABLE statements are, by default, NOT NULL 
if the nullability status of the column is not explicitly specified. SET 
ANSI_NULL_DFLT_OFF has no effect on columns created with an explicit NULL 
or NOT NULL.
</quote>


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 not nullable if and only if either:

         -  there exists at least one constraint that is not deferrable and
            that simply contains a <search condition> that contains CN IS
            NOT NULL or NOT CN IS NULL or RVE IS NOT NULL, where RVE is a
            <row value constructor> that contains a <row value constructor
            expression> that is simply CN without an intervening <search
            condition> that specifies OR and without an intervening 
<boolean factor> that specifies NOT.

         -  C is based on a domain that has a domain constraint that is
            not deferrable and that simply contains a <search condition>
            that contains VALUE IS NOT NULL or NOT VALUE IS NULL without an
            intervening <search condition> that specifies OR and without an
            intervening <boolean factor> that specifies NOT.

         -  CN is contained in a non-deferrable <unique constraint defi-
            nition> whose <unique specification> specifies PRIMARY KEY.

         Otherwise, a column C is possibly nullable.
</quote>
-- 
Stuart