Presents your 
SQL SERVER E-NEWSLETTER for August 13, 2002

<------------------------------------------->

IDENTIFY FOREIGN KEYS THAT ALLOW NULL VALUES

In SQL Server, foreign keys that have nullable values cause further
overhead. SQL Server must track additional information on nullable 
columns,
which causes this extra overhead. You can determine how many foreign 
keys
allow nullable values on your databases by using the following script:

select    substring(object_name(sr.fkeyid),1,30) tab_name,
substring(object_name(sr.rkeyid),1,30) referencing_tab_name,
      substring(sc.name,1,30) col_name,
      CASE WHEN sc.isnullable = 0 then 'NOT NULL'
      ELSE 'NULL'
      END col_nullability,
      substring(object_name(sr.constid),1,30) const_name,
      sr.keycnt
from syscolumns sc
join sysreferences sr on sc.id = sr.fkeyid
            and (   sc.colid = sr.fkey1 OR
               sc.colid = sr.fkey2 OR
               sc.colid = sr.fkey3 OR
               sc.colid = sr.fkey4 OR
               sc.colid = sr.fkey5 OR
               sc.colid = sr.fkey6 OR
               sc.colid = sr.fkey7 OR
               sc.colid = sr.fkey8 OR
               sc.colid = sr.fkey9 OR
               sc.colid = sr.fkey10 OR
               sc.colid = sr.fkey11 OR
               sc.colid = sr.fkey12 OR
               sc.colid = sr.fkey13 OR
               sc.colid = sr.fkey14 OR
               sc.colid = sr.fkey15 OR
               sc.colid = sr.fkey16
   )
   where    sc.isnullable != 0
      and sr.keycnt > 1
   go

It's best to make columns not nullable. There may be times when you are
populating tables where the values are unknown. However, if it's 
possible
to establish a default value for these columns, it's best to do so.
Practically speaking, making nonnullable columns with defaults can't 
fulfill
all situations and a cost benefit decision exists, based on performance
and data integrity in these situations. Just remember that there is an
additional cost for nullable columns, and if the nullable columns can 
be
avoided, there are gains to be realized.

----------------------------------------