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.