其實本來這個問題沒有什麼好說的，今天優化的時候遇到一個SQL語句，因為比較有意思，所以我截取、簡化了SQL語句，演示給大家看，如下所示
declare @bamboo_Code varchar(3);
set @bamboo_Code='-01';
SELECT DISTINCT yarn_lot
FROM dbo.rsjob WITH ( nolock )
WHERE RIGHT(ges_no, 3) = @bamboo_Code
AND Isnull(yarn_lot, '') <> '';
如上所示，SQL中對列yarn_log 使用了Isnull(yarn_lot, '') <> ''這種寫法，我估計書寫該SQL語句的人應該是深信了“is null 和 is not null 將會導致索引失效”這條網上流傳的教條， 至於這個建議是從哪裡流傳開來，已經無法考證。 那麼我們通過實踐來驗證一下is null 或 is not null 是否會導致索引失效。
表rsjob是一個堆表，在列yarn_lot上建有索引yarn_lot.那麼我們通過實驗來驗證吧
SELECT DISTINCT yarn_lot
FROM dbo.rsjob WITH(nolock)
WHERE yarn_lot IS NOT NULL;
SELECT DISTINCT yarn_lot
FROM dbo.rsjob WITH(nolock)
WHERE yarn_lot IS NULL
如上所示，不管是IS NULL 或IS NOT NULL都走了索引查找。
declare @bamboo_Code varchar(3);
set @bamboo_Code='-01';
SELECT DISTINCT yarn_lot
FROM dbo.rsjob WITH ( nolock )
WHERE RIGHT(ges_no, 3) = @bamboo_Code
AND Isnull(yarn_lot, '') <> '';
SELECT DISTINCT yarn_lot
FROM dbo.rsjob WITH ( nolock )
WHERE RIGHT(ges_no, 3) = @bamboo_Code
AND yarn_lot IS NOT NULL;
另外我們來看看這兩個原始SQL執行計劃的開銷比值為52:48， 也就是說使用IS NOT NULL性能更好，第一個SQL語句由於做了轉換，導致其走索引掃描，而使用IS NOT NULL則走索引查找。
“is null 和 is not null 將會導致索引失效”這種坑人教條直接被推翻了。所以還在信奉這個教條的人真應該自己動手驗證一下。
下面我們可以通過實驗驗證一下，考慮到在真實環境中，可能情況比較復雜。我們可以構建下面幾個場景。其實真實環境中情況還會復雜一些。但是基本上大致有如下一些場景
情況1：堆表 謂詞上單獨索引列
USE Test;
GO
DROP TABLE TEST;
GO
CREATE TABLE TEST (OBJECT_ID INT, NAME VARCHAR(12));
CREATE INDEX PK_TEST ON TEST(OBJECT_ID) INCLUDE(NAME);
DECLARE @Index INT =0;
WHILE @Index < 10000
BEGIN
INSERT INTO TEST
SELECT @Index, 'kerry'+ CAST(@Index AS VARCHAR);
SET @Index = @Index +1;
END
INSERT INTO TEST
SELECT NULL, 'only test1' UNION ALL
SELECT NULL, 'only test2'
UPDATE STATISTICS TEST WITH FULLSCAN;
SELECT * FROM TEST WHERE OBJECT_ID IS NULL;
SELECT * FROM TEST WHERE OBJECT_ID IS NOT NULL;
刪除索引，建立如下索引。如下所示
DROP INDEX PK_TEST ON TEST;
CREATE INDEX PK_TEST ON TEST(OBJECT_ID)
由此可見IS NULL 或IS NOT NULL的執行計劃即與索引有關系，還跟數據分布有一定關系。
情況2：堆表 謂詞上無索引
USE Test;
GO
DROP TABLE TEST;
GO
CREATE TABLE TEST (OBJECT_ID INT, NAME VARCHAR(12));
DECLARE @Index INT =0;
WHILE @Index < 10000
BEGIN
INSERT INTO TEST
SELECT @Index, 'kerry'+ CAST(@Index AS VARCHAR);
SET @Index = @Index +1;
END
INSERT INTO TEST
SELECT NULL, 'only test1' UNION ALL
SELECT NULL, 'only test2'
UPDATE STATISTICS TEST WITH FULLSCAN;
SELECT * FROM TEST WHERE OBJECT_ID IS NULL;
SELECT * FROM TEST WHERE OBJECT_ID IS NOT NULL;
如上所示，如果一個堆表沒有建立任何索引，那麼使用IS NULL 或IS NOT NULL肯定要走全表掃描，不過這不在我們的討論范圍之內。然後我們看看將索引建立在其它字段上（主要是為了與聚集索引表對比），它依然全表掃描。
CREATE INDEX PK_TEST ON TEST(OBJECT_ID) INCLUDE(NAME);
INSERT INTO TEST
SELECT 10000, NULL UNION ALL
SELECT 10001, NULL ;
SELECT * FROM TEST WHERE NAME IS NULL;
SELECT * FROM TEST WHERE NAME IS NOT NULL;
情況3：堆表 聯合索引列
USE Test;
GO
DROP TABLE TEST;
GO
CREATE TABLE TEST (OBJECT_ID INT, NAME VARCHAR(12), AGE INT);
CREATE INDEX IDX_TEST_N1 ON TEST(NAME, AGE);
DECLARE @Index INT =0;
WHILE @Index < 10000
BEGIN
INSERT INTO TEST
SELECT @Index, 'kerry'+ CAST(@Index AS VARCHAR), floor(rand()*100) ;
SET @Index = @Index +1;
END
INSERT INTO TEST
SELECT NULL, 'only test1', 12 UNION ALL
SELECT NULL, 'only test2',24
UPDATE STATISTICS TEST WITH FULLSCAN;
SELECT * FROM TEST WHERE NAME IS NULL;
SELECT * FROM TEST WHERE NAME IS NOT NULL;
如果聯合索引中，謂詞位於聯合索引的第二或更後位置，那麼又是什麼情況？ 從下面我們可以看到，SQL走全表掃描了。
DROP INDEX IDX_TEST_N1 ON TEST;
CREATE INDEX IDX_TEST_N1 ON TEST( AGE,NAME);
UPDATE STATISTICS TEST WITH FULLSCAN;
4 聚集索引表 單獨索引列
USE Test;
GO
DROP TABLE TEST;
GO
CREATE TABLE TEST (OBJECT_ID INT, NAME VARCHAR(12));
CREATE CLUSTERED INDEX PK_TEST ON TEST(OBJECT_ID)
DECLARE @Index INT =0;
WHILE @Index < 10000
BEGIN
INSERT INTO TEST
SELECT @Index, 'kerry'+ CAST(@Index AS VARCHAR);
SET @Index = @Index +1;
END
INSERT INTO TEST
SELECT NULL, 'only test1' UNION ALL
SELECT NULL, 'only test2'
SELECT * FROM TEST WHERE OBJECT_ID IS NULL;
SELECT * FROM TEST WHERE OBJECT_ID IS NOT NULL;
如果我在列NAME上面使用IS NULL 或IS NOT NULL進行查詢，你會發現執行計劃從聚集索引查找變為了聚集索引掃描。
INSERT INTO TEST
SELECT 10000, NULL UNION ALL
SELECT 10001, NULL ;
SELECT * FROM TEST WHERE NAME IS NULL;
SELECT * FROM TEST WHERE NAME IS NOT NULL;
4 聚集索引表 聯合索引列
USE Test;
GO
DROP TABLE TEST;
GO
CREATE TABLE TEST (OBJECT_ID INT, NAME VARCHAR(12), AGE INT);
CREATE CLUSTERED INDEX PK_TEST ON TEST(OBJECT_ID)
DECLARE @Index INT =0;
WHILE @Index < 10000
BEGIN
INSERT INTO TEST
SELECT @Index, 'kerry'+ CAST(@Index AS VARCHAR), floor(rand()*100) ;
SET @Index = @Index +1;
END
INSERT INTO TEST
SELECT 10001, 'NULL', 12 UNION ALL
SELECT 10002, 'NULL',24
CREATE INDEX IDX_TEST_N2 ON TEST(NAME,AGE);
UPDATE STATISTICS TEST WITH FULLSCAN;
如果聯合索引中，謂詞位於不位於第一列，那麼IS NULL 或IS NOT NULL有會不會走索引呢？
DROP INDEX IDX_TEST_N2 ON TEST;
CREATE INDEX IDX_TEST_N2 ON TEST(AGE,NAME);
UPDATE STATISTICS TEST WITH FULLSCAN;
如上所示，它從索引查找變成索引掃描了。
小結： 1：“is null 和 is not null 將會導致索引失效”這種教條完全是狗屎，SQL Server的索引是包含了null 值，而Oracle的索引是不包含null值的。不同數據庫情況有所不同，不要生搬硬套。
2：如果謂詞上面建立有索引的話，基本上都會走索引，至於是走索引查找還是索引掃描與索引類型有一定關系，也與字段位於聯合索引中位置有關系。另外，數據分布傾斜得非常厲害也會導致其走全表掃描而不走索引，但是這並不是說IS NULL 和 IS NOT NULL導致索引失效。有一點非常重要，通過觀察SQL語句而推斷執行計劃是很不現實的，需要綜合考察SQL語句所涉及表的索引、數據分布、統計信息，才能綜合判斷，用通俗的話來說要結合具體場景。
