When designing a table, we sometimes argue about whether the field allows null values. The database master Kalen Delaney gave me some 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 point, we say in the reverse direction, if null is allowed, what impact will it bring 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. Allow null and add applicationsProgramCodeThe complexity of adding some special logic to process this null value, which often leads to bugs.
Second, add some default values to columns that do not allow null. If null is not allowed but the default value is not added, this will cause insert failure. By default, SQL Server inserts null columns not displayed in insert.
The last point 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