Some Suggestions on setting null in SQL server: sqlnull

Some Suggestions on setting null in SQL server: sqlnull

When designing a table, we sometimes argue about whether the field allows Null values.

The database master Kalen Delaney gave the following suggestions:

1. Never allow NULL values in the User table
2. the user table definition contains a not null limit.
3. Do not rely on Database properties to control NULL values.

For the first pointIn the reverse direction, if NULL is allowed, what will happen to us.

1. SQL sets a special bitmap in each record to show which columns that allow null values are really null. 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. Some special logic is added to process this NULL value, which often leads to bugs.

Second pointAdd some default values to columns that do not allow NULL. If NULL is not allowed but the default value is not added, INSERT will fail if no column is displayed, SQL Server performs NULL INSERT on columns not displayed in INSERT by default.

Last pointIt mainly involves the comparison of NULL values. In our impression, whether to use is null or is not null for comparison, or use =, <> for comparison. This depends on the database option ansi nulls. We cannot change the database option (most of us are not DBA), but we can use session settings SET ANSI_NULLS to be equivalent to the database option ansi nulls. When this option IS true, all comparisons with NULL values will result in FALSE. The code must use the is null condition to determine whether it is null. When this option is false, if both values are NULL, true is obtained. SQL Server allows = NULL as the synonym of IS NULL and <> NULL as the synonym of IS NOT NULL.

If you forget this option, we recommend that you use is null to judge whether it is null or is not null to determine whether it is not null.

The test is as follows:

There are only two rows of data in table t3,

When SET ANSI_NULLS OFF:

SET ANSI_NULLS ON

To sum up, the optimal solution is:When designing a table, do not allow the field to be NULL and add the default value to the field..