值班期间研发同事打来电话，说应用有超时，上服务器上检查发现有SQL大批量地执行，该SQL消耗IO资源较多，导致服务器存在IO瓶颈，细看SQL，发现自己都被整蒙了，不知道这SQL是要干啥，处理完问题赶紧研究下。
SQL类似于：
WITH T1 AS ( SELECT TOP ( 100 ) ID , ROW_NUMBER() OVER ( ORDER BY C1 ) AS RID FROM [dbo].[TB002])SELECT *FROM T1WHERE T1.RID > (1-1)*2147483647 AND T1.RID < 1*2147483647
第一赶脚是写这代码的研发同事想分页，但是这每页的数据量有点吓人啊（是我太胆小么？）
再仔细看下，赶脚又不是分页，上面还有TOP(100)呢?
如果把TOP(100) 放到CTE外面，很容易理解，根据RID列过滤完后再取前100行数据。
对于上面的TOP(100) 在CTE内部SQL执行步骤如下
1>对表TB002中C1列排序计算每行的RID值，得到临时结果集T1
2>对临时结果集T1中数据“随机”取100条(注意：因为CTE中TOP(100) 没有对应ORDER BY 子句，因此无法保证返回的100条数据是有序的，即使在不少场景下返回的数据是按RID排序的) 得到临时结果集T2
3>将临时结果集T2的数据按照T1.RID > (1-1)*2147483647 AND T1.RID < 1*2147483647 的条件过滤，得到最终结果集T3
4>强最终结果集T3返回给客户端
--=========================华丽分割线=======================================--
在SQL SERVER 世界里，ROW_NUMBER函数已经有些泛滥成灾，很多不明真相的群众磕着瓜子就把ROW_NUMBER函数写到应用查询中，甚至不少研发同事(抱歉有些人躺枪了)把ROW_NUMBER函数用到登峰造极的程度，当看到一条SQL里使用到N多ROW_NUMBER函数和子查询再加N多大表关联查询，我都对自己DBA的身份表示怀疑，完全看不懂啊！！！
--=========================华丽分割线=======================================--
回归正题，ROW_NUMBER函数的引入是为了更简单地实现分页，SQL SERVER 查询引擎会将CTE外部的条件引入到CET内部，以避免CTE内部语句执行时访问“无用”数据，如对下面的语句
;WITH T1 AS ( SELECT ID , ROW_NUMBER() OVER ( ORDER BY ID ) AS RID FROM [dbo].[TB002])SELECT *FROM T1WHERE T1.RID > 10 AND T1.RID < 30
由于表TB002上ID有索引，因此查询会利用索引访问前30条记录，丢弃不满足RID>10的第1到10条数据。
由于这种优化的存在，使得查询无需先执行
SELECT ID ,ROW_NUMBER() OVER ( ORDER BY ID ) AS RID FROM [dbo].[TB002]
然后再执行WHERE T1.RID > 10 AND T1.RID < 30 的过滤操作。
但如果CTE内部加入TOP子句，就使得CTE外部的T1.RID > 10 AND T1.RID < 30条件不能引入到CET内部(查询优化器首先得保障返回结果集的正确性，然后才考虑执行的高效性)。对于研发同事也一样，他们首先关注查询结果是否正确，然后才考虑查询效率是否高效，那么引入TOP是否能保证数据正确呢？
为了掩饰，我们将查询做轻微调整如下：
;WITH T1 AS ( SELECT TOP(10) ID , ROW_NUMBER() OVER ( ORDER BY ID ) AS RID FROM [dbo].[TB002])SELECT *FROM T1WHERE T1.RID > 10AND T1.RID < 30
我们会悲哀地发现，查询返回结果为空，这显然不是一个好兆头，为什么会返回空呢？
轻轻推敲一下，我们就会发现，CTE内部的执行结果总是“巧合”地返回RID为1到10的数据，而外部条件RID>10又将这10条数据过滤掉，SO返回为空。
PS: 查询优化器真的是“顺手”返回前10条数据，因为恰好这10条数据“在手边”，不能保证其他场景下也是返回RID为1到10的数据，当然也不是查询优化器故意“坑人”哈
--=========================华丽分割线=======================================--
至此，我总算明白为啥要将写SQL的那位兄弟要传入入2147483647 这么大一个页数量，估计是传小了查不出数据，所以一劳永逸传个最大值，想想也是醉了！
--=========================华丽分割线=======================================
总结：
编写SQL的目的在于实现业务需求，而不是显示个人SQL能力，也没有“一招鲜吃遍天”可以秒杀所有问题的写法，在尊重业务需求的前提下，依据业务场景，考虑数据分布和当前以及未来的数据量，用尽可能简单的SQL地实现业务需求才是王道。
其实写博客的目的是发图，你们懂的！
图片来源网络，勿求粽子！
- 4楼wy123
- http://www.cnblogs.com/shanksgao/p/4942928.html#3305575，，从高大神的文章中看到，row_number分页的一些潜在问题，，他文章中说row_number分页的时候是逐行加锁然后释放的，而我自己测试用offset方式分页就不是逐行锁定的，当时在想，为什么“row_number分页的时候是逐行加锁然后释放的”？为什么?是row_number的先天缺陷吗，微软升级到2012之后新增加的offset分页方式是改进row_number分页的吗？，，问题想不通也不碍事，主要是过不去自己心里这道坎，，后来总算是想明白了，row_number并不是为了专门分页而生的，为啥要逐行锁定，而不是一个更大粒度的锁定，最主要的是row_number是为了对同一组数据做开窗使用的，更多的时候是分组后给每个组内一个编号（over partition by col1 order by col2），再细想，此时分组的列col2并非一定顺序存储在一个page中，所以直接锁定page粒度就太大了。，，反观offset方式的分页，排序列上有索引的话，这个列一定是顺序存储的，顺序存储的话，连续的列是存储在一个page中的（当然一个page不够再接着下一个page，我也不是强调在page内物理有序存放），所以可以直接锁定一个page（或者更大的粒度），，我想说的是，row_number天生就不是为了分页而生的，更多的时候是为了实现那种（over partition by col1 order by col2）分组再给组内一个编号。，，再者可以参考Oracle，为啥Oracle中都存在row_number了，还有一个rownum？当然分页的时候两者都可以使用，但Oracle中并不推荐row_number，而是用rownum，如果说非要用row_number的话也没问题，用人家Oracle那边大神的话说就是，row_number根本就不是为了分页的。说明row_number的作用不在于分页，或者说row_number不是专门为了分页而生的，只不过人们用它来分页罢了，其实并不适合分页。，当然Oracle 12c中也引进了 offset方式的分页。，，对于SqlServer，2012之后还是选择offset 吧
- 3楼wy123
- 另外，您的这篇文章http://www.cnblogs.com/TeyGao/p/3851809.html，是不是也能说明，row_number在某些情况下，不适合分页
- Re: 桦仔
- @wy123，引用另外，您的这篇文章http://www.cnblogs.com/TeyGao/p/3851809.html，是不是也能说明，row_number在某些情况下，不适合分页，WITH T1 AS ，(，SELECT TOP ( 100 )，ID ,，ROW_NUMBER() OVER ( ORDER BY C1 ) AS RID，FROM [dbo].[TB002]，)，SELECT *，FROM T1，WHERE T1.RID gt; (1-1)*2147483647，AND T1.RID lt; 1*2147483647，，因为top(100)返回的rid是随机的，比如返回的rid范围是2005~2105，那么他引用cte的时候where条件不能写一个确定的范围，比如where T1.RID gt; 2005，AND T1.RID lt; 2105，，每次执行这个参数返回的rid范围不一样，所以程序员索性写21亿int最大值进去，，解决办法就是，;WITH T1 AS ，(，，SELECT ID ,，ROW_NUMBER() OVER ( ORDER BY ID ) AS RID，FROM [dbo].[TB002]，)，SELECT *，FROM T1，WHERE T1.RID gt; 10，AND T1.RID lt; 30
- Re: 笑东风
- @wy123，，其实很多问题归结到最后都是业务没有理解好，或者研发同事没有写对，条条道路通罗马，但不是每条道路都那么通畅而已！，，像那种需要对几十万上千万数据进行分页的业务，十有八九是可以修改其他方式实现的！
- 2楼wy123
- 我还是不明白，他这个查询到底是干啥用的，每次查出来的结果不一定相同啊，，就是为了随机取100条数据用的？
- Re: 笑东风
- @wy123，，至于为什么业务要TOP 100, 这个只有天知道啦，这个只能供其他人借鉴，避免踩入此类坑！
- 1楼wy123
- 以下是摘自于这篇文章，http://www.iflym.com/index.php/english-translate/201308060001.html，，如果row_number和rownum使用同样的执行计划，但为什么rownum明显更快呢。，这是因为：oracle的历史实在是太久了，而不同的时间导致相同的特性却有不同的效果。，，rownum在oracle6中被引进，发布时间为1988年，在当时什么资源和条件都不满足的情况下，作为一个简单的计数器，被认为是非常简单和高效的。，而随着时代的发展，更多的需求被提及出来，这时，一个相当于但功能比rownum更强大的函数被引入，[这就是row_number函数，它从oracle9i开始被引进。这时，效率已经不再是惟一的条件了，所以row_number的实现也不再以效率为惟一的指标了。，，当然，如果你有更多的要求，如分组排序等，则需要使用row_number函数，但如果你仅仅是简单的分页查询，建议使用rownum，这也是为什么在现在的时代rownum还是这么流行(据说在oracle12c中有offset分页操作符了，内部同样使用row_number函数,这样rownum可以退休了)，，，----------------------------------，以上是Oracle中row_number产生的背景，我想SqlServer中引起来row_number也不全是为了分页。，，好像扯远了，我就是想说row_number能用来分页，但是不是为了分页而设计的，尤其SqlServer2012出来之后，哈哈，，另外佩服楼主，真用心，很多时候被一些稀烂的sql搞烦了，高效要遵循一些高效写法的规则，低效sql有1000个低效的原因，尤其面对好几屏的存储过程，没的心情一一分析它为什么慢了。