Is there a value that I can use in a SELECT TOP that will return all rows?

Well, it looks like TOP is a BIGINT if you aren't using a PERCENT. That means you could pass in the max value of BIGINT,

SELECT TOP (9223372036854775807) * FROM table1

I seriously doubt you will ever see a table that large. I'm not sure what kind of effect that would have on the query plan though.


You could also consider

SET ROWCOUNT @x;

SELECT Foo
FROM Bar
ORDER BY Baz;

Instead of

SELECT TOP (@x) Foo
FROM Bar 
ORDER BY Baz;

The value you would need to set @x to is 0 to disable it.

This is deprecated for data modification statements but not deprecated for SELECT.

In 2012 a different plan is compiled for the case that ROWCOUNT is 0 vs some non zero value.

If the ORDER BY Baz is only there to give meaning to the TOP rather than to provide a presentation order for results and you don't have an index supporting this then splitting into two queries would avoid an unnecessary sort in the 0 case.


SELECT TOP 100 PERCENT can be used to bypass any errors with using "TOP" in a query.

Tags:

Sql Server