Sqlserver: Null Value

 

Null: Unknown data

 

Null: any operation that has null is null, except the following:

• When set ansi_null of: null = NULL, the result is true.
• Set concat_null_yields_null off: NULL is treated as a null string (however, if all the parameters involved in the operation are null, the result is of course null)

 

Null Function

Is [not] Null: The expression can only be used as a condition. Is it null? Yes. The condition is true, not false.

Isnull (): function. If the first parameter is null, replace the value of the second parameter with the value of the first parameter as the return value of the function. Remember: the type of the second parameter must be compatible with that of the first parameter!

Nullif (): function. If two parameters have equal values, one parameter is null, or two parameters are null, the return value of the function is null; otherwise, the value of the first parameter is returned.

 

 

Set ansi_nulls {on | off}
 

The SQL-92 standard requires that the value be false when a null value is equal to (=) or not equal to (<>. When set
When ansi_nulls is on, select where column_name = NULL is used even if column_name contains null values.
The statement returns zero rows. Even if column_name contains non-null values, select using where column_name <> null
The statement still returns zero rows.

When set ansi_nulls is off, equal to (=) and not equal to (<>) Comparison operators do not follow SQL-92
Standard. Use the SELECT statement where column_name = NULL to return the rows containing null values in column_name. Use where
Column_name <> A null SELECT statement returns rows in a column that contain non-null values. In addition, use where column_name
<> All rows that are neither xyz_value nor null are returned by the SELECT statement of xyz_value.

When set ansi_nulls is on, all Null Value Comparison values are unknown. When set ansi_nulls is off
If the data value is null, the comparison of null values for all data is true. If this parameter is not specified, the ANSI nulls option of the current database is applied. About ANSI
For more information about nulls Database options, see alter
Database (TRANSACT-SQL) and .

Set ansi_nulls on
Will affect the comparison. If both sides of the comparison are columns or composite expressions, this setting will not affect the comparison.

To make the script run as expected, regardless of the ANSI nulls Database options or set ansi_nulls settings, use
Is null and is not null.

For stored procedures, SQL server uses the set ansi_nulls value when the stored procedure was initially created. Set
The settings of ansi_nulls are restored to their original values and take effect. When set ansi_nulls is called inside the stored procedure, its settings are not changed.

Set ansi_nulls to on when executing a distributed query.

Set ansi_nulls must also be on when you create or change an index for a calculated column or index view. If set ansi_nulls is
Off, the CREATE, update, insert, and delete statements for the table (including the index of the calculation column or index view) will fail. SQL Server
An error message is returned, which lists all set options that violate the required values. In addition, if set ansi_nulls is off
SQL Server ignores the index value of the computed column or view and parses the SELECT statement, just as the table or view does not have such an index.

SQL Server's SQL local client ODBC driverProgramAnd the SQL local client OLE DB access interface will automatically
Set ansi_nulls to on. This setting can be set in the ODBC data source, ODBC connection attribute, or ole db connection attribute (they are connected to the SQL Server
In the application. For connections from DB-Library applications, the default value of set ansi_nulls is off.

When set ansi_defaults is on, set ansi_nulls is enabled.

Set ansi_nulls is set during execution or running, rather than during analysis.

 

 

 
Set ansi_null_dflt_on {on | off}
 

When the ANSI null default option of the database is false, modify the session behavior to overwrite the default null of the new column.

This setting affects the null of new columns only when no column is specified in the create table and alter table statements. Set
When ansi_null_dflt_on is on, if the column is not explicitly specified as null, alter table and create table are used.
New columns created by the statement can use null values. Set ansi_null_dflt_on is invalid for columns created using explicit null or not null.

Set ansi_null_dflt_off and set ansi_null_dflt_on cannot be set
On. If one option is set to on, the other option is set to off. Therefore, you can set ansi_null_dflt_off or ansi_null_dflt_on
Set to on or off. If one option is on, set ansi_null_dflt_off or set
Ansi_null_dflt_on. If both options are set to off, SQL Server 2005 uses the SYS. Databases directory View
The value of the is_ansi_null_default_on column.

To make the transact-SQL script more reliable in a database containing different null settings, it is best to always create table and alter
Specify null or not null in the table statement.

SQL native client ODBC driver and SQL native Client
The ole db access interface automatically sets ansi_null_dflt_on to on. For connections from DB-Library applications, Set
Ansi_null_dflt_on is set to off by default.

When set ansi_defaults is on, set ansi_null_dflt_on is enabled.

Set ansi_null_dflt_on is set during execution or running, rather than during analysis.

 

 
Set ansi_null_dflt_off {on | off}
 

When the ANSI null default option of the database is true, the session behavior is changed to overwrite the default null of the new column.

This setting affects the null of new columns only when no column is specified in the create table and alter table statements. By default, when
When ansi_null_dflt_off is on, if the column is not explicitly specified as null, alter table and create table are used.
The new column created by the statement is not null. Set ansi_null_dflt_off is invalid for columns created by displaying null or not null.

Set ansi_null_dflt_off and set ansi_null_dflt_on cannot be set
On. If one option is set to on, the other option is set to off. Therefore, you can set ansi_null_dflt_off or set
Set ansi_null_dflt_on to on, or set both to off. If one option is on, set ansi_null_dflt_off or
Set ansi_null_dflt_on. If both options are set to off, SQL Server 2005 uses the SYS. Databases directory View
The value of the is_ansi_null_default_on column.

To make the transact-SQL script more reliable in the database containing different null settings, it is best to always create table and alter
Specify null or not null in the table statement.

Set ansi_null_dflt_off is set during execution or runtime, rather than during analysis.