SQL Server some suggestions for setting null _mssql2005

When we design a table, we sometimes argue about whether the field allows null values.

Database Cow Kalen Delaney gives some advice:

1, never allow null values in user tables
2, include a NOT null limit in the user table definition
3, do not rely on database properties to control the behavior of NULL values

for the 1th , we say in reverse, if we allow null, what effect it will have on us.

1,sql sets a special bitmap in each record to show which columns that allow null values are stored really empty values. If it is null, SQL Server must decode the bitmap when accessing each row.
2, allowing null also increases the complexity of the application code, adding some special logic to handle the null value, which often leads to bugs.

2nd , to include some default values on columns that contain disallowed nulls, if NULL is not allowed, but no default value has been added, the insert will fail if column insertion is not displayed, and SQL Server defaults to insert. Null insert for columns that do not appear.

The last point is mainly related to the comparison of null values. In our impression, is it null,is not NULL comparison, or with =,<> comparison. Depending on the database options ANSI NULLS, we cannot change the database options (most of us are not DBAs), but we can use the session set ANSI_NULLS equivalent to the database option ANSI NULLS. When this option is true, all comparisons to null values will be false, and the code must use is NULL condition to determine whether it is null, and when this option is false, if the two values that are compared are null values, the TRUE,SQL server allows =null as a synonym for is null , <> null is used as a synonym for not null.

If you forget this option, it is recommended to use is NULL to determine NULL, is not NULL to determine non-null.

The test is as follows:

There are only two rows of data in the T3 table, as shown in figure:

When SET ansi_nulls off:

When SET ansi_nulls on

In summary, the best way to do this is to not allow the field to be null when designing the table, and attach a default value to the fields .