Sqlserver：Null值

 

NULL：未知資料

 

NULL的運算：有null的任何運算都是null，除了以下：

• set ansi_null of 時：null = null 的結果為 true
• set concat_null_yields_null off時：null作為空白字串處理（但如果參與運算的所有參數都是null，結果當然是null了）

 

NULl函數

is [not] null： 只能用做條件判斷運算式，是否是null？是 條件為true，不是 條件為false

isnull()：函數，如果第一個參數是null，則用第二個參數的值替換第一個參數的值作為函數的傳回值。記住：第二個參數的類型必須和第一個相容！

nullif()：函數，如果兩個參數值相等、有一個參數是null、或兩個參數是null，函數傳回值是null；否則 返回第一個參數的值

 

SET ANSI_NULLS { ON | OFF }

SQL-92 標準要求在對空值進行等於 (=) 或不等於 (<>) 比較時取值為 FALSE。當 SET
ANSI_NULLS 為 ON 時，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT
語句仍返回零行。即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT
語句仍會返回零行。

當 SET ANSI_NULLS 為 OFF 時，等於 (=) 和不等於 (<>) 比較子不遵從 SQL-92
標準。使用 WHERE column_name = NULL 的 SELECT 語句返回 column_name 中包含空值的行。使用 WHERE
column_name <> NULL 的 SELECT 語句返回列中包含非空值的行。此外，使用 WHERE column_name
<> XYZ_value 的 SELECT 語句返回所有不為 XYZ_value 也不為 NULL 的行。

當 SET ANSI_NULLS 為 ON 時，所有對空值的比較均取值為 UNKNOWN。當 SET ANSI_NULLS 為 OFF
時，如果資料值為 NULL，則所有資料對空值的比較將取值為 TRUE。如果未指定，則應用當前資料庫的 ANSI nulls 選項的設定。有關 ANSI
nulls 資料庫選項的詳細資料，請參閱 ALTER
DATABASE (Transact-SQL) 和。

僅當某個比較運算元是值為 NULL 的變數或文字 NULL 時，SET ANSI_NULLS ON
才會影響比較。如果比較雙方是列或複合運算式，則該設定不會影響比較。

為使指令碼按預期運行，不管 ANSI nulls 資料庫選項或 SET ANSI_NULLS 的設定如何，請在可能包含空值的比較中使用
IS NULL 和 IS NOT NULL。

對於預存程序，SQL Server 使用最初建立預存程序時的 SET ANSI_NULLS 設定值。無論隨後何時執行預存程序，SET
ANSI_NULLS 的設定都還原為其最初使用的值並生效。當在預存程序內部調用 SET ANSI_NULLS 時，其設定不更改。

在執行分散式查詢時應將 SET ANSI_NULLS 設定為 ON。

對計算資料行或索引檢視表建立或更改索引時，SET ANSI_NULLS 也必須為 ON。如果 SET ANSI_NULLS 為
OFF，則針對錶（包含計算資料行或索引檢視表的索引）的 CREATE、UPDATE、INSERT 和 DELETE 語句將失敗。SQL Server
將返回一個錯誤訊息，該錯誤訊息會列出所有違反所需值的 SET 選項。另外，在執行 SELECT 語句時，如果 SET ANSI_NULLS 為 OFF，則
SQL Server 將忽略計算資料行或視圖的索引值並解析 Select 語句，就好像表或視圖沒有這樣的索引一樣。

SQL Server 的 SQL 本機用戶端 ODBC 驅動程式和 SQL 本機用戶端 OLE DB 提供者在串連時會自動將
ANSI_NULLS 設定為 ON 。該設定可以在 ODBC 資料來源、ODBC 串連屬性或 OLE DB 串連屬性（它們在串連到 SQL Server
之前在應用程式中設定）中進行配置。對於來自 DB-Library 應用程式的串連，SET ANSI_NULLS 的預設值為 OFF。

當 SET ANSI_DEFAULTS 為 ON 時，將啟用 SET ANSI_NULLS。

SET ANSI_NULLS 的設定是在執行或運行時設定，而不是在分析時設定。

 

 

SET ANSI_NULL_DFLT_ON {ON | OFF}

當資料庫的 ANSI null default 選項為 false 時，修改會話的行為以覆蓋新列的預設為空白性。

僅當在 CREATE TABLE 和 ALTER TABLE 語句中沒有指定列的為空白性時，該設定才能影響新列的為空白性。SET
ANSI_NULL_DFLT_ON 為 ON 時，如果沒有顯式指定列的為空白性狀態，則使用 ALTER TABLE 和 CREATE TABLE
語句建立的新列可以使用空值。SET ANSI_NULL_DFLT_ON 對使用顯式 NULL 或 NOT NULL 建立的列無效。

不能同時將 SET ANSI_NULL_DFLT_OFF 和 SET ANSI_NULL_DFLT_ON 設定為
ON。如果將一個選項設定為 ON，則將另一個選項設定為 OFF。因此，可以將 ANSI_NULL_DFLT_OFF 或 ANSI_NULL_DFLT_ON
設定為 ON，或者將二者都設定為 OFF。如果有一個選項為 ON，則該設定（SET ANSI_NULL_DFLT_OFF 或 SET
ANSI_NULL_DFLT_ON）生效。如果將這兩個選項都設定為 OFF，則 SQL Server 2005 將使用 sys.databases 目錄檢視中
is_ansi_null_default_on 列的值。

為使 Transact-SQL 指令碼在含有不同為空白性設定的資料庫中獲得更可靠的操作，最好始終在 CREATE TABLE 和 ALTER
TABLE 語句中指定 NULL 或 NOT NULL。

進行串連時，SQL Server 的 SQL Native Client ODBC 驅動程式和 SQL Native Client
OLE DB 提供者會自動將 ANSI_NULL_DFLT_ON 設定為 ON。對於來自 DB-Library 應用程式的串連，SET
ANSI_NULL_DFLT_ON 的預設設定為 OFF。

SET ANSI_DEFAULTS 為 ON 時，將啟用 SET ANSI_NULL_DFLT_ON。

SET ANSI_NULL_DFLT_ON 的設定是在執行或運行時設定的，而不是在分析時設定的。

 

SET ANSI_NULL_DFLT_OFF { ON | OFF }

資料庫的 ANSI null default 選項為 true 時，更改會話行為以覆蓋新列的預設為空白性。

僅當 CREATE TABLE 和 ALTER TABLE 語句中沒有指定列的為空白性，此設定才能影響新列的為空白性。預設情況下，當 SET
ANSI_NULL_DFLT_OFF 為 ON 時，如果沒有顯式指定列的為空白性狀態，則使用 ALTER TABLE 和 CREATE TABLE
語句建立的新列將為 NOT NULL。SET ANSI_NULL_DFLT_OFF 對使用顯示 NULL 或 NOT NULL 建立的列無效。

不能同時將 SET ANSI_NULL_DFLT_OFF 和 SET ANSI_NULL_DFLT_ON 設定為
ON。如果將一個選項設定為 ON，則將另一個選項設定為 OFF。因此，可以將 ANSI_NULL_DFLT_OFF 或 SET
ANSI_NULL_DFLT_ON 設定為 ON，或者將二者都設定為 OFF。如果有一個選項為 ON，則該設定（SET ANSI_NULL_DFLT_OFF 或
SET ANSI_NULL_DFLT_ON）生效。如果將這兩個選項都設定為 OFF，則 SQL Server 2005 將使用 sys.databases 目錄檢視中
is_ansi_null_default_on 列的值。

為使 Transact-SQL 指令碼在包含不同為空白性設定的資料庫中獲得更可靠的操作，最好始終在 CREATE TABLE 和 ALTER
TABLE 語句中指定 NULL 或 NOT NULL。

SET ANSI_NULL_DFLT_OFF 的設定是在執行或運行時設定的，而不是在分析時設定的。