选择TOP(全部) [英] select TOP (all)

问题描述

declare @t int
set @t = 10
if (o = 'mmm') set @t = -1
select top(@t) * from table

如果我想要的话，结果通常是 10 行，但很少是全部.

What if I want generally it resulted with 10 rows, but rarely all of them.

我知道我可以通过SET ROWCOUNT"来做到这一点.但是是否有一些变量数，如 -1，导致 TOP 产生所有元素.

I know I can do this through "SET ROWCOUNT". But is there some variable number, like -1, that causing TOP to result all elements.

推荐答案

可以传递给 TOP 的最大可能值是 9223372036854775807 所以你可以传递它.

The largest possible value that can be passed to TOP is 9223372036854775807 so you could just pass that.

下面我使用二进制形式表示 max signed bigint，因为只要您知道基本模式并且 bigint 是 8 个字节，就更容易记住.

Below I use the binary form for max signed bigint as it is easier to remember as long as you know the basic pattern and that bigint is 8 bytes.

declare @t bigint =  case when some_condition then 10 else  0x7fffffffffffffff end;

select top(@t) * 
From table

如果您没有 order by 子句，则前 10 个将是任意 10 个并且依赖于优化.

If you dont have an order by clause the top 10 will just be any 10 and optimisation dependant.

如果您确实有一个 order by 子句来定义前 10 名，并有一个索引来支持它，那么上述查询的计划应该适用于任一可能的值.

If you do have an order by clause to define the top 10 and an index to support it then the plan for the query above should be fine for either possible value.

如果您没有支持索引并且计划显示排序，您应该考虑拆分为两个查询.

If you don't have a supporting index and the plan shows a sort you should consider splitting into two queries.

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