是否在分页中使用CTE插入的临时表来应付? [英] Is it bater using temp table insted of CTE in paging?

问题描述

当我们要在cte中使用行号功能时，分页效率较低，而不是可以使用临时表并在行号列上创建索引.

当使用cte进行分页时，我们需要使用rownumber函数.但是我们可以创建临时表并包含在行号列中，并在行号列上创建索引，而sql将在索引内执行索引查找以到达低边界行，直到到达高边界行.

通过使用临时表，仅在索引中扫描请求的结果页面的行.在Cte索引中时，将执行扫描，并从第一行开始扫描.

所以，我想知道是否正确?

Hi,


When we want to use row number function in cte is less efficient in paging instead of we can use temp table and create index on row number column.

When cte is use for paging than we need to use rownumber function. But we can create temp table and included with row number column and create index on row number column than sql will perform index seek within the index to reach low boundary row until it reaches the high boundary row.

By using temp table Only the rows of the requested page of results are scanned within the index. While in Cte Index scan is performed and it scans from first row.

So, i want to know that is it right or not?

推荐答案

检查一下

http://stackoverflow.com/questions/690465/which-are- more-performant-cte-or-temporary-tables [ ^ ]

http://stackoverflow.com/questions/1531835/sql-2005-cte-vs-temp-table-performance-when-used-in-joins-of-other-tables [

Check this out

http://stackoverflow.com/questions/690465/which-are-more-performant-cte-or-temporary-tables[^]

http://stackoverflow.com/questions/1531835/sql-2005-cte-vs-temp-table-performance-when-used-in-joins-of-other-tables[^]

这篇关于是否在分页中使用CTE插入的临时表来应付?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！