HoneyHoney

可能大家都习惯了SELECT * FROM TABLE，或者SELECT DATA FROM TABLE WHERE ID = 1。一般来说，无非就是INDEX SEEK，或者CLUSTERED INDEX SCAN。但下面这个例子，却两者都不是。

IF OBJECT_ID('DBO.T1') IS NOT NULL
    DROP TABLE t1

CREATE TABLE T1(
    ID BIGINT,
    ANOTHER_ID INT,
    DATA VARCHAR(200),
    CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED (ID)
)

INSERT INTO T1(ID, ANOTHER_ID,DATA)
SELECT n,
    n%4, 
    CASE WHEN n % 4 = 1 THEN 'pHoEnIx'
                    WHEN n % 4 = 2 THEN 'eRiK'
                    WHEN n % 4 = 3 THEN 'dBe'
                    ELSE 'Papa'
                    END
FROM dbo.getnums(50000)

CREATE NONCLUSTERED INDEX idx_t1_another_id ON t1(another_id)

SELECT ID FROM T1
SELECT DATA FROM T1

对于第二个SELECT query，很明显，DATA不被包含在non-clustered index里面，所以只能够用CLUSTERED INDEX SCAN。但是第一个SELECT query，却是INDEX SCAN。WHY？是错误的执行计划？不是。
事实是，很明显non-clustered index占用的空间比clustered index小，而且non-clustered index的最后一个节点就是ID，不用它用谁。此外，如果一个表上面有多个non-clustered index，SQL SERVER会用INDEX占用最小的INDEX。



如果一切这么简单就好，这样的query单独列出来可能稍微仔细看就发现问题了。继续上面的SQL script

CREATE TABLE T2(
    ID INT
)

INSERT INTO T2(ID)
SELECT n*4
FROM dbo.getnums(50000)

DELETE FROM T2 WHERE NOT EXISTS(SELECT 1 FROM T1 WHERE T1.ID = t2.ID)

当T2的数据级和T1的数据级差不多的时候，SQL SERVER就不会SCAN T2再去T1里面进行CLUSTERED INDEX SEEK。相反，SQL SERVER会对两个表都进行SCAN，再HASH MATCH。这里很隐晦地把SELECT ID FROM TABLE转换成SELECT 1 FROM TABLE WHERE ID ...。满心以为这个很明显的是CLUSTERED INDEX SEEK啊， WHERE ID = 啊。等你看执行计划的时候，你就傻眼了。啥？！INDEX SCAN？？


一旦是INDEX SCAN，一般都是PAGE级别的锁，放在non-clustered index上，一不留神就会导致长时间BLOCKING。