sql server - select TOP (all)

Question

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

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

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

Answer 1

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

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

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

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.