SQL Server: About null

Source: Internet
Author: User
Tags table definition
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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.