love fish大鵬一曰同風起，扶搖直上九萬里

SQL Server的空值處理策略

大多數集合函數都能在計算時消除空值；COUNT函數則屬于例外。對包含空值的一個列使用COUNT函數，空值會從計算中消除。但假如COUNT函數使用一個星號，它就計算所有行，而不管是否存在空值。

如果希望COUNT函數對給定列的所有行（包括空值）進行計數，請使用ISNULL函數。ISNULL函數會將空值替換成有效的值。

事實上，對集合函數來說，如果空值可能導致錯誤結果，ISNULL函數就非常有用。記住在使用一個星號時，COUNT函數會對所有行進行計算。下例演示了空值在AVG和COUNT集合函數中的影響：

    SET NOCOUNT ON

    GO

    CREATE TABLE xCount

    (pkey1 INT IDENTITY NOT NULL

    ??? CONSTRAINT pk_xCount PRIMARY KEY,

    Col1 int NULL)

    GO

    INSERT xCount (Col1) VALUES (10)

    GO

    INSERT xCount (Col1) VALUES (15)

    GO

    INSERT xCount (Col1) VALUES (20)

    GO

    INSERT xCount (Col1) VALUES (NULL)

    GO

    SELECT AVG(Col1) AvgWithoutIsNullFunctionOnCol1,

    AVG(ISNULL(Col1,0)) AvgWithIsNullFunctionOnCol1,

    COUNT(Col1) NoIsNullFunctionOnCol1 ,

    COUNT(ISNULL(Col1,0)) UsingIsNullFunctionOnCol1,

    Count(*) UsingAsterisk

    FROM xCount

    GO

    DROP TABLE xCount

    GO


    OUTPUT:

AvgWOIsNullFnctnCol1 AvgWIsNullFnctnCol1 WOIsNullFnctnCol1

WIsNullFnctnCol1 UsingAsterisk

 ---------------- ------------- -------------- ------------  ---------

15?????????????11????????????????3?????????? 4?????????4

恰當使用空表值

				
SQL Server可能出現一種特殊情況：在引用父表的一個表中，因為不允許空值，所以“聲明引用完整性”（DRI）可能不會得到強制。即使父表不包含空值，在子表引用了父表主鍵約束或惟一約束的列中，也可能包含空值。

				
假如來自父表的值目前未知，就不會有任何問題。例如，父表可能是一個地址表，而子表可能包含聯系信息。由于許多原因，可能暫時不知道要傳給父表的聯系地址。這是一種基于時間的問題，空值在其中或許是合適的。
				
如下例所示，我們創建父表，并在其中插入兩個值。
				
?SET NOCOUNT ON

    GO
CREATE TABLE Parent
(pkey1 INT IDENTITY NOT NULL
??? CONSTRAINT pkParent PRIMARY KEY,
col1 INT NULL)
GO
INSERT Parent (col1) VALUES (284)
GO
INSERT Parent (col1) VALUES (326)
GO
?

				
以下代碼則創建子表，并在引用父表的列中插入一個空值。 
?CREATE TABLE Child

    (pkey1 INT IDENTITY
CONSTRAINT pkChild PRIMARY KEY,
Parentpkey1 INT NULL
CONSTRAINT fkChildParent FOREIGN KEY
REFERENCES Parent(pkey1),
col1 INT NULL)
GO
INSERT Child (Parentpkey1, col1) VALUES (null,2)
GO
?
但在以下代碼中，要同時從父表和子表選擇值。雖然父表不包含空值，但在子表引用了父表的那個列中，將允許一個空值。
然后丟棄所有表，清除這個演示所用的數據庫對象。
?SELECT * FROM Child
GO
SELECT * FROM Parent
GO
DROP TABLE Child, Parent
GO





檢查數據有效性
在可以為空的外鍵中檢查數據的有效性
<a target="_top" ><img src="http://m.cn.2mdn.net/1280511/SCH_300x250_v02.gif" border="0" /></a>
如果由兩個列共同組成主鍵，而且一個子表將主鍵作為可為空值的外鍵來繼承，就可能得到錯誤的數據。可在一個外鍵列中插入有效的值，但在另一個外鍵列中插入空值。然后，可添加一個數據表檢查約束，在可為空的外鍵中檢查數據的有效性。
任何多列外鍵都可能遇到同樣的問題。所以，你需要添加一個檢查約束來檢測異常。最初，檢查約束將檢查構成外鍵的所有列中可能為空的值。檢查約束還要檢查這些列中不能為空的值。如兩個檢查都通過，問題就解決了。
以下示范腳本展示了這樣的一個異常，以及如何用檢查約束來糾正它。
    ?
SET NOCOUNT ON
GO
CREATE TABLE parent (pkey1 INT IDENTITY NOT NULL, pkey2 INT
NOT NULL, col1 INT NULL,
CONSTRAINT pk_parent PRIMARY KEY NONCLUSTERED ( pkey1, pkey2))
GO
INSERT parent (pkey2) VALUES ( 2 )
INSERT parent (pkey2) VALUES ( 85 )
INSERT parent (pkey2) VALUES ( 41 )
INSERT parent (pkey2) VALUES ( 11 )
GO
SELECT * FROM parent
GO
CREATE TABLE child (cpkey1 INT IDENTITY NOT NULL
CONSTRAINT pk_child PRIMARY KEY NONCLUSTERED (cpkey1),
pkey1 INT NULL, pkey2 INT NULL, col1 INT NULL,
CONSTRAINT fk_parent_child FOREIGN KEY (pkey1, pkey2)
REFERENCES parent (pkey1, pkey2))
GO
INSERT child (pkey1, pkey2) VALUES ( null, 85 )
GO
SELECT * FROM child
GO
DELETE child
GO
ALTER TABLE child WITH NOCHECK
ADD CONSTRAINT ck_fk_parent_child CHECK
((pkey1 IS NOT NULL AND pkey2 IS NOT NULL) OR
(pkey1 IS NULL AND pkey2 IS NULL) )
GO
INSERT child (pkey1, pkey2) VALUES ( null, 11 )
GO
DROP TABLE child, parent
GO
?

空值是所有數據庫開發者和管理員都要遇到的。所以，要想開發成功的應用程序，必須知道如何處理這些值。本文和你分享了空值處理的一些技巧和技術。 

posted on 2006-09-15 10:22 liaojiyong 閱讀(416) 評論(0)  編輯  收藏 所屬分類: MSSQL