Comparison of SQL Server null

I found that

Update DBO. tblinvtransaction
Set area_type = 'groups ',
Where (area_type = NULL)

The result is zero.

Then find the following article
Change

Update DBO. tblinvtransaction
Set area_type = 'group'
Where (area_type is null)
OK.

A problem encountered when I wrote a database query program a few days ago was about the comparison of the null values of SQL Server. In general, when we query null or non-null values, we use is null/is not null, but seldom use =/<>. However, in my program, the keyword "is not used, but the comparative meta-computing symbol" =/<> is used, which leads to some problems.
The problem originated from a web query page. The problem is complicated.
On the page, you can select certain fields of the data table and enter the query conditions for this field. First, select the comparison operator number (=, <>) and then enter the value. After submission, you need to create an SQL statement. Each part of the query condition is created by different program modules. Two program modules are involved here. One module creates a comparison operator number based on the submission, and the other module creates a comparison value module. In the create value module, there is a rule, "If the submitted value is empty, set this value to null ".
However, I found that when the ratio is null, the same SQL query statement is stored in the stored procedure and the query results through the application are different.
I checked the SQL server document and found that there are two rules for comparing null values:
There are two rules for comparing null values in SQL2000. One is that the comparison results for the null value specified by ansi SQL (SQL-92) are both false, both null = NULL and false. In addition, the ansi SQL standard is not followed, that is, null = NULL is true.
Take the query of a table t as an example.

Table t contains the following data:
Rowid data
--------------
1 'test'
2 null
3 'test1'

According to the ansi SQL standard, the following two queries do not return any rows:
Query1:Select * from T where Data = null
Query2:Select * from T where Data <> null
According to the non-ansi SQL standard, query 1 returns the second row, and query 2 returns the first and third rows.
The following query is required for the row whose null value is obtained in the ansi SQL standard:
Select * from t where data is null
Otherwise, is not null is used. In non-ansi SQL standards, data = NULL is equivalent to data is null, and data <> null is equivalent to data is not null.

The command is required to control the rule.Set ansi_nulls [ON/OFF]. The on value adopts the ansi SQL standard, and the off value adopts the non-standard mode. In additionSet ansi_defaults [ON/OFF]The command can also implement standard switching, But it controls a set of settings that comply with the SQL-92 standard, including the null value standard.

By default (DB-Library) Is SET ANSI_NULLS to OFF. However, most of our applications access the database through ODBC or OLEDB. As an open and compatible database access program, the SET ANSI_NULLS value may be SET to ON. In this way, you need to pay attention to some problems. Applications such as stored procedures and user-defined functions are based on DB-Library. By default, SET ANSI_NULLS is OFF, and in such programs, you cannot use SET ANSI_NULLS to modify rules in an environment. You can only modify Database configuration parameters.

Consider the following situation.
Your application uses ADODB to access the database and OleDb or ODBC data provider. For the previous query 1:
Select * from t where data = NULL
You can directly send a command to obtain the result set, or place it in the stored procedure. However, their query results are different. If you directly use the query command, there is no result, and if you access the stored procedure, you get 2nd rows of data.

I wrote a. Net program to verify this. It is also used for verification. SET ANSI_NULLS settings of Net SqlClient. Because SqlClient does not access SQL Server through OleDb or ODBC data providers, it directly accesses SQL Server, I thought it would use the default settings of SQL Server, but the result is exactly the opposite. Its default settings are the same as those of OleDb and ODBC.

Using System;
Using System. Data;
Using System. Data. SqlClient;
Using System. Data. OleDb;
Using System. Data. Odbc;
Public class AnsiNullsTest {
Public static void Main (String [] args ){
IDbConnection conn;
String connType = "SqlClient ";
If (args. Length> 0) connType = args [0];
If (connType. ToUpper () = "OLEDB "){
Console. WriteLine ("Connection Type: OLEDB ");
Conn = new OleDbConnection ("Provider = SQLOLEDB.1; User ID = sa; PWD = test; Initial Catalog = TEST; Data Source = TEST ");
} Else if (connType. ToUpper () = "ODBC "){
Console. WriteLine ("Connection Type: ODBC ");
Conn = new OdbcConnection ("Driver = {SQL Server}; UID = sa; PWD = test; Database = TEST; Server = TEST ");
} Else {
Console. WriteLine ("Connection Type: SQLClient ");
Conn = new SqlConnection ("Server = TEST; Database = TEST; User ID = sa; PWD = test ");
}
Test (conn );
}
Public static void Test (IDbConnection conn ){
String query1 = "select 'test' where null = null ";
String query2 = "exec p_Test"; // The same SQL statement in the Stored Procedure
IDbCommand cmd;
IDataReader reader;
Console. WriteLine ("print 'test' set ansi_nulls off ");
Try {
Cmd = conn. CreateCommand ();
Conn. Open ();
Cmd. CommandText = query1;
Reader = cmd. ExecuteReader ();
Console. WriteLine ("command:" + query1 );
While (reader. Read ()){
Console. WriteLine ("result:" + reader [0]. ToString ());
}
Reader. Close ();
Cmd. CommandText = query2;
Reader = cmd. ExecuteReader ();
Console. WriteLine ("command:" + query2 );
While (reader. Read ()){
Console. WriteLine ("result:" + reader [0]. ToString ());
}
Reader. Close ();
}
Catch (Exception ex ){
Console. WriteLine (ex. Message );
}
Finally {
Conn. Close ();
}

}
}

It has a parameter that uses different database access programs based on different parameter values. The command object is queried twice. One is an SQL query command, and the other is a stored procedure call. The statements are the same, but the results are different.