SQL 性能:WHERE 与 WHERE(ROW_NUMBER) [英] SQL performance: WHERE vs WHERE(ROW_NUMBER)

问题描述

我想在表中获取第 n 到 m 条记录，以下两种解决方案的最佳选择是什么:

I want get n-th to m-th records in a table, what's best choice in 2 below solutions:

解决方案 1:

    SELECT * FROM Table WHERE ID >= n AND ID <= m

解决方案 2:

    SELECT * FROM 
                (SELECT *, 
                        ROW_NUMBER() OVER (ORDER BY ID) AS row 
                 FROM Table 
                )a 
    WHERE row >= n AND row <= m

推荐答案

第二个答案是您的最佳选择.它考虑到您的 ID 列中可能有孔的事实.我会将它重写为 CTE 而不是子查询...

The 2nd answer is your best choice. It takes into account the fact that you could have holes in your ID column. I'd rewrite it as a CTE though instead of a subquery...

;WITH MyCTE AS
(SELECT  *,  
         ROW_NUMBER() OVER (ORDER BY ID) AS row  
FROM     Table)
SELECT   *
FROM     MyCTE
WHERE    row >= @start 
         AND row <= @end

这篇关于SQL 性能:WHERE 与 WHERE(ROW_NUMBER)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！