TOP 与 SET ROWCOUNT [英] TOP versus SET ROWCOUNT

问题描述

TOP 和 SET ROWCOUNT 之间的性能是否存在差异，或者它们只是以相同的方式执行?

Is there a difference in performance between TOP and SET ROWCOUNT or do they just get executed in the same manner?

推荐答案

是的，在功能上它们是一样的.据我所知，两者之间没有显着的性能差异.

Yes, functionally they are the same thing. As far as I know there are no significant performance differences between the two.

需要注意的一件事是，一旦您设置了 set rowcount，这将在连接的生命周期内持续存在，因此请确保在完成后将其重置为 0与它.

Just one thing to note is that once you have set rowcount this will persist for the life of the connection so make sure you reset it to 0 once you are done with it.

---

SET ROWCOUNT 的范围仅适用于当前过程.这包括当前过程调用的过程.它还包括通过 EXEC 或 SP_EXECUTESQL 执行的动态 SQL，因为它们被视为子"范围.

The scope of SET ROWCOUNT is for the current procedure only. This includes procedures called by the current procedure. It also includes dynamic SQL executed via EXEC or SP_EXECUTESQL since they are considered "child" scopes.

请注意，SET ROWCOUNT 位于 BEGIN/END 范围内，但它超出了范围.

Notice that SET ROWCOUNT is in a BEGIN/END scope, but it extends beyond that.

create proc test1
as
begin
    begin
    set rowcount 100
    end
    exec ('select top 101 * from master..spt_values')
end
GO

exec test1
select top 102 * from master..spt_values

结果 = 100 行，然后是 102 行

Result = 100 rows, then 102 rows

这篇关于TOP 与 SET ROWCOUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！