摘要:1.Null表示Unknow;
2.聚合運算;
3.性能;
4.NO NULL!
1.Null表示Unknow:
SQL基于三值邏輯true、false、unknow；Null與任何類型的值進行比較(=、>、>=、<、<=)或運算(+、-、*、/、In)的結果為Unknow；看起來好像很容易理解，但在使用中我們卻經常容易忽略細節，下面具體的測試：
--Query1.初始化測試數據
Create Table #T1(Col nvarchar(10) default null)
INSERT INTO #T1 default values;
INSERT INTO #T1 values('a');
INSERT INTO #T1 values('b');
Create Table #T2(Col nvarchar(10) default null)
INSERT INTO #T2 default values;
INSERT INTO #T2 values('a');
INSERT INTO #T2 values('b');
INSERT INTO #T2 values('c'); //比#T1多一條記錄
現在希望找出#T2中比#T1中多出的記錄(Y的，這還不簡單，這不侮辱咱的智商嗎...一句就Not IN或者NOT Exists就搞定了；肉眼金睛：#T2比#T1中僅多一條記錄)，于是我們寫出下面的兩個版本的SQL：
(1). NOT IN
SELECT * FROM #T2
WHERE Col NOT IN(SELECT Col FROM #T1)
很遺憾，這句SQL失敗了，得到了空結果集！于是我們開罵：頂In個肺，IN太垃圾了，效率又低；我們應該改用NOT EXISTS！
(2). NOT EXISTS (=)
SELECT * FROM #T2
WHERE NOT EXISTS(SELECT * FROM #T1 WHERE #T1.Col=#T2.Col)
很遺憾，這句SQL又失敗了，找出了兩條記錄！太打擊了，靠～
說明：可以設置當前會話SET ANSI_Nulls OFF，來使Null==Null；
2.聚合運算:
下面的SQL，看看輸出結果多少：
--Query1中已創建#T1并插入測試數據
SELECT Count(Col) FROM #T1
SELECT Count(*) FROM #T1
#T1就只有一列Col，憑直覺，Count(Col)和Count(*)的結果應該是相等的吧。
等等，別被直覺給忽悠了，上面的語句執行得到的結果是：Count(Col)=2，Count(*)=3。
說明：Count(columnName)、Sum、AVG、Max()、Min()等聚集函數會忽略null值；但Count(*)不會忽略值全部為null的行;
3.性能
SQL Server通過系統表SysObject的位圖列來記錄表中列是否允許為null，所以處理允許為null的列時需要耗費一定的資源；(當然也有個特例，譬如某列Column不允許為空，則根據Column IS NULL來進行查詢過濾時，會直接返回空集，而不會傻到去表中取數據判斷；但這樣的過濾條件是沒有任何現實意義的。)
某些數據庫上，例如Oracle，使用IS NULL進行過濾時，將不會使用索引。http://www.eygle.com/archives/2006/02/index_null_hints_explain.html
引自上面的鏈接："由于B*Tree索引不存儲Null值,所以在索引字段允許為空的情況下,某些Oracle查詢不會使用索引。很多時候,我們看似可以使用全索引掃描(Full Index Scan)的情況,可能Oracle就會因為Null值的存在而放棄索引。在此情況下即使使用Hints,Oracle也不會使用索引,其根本原因就是因為Null值的存在。"
我在SQL Server 2000上測試，貌似是可以用到索引(暫還沒有搞清楚是咋實現的)，下面是具體的測試過程：
(1) 測試SQL:
IF(Object_ID('TestNull') IS NOT NULL)
DROP TABLE TestNull;
CREATE TABLE [TestNull] (
[ID] [int],
[Value] [nchar] (1000) NULL
);
Create CLUSTERED Index ID_Index ON TestNull(ID);
--插入測試數據
DECLARE @Index int;
SET @Index=-1;
WHILE(@Index<10000)
BEGIN
INSERT INTO TestNull([ID],Value) Values(@Index, 10000-@Index);
SET @Index=@Index+1;
END
INSERT INTO TestNull([ID],Value) Values(NULL,10000);
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT * FROM TestNull WHERE ID=-1
SET STATISTICS TIME OFF
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS TIME ON
SELECT * FROM TestNull WHERE ID IS NULL
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
(2). 測試結果:
<1> SELECT * FROM TestNull WHERE ID=-1
（所影響的行數為 1 行）
表 'TestNull'。掃描計數 1，邏輯讀 3 次，物理讀 1 次，預讀 0 次。
SQL Server 執行時間: CPU 時間 = 0 毫秒，耗費時間 = 0 毫秒。
<2> SELECT * FROM TestNull WHERE ID IS NULL
（所影響的行數為 1 行）
表 'TestNull'。掃描計數 1，邏輯讀 3 次，物理讀 1 次，預讀 0 次。
SQL Server 執行時間: CPU 時間 = 0 毫秒，耗費時間 = 1 毫秒。
從執行計劃和統計信息來看，這里是使用了索引查找；從SQL Server 2005的聯機叢書中，我也找到下面這句：For indexing purposes, NULL values compare as equal.
4.NO Null！
在我以前參與過的幾個項目中，都沒有在使用Null的地方進行規范，譬如一個允許為null的nvarchar(xx)類型的字段，有些記錄中值為null，有些記錄中值為空字符串''，每次進行條件過時就要同時寫上IS NULL OR ColumnName=''，nvarchar算是比較幸運的，因為Convert.ToString(DBNull)可以將DBNull轉成空字符串；但相比之下，int或者DateTime就沒有這么幸運了，Convert.ToInt32(DBNull)和Convert.ToDateTime(DBNull)會拋出異常，以至于我們經常需要寫如下的代碼來處理Null值：
object columnValue = ReadFromDB;
DateTime? time = null;
if(columnValue==DBNull)
time = Convert.ToDateTime(columnValue);
這樣使用起來相當地不爽；可以看到，nvarchar中，NULL其實和空字符串('')代表同樣的含義，我們完全可以用''來代替NULL；DateTime中，我們也可以用特殊值'9999-12-31'來代替空值，從而可以在C#中的統一處理，而不用加個扯淡判斷(columnValue==DBNull)；我們完全可以用一些特殊值(系統的正常邏輯中，不會用到的值，例如Int.MinValue、0、-1、'N/A'、''、'1900-01-01'、'9999-12-31'等)來替代null，以避免使用null！
這樣替換后，也帶來一個問題：Null不參與聚集函數(Count(*)除外)的運算，如何讓替換后的特殊值也不參與聚集函數的運算呢？這里，我們可以使用NullIf來實現：
SELECT SUM(NullIF(ColumnName,'-1')) FROM TableName;
NullIF:當列值與替換后的特殊值-1相等時，則返回null；null參與SUM聚集運算時，會被忽略掉，從而達到特殊值不參與聚集函數運算的功能。
浙公網安備 33010602011771號