SQL Server Null的比較運算

今天我在寫sql的時候發現用

UPDATE dbo.tblInvTransaction
SET Area_Type = 'Gross'，
WHERE (Area_Type  = null)

結果為零。

然後查到下面這個文章
改成

UPDATE dbo.tblInvTransaction
SET Area_Type = 'Gross'
WHERE (Area_Type is NULL )
就ok了

前幾天寫一個資料庫查詢程式，碰到的一個問題，是關於SQL Server的Null值的比較運算的。一般情況下我們查詢空值或者非空值的時候，用的是is null/is not null，而很少用=/<>。但是在我的這個程式中，沒有用is這樣的關鍵字，而是用=/<>這樣的比較元算符號，這就碰到了一些問題。
問題起源於一個Web查詢頁面，因為問題比較複雜的，所以簡化一下來說明。
在頁面上使用者可以自由選擇資料表的某些欄位，填寫該欄位的查詢條件，先是選擇比較子號（=，<>等），然後填寫值。提交之後，就需要建立一個SQL語句，查詢條件的各部分由不同的程式模組建立。這裡涉及兩個程式模組，一個模組根據提交建立比較子號，一個模組負責建立比較值模組。在建立值模組中有這樣一個規則，“如果提交的值是空的，把該值設為Null”。
但是我發現，如果比價值為Null的時候，同樣一個SQL查詢語句放在預存程序裡邊查詢和通過應用程式直接查詢的結果是不一樣的。
查了查SQL Server文檔，發現Null值的比較運算，存在兩種規則：
在SQL2000中Null值的比較運算有兩種規則。一種是ANSI SQL（SQL-92）規定的Null值的比較取值結果都為False，既Null=Null取值也是False。另一種不準循ANSI SQL標準，即Null=Null為True。
以一張表T的查詢為例。

表T存在下面的資料：
RowId Data
--------------
1 'test'
2 Null
3 'test1'

按照ANSI SQL標準，下面的兩個查詢都不返回任何行：
Query1: select * from T where Data=null
Query2: select * from T where Data<>null
而按照非ANSI SQL標準，查詢1將返回第二行，查詢2返回1、3行。
ANSI SQL標準中取得Null值的行需要用下面的查詢：
select * from T where Data is null
反之則用is not null。由此可見非ANSI SQL標準中Data=Null等同於Data Is Null，Data<>Null等同於Data Is Not Null。

而控制採用那一種規則，需要使用命令SET ANSI_NULLS [ON/OFF]。ON值採用ANSI SQL標準，OFF值採用非標準模式。另外SET ANSI_DEFAULTS [ON/OFF]命令也可以實現標準的切換，只是這個命令控制的是一組符合SQL-92標準的設定，其中就包括Null值的標準。

預設情況下，資料庫管理程式（DB-Library）是SET ANSI_NULLS為OFF的。但是我們的大多數應用程式，都是通過ODBC或者OLEDB來訪問資料庫的，作為一種開放相容的資料庫訪問程式，或許是相容性的考慮，SET ANSI_NULLS值設定為ON。這樣一來帶來的一些問題是需要注意的。像預存程序或者自訂函數這樣的應用程式都是基於DB-Library的，預設情況下，SET ANSI_NULLS為OFF，並且在這樣的程式中，不能使用SET ANSI_NULLS在一個環境中修改規則，只能修改資料庫配置參數。

考慮下面這種情況。
你的應用程式使用ADODB來訪問資料庫，採用OleDb或者ODBC資料提供者。對於前面的查詢1：
select * from T where Data=null
你可以直接發送命令取得結果集，也可以把它放到預存程序當中。但是他們的查詢結果是不一樣的。如果直接使用查詢命令，什麼結果也沒有，而如果訪問預存程序，你獲得第2行的資料。

我寫了一個.Net程式來驗證這一點。同時也為了驗證.Net SqlClient的SET ANSI_NULLS的設定，由於SqlClient不是通過OleDb或者ODBC這些資料提供者來訪問SQL Server，而是直接對SQL Server進行訪問，本來我以為它會採用SQL Server預設的設定，但是結果恰恰相反，它的預設設定和OleDb、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"; //預存程序中是一樣的SQL語句
   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();
   }
  
}
}

它有一個參數，根據參數採用不同的參數值採用不同的資料庫訪問程式。命令對象作了兩次查詢，一次是SQL查詢命令，一次是調用預存程序。語句都是一樣，但是結果不一樣。