值班期間研發(fā)同事打來電話，說應(yīng)用有超時，上服務(wù)器上檢查發(fā)現(xiàn)有SQL大批量地執(zhí)行，該SQL消耗IO資源較多，導(dǎo)致服務(wù)器存在IO瓶頸，細看SQL，發(fā)現(xiàn)自己都被整蒙了，不知道這SQL是要干啥，處理完問題趕緊研究下，
當(dāng)ROWNUMBER遇到TOP
。SQL類似于：
WITH T1 AS
(
SELECT TOP ( 100 )
ID ,
ROW_NUMBER() OVER ( ORDER BY C1 ) AS RID
FROM [dbo].[TB002]
)
SELECT *
FROM T1
WHERE T1.RID > (1-1)*2147483647
AND T1.RID < 1*2147483647
第一趕腳是寫這代碼的研發(fā)同事想分頁，但是這每頁的數(shù)據(jù)量有點嚇人�。ㄊ俏姨懶∶�？）
再仔細看下，趕腳又不是分頁，上面還有TOP(100)呢?
如果把TOP(100) 放到CTE外面，很容易理解，根據(jù)RID列過濾完后再取前100行數(shù)據(jù)。
對于上面的TOP(100) 在CTE內(nèi)部SQL執(zhí)行步驟如下
1>對表TB002中C1列排序計算每行的RID值，得到臨時結(jié)果集T1
2>對臨時結(jié)果集T1中數(shù)據(jù)“隨機”取100條(注意：因為CTE中TOP(100) 沒有對應(yīng)ORDER BY 子句，因此無法保證返回的100條數(shù)據(jù)是有序的，即使在不少場景下返回的數(shù)據(jù)是按RID排序的) 得到臨時結(jié)果集T2
3>將臨時結(jié)果集T2的數(shù)據(jù)按照T1.RID > (1-1)*2147483647 AND T1.RID < 1*2147483647 的條件過濾，得到最終結(jié)果集T3
4>強最終結(jié)果集T3返回給客戶端
--=========================華麗分割線=======================================--
在SQL SERVER 世界里，ROW_NUMBER函數(shù)已經(jīng)有些泛濫成災(zāi)，很多不明真相的群眾磕著瓜子就把ROW_NUMBER函數(shù)寫到應(yīng)用查詢中，甚至不少研發(fā)同事(抱歉有些人躺槍了)把ROW_NUMBER函數(shù)用到登峰造極的程度，當(dāng)看到一條SQL里使用到N多ROW_NUMBER函數(shù)和子查詢再加N多大表關(guān)聯(lián)查詢，我都對自己DBA的身份表示懷疑，完全看不懂啊�。�！
--=========================華麗分割線=======================================--
回歸正題，ROW_NUMBER函數(shù)的引入是為了更簡單地實現(xiàn)分頁，SQL SERVER 查詢引擎會將CTE外部的條件引入到CET內(nèi)部，以避免CTE內(nèi)部語句執(zhí)行時訪問“無用”數(shù)據(jù)，如對下面的語句
;WITH T1 AS
(
SELECT ID ,
ROW_NUMBER() OVER ( ORDER BY ID ) AS RID
FROM [dbo].[TB002]
)
SELECT *
FROM T1
WHERE T1.RID > 10
AND T1.RID < 30
由于表TB002上ID有索引，因此查詢會利用索引訪問前30條記錄，丟棄不滿足RID>10的第1到10條數(shù)據(jù)，
電腦資料
《當(dāng)ROWNUMBER遇到TOP》(http://www.szmdbiao.com)。由于這種優(yōu)化的存在，使得查詢無需先執(zhí)行
SELECT ID ,ROW_NUMBER() OVER ( ORDER BY ID ) AS RID FROM [dbo].[TB002]
然后再執(zhí)行WHERE T1.RID > 10 AND T1.RID < 30 的過濾操作。
但如果CTE內(nèi)部加入TOP子句，就使得CTE外部的T1.RID > 10 AND T1.RID < 30條件不能引入到CET內(nèi)部(查詢優(yōu)化器首先得保障返回結(jié)果集的正確性，然后才考慮執(zhí)行的高效性)。對于研發(fā)同事也一樣，他們首先關(guān)注查詢結(jié)果是否正確，然后才考慮查詢效率是否高效，那么引入TOP是否能保證數(shù)據(jù)正確呢？
為了掩飾，我們將查詢做輕微調(diào)整如下：
;WITH T1 AS
(
SELECT TOP(10) ID ,
ROW_NUMBER() OVER ( ORDER BY ID ) AS RID
FROM [dbo].[TB002]
)
SELECT *
FROM T1
WHERE T1.RID > 10
AND T1.RID < 30
我們會悲哀地發(fā)現(xiàn)，查詢返回結(jié)果為空，這顯然不是一個好兆頭，為什么會返回空呢？
輕輕推敲一下，我們就會發(fā)現(xiàn)，CTE內(nèi)部的執(zhí)行結(jié)果總是“巧合”地返回RID為1到10的數(shù)據(jù)，而外部條件RID>10又將這10條數(shù)據(jù)過濾掉，SO返回為空。
PS: 查詢優(yōu)化器真的是“順手”返回前10條數(shù)據(jù)，因為恰好這10條數(shù)據(jù)“在手邊”，不能保證其他場景下也是返回RID為1到10的數(shù)據(jù)，當(dāng)然也不是查詢優(yōu)化器故意“坑人”哈
--=========================華麗分割線=======================================--
至此，我總算明白為啥要將寫SQL的那位兄弟要傳入入2147483647 這么大一個頁數(shù)量，估計是傳小了查不出數(shù)據(jù)，所以一勞永逸傳個最大值，想想也是醉了！
--=========================華麗分割線=======================================
編寫SQL的目的在于實現(xiàn)業(yè)務(wù)需求，而不是顯示個人SQL能力，也沒有“一招鮮吃遍天”可以秒殺所有問題的寫法，在尊重業(yè)務(wù)需求的前提下，依據(jù)業(yè)務(wù)場景，考慮數(shù)據(jù)分布和當(dāng)前以及未來的數(shù)據(jù)量，用盡可能簡單的SQL地實現(xiàn)業(yè)務(wù)需求才是王道。