I'm allowing the end user to define how many rows are returned by a query (SELECT TOP (@x)). Is there a value that can be entered where all rows are returned? Or do I have to dynamically create the query without the TOP (@x) if they want all rows returned?

I'm using SQL Server 2012.

  • Is it TOP ... ORDER BY something? Is the ORDER BY something still required in the case that you select all? Commented Aug 17, 2016 at 20:05
  • 1
    I guess uhhh... just omitting the TOP is out of the question? Like you're dealing with some predefined query and you have to pass it something?
    – corsiKa
    Commented Aug 18, 2016 at 19:29

4 Answers 4


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.

  • 7
    Assuming the variable @x is a BIGINT then SET @x = 0x7fffffffffffffff may be clearer to some. It's easier to remember anyway. Commented Aug 17, 2016 at 20:11
  • @MartinSmith I'll be honest I'm going to look it up either way :) I've never seen that done before though. Is that an implicit conversion? Commented Aug 17, 2016 at 20:13
  • 7
    Well it's easier to remember if you just remember to begin with 7 and then the rest of it is F and you need 16 characters in total for the 8 bytes. And yes it will be implicitly converted if assigning to a variable of that datatype. Commented Aug 17, 2016 at 20:16
  • 1
    @MartinSmith Is it a good practice to rely on binary representation of the value? The documentation states The binary representation of a value might change from version to version of SQL Server. Also, I don't know if I'm the only person, who was always confused by 0x7fffffffffffffff representation of the max value of bigint in SqlServer. The reason is that in SqlServer binary constant notation you have 7f in the lowest byte, where as in languages like c++ you have it in the highest byte to get max of signed integral type.
    – i-one
    Commented Aug 18, 2016 at 10:00
  • 3
    @i-one. The binary representation of integer types is extremely unlikely to change. There would be no point in SQL Server supplying bitwise operators that operate on the integer types if it was not expected we could rely on a specific format. Commented Aug 18, 2016 at 10:34

You could also consider



Instead of


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.

  • 1
    To me, that doesn't really explain the reason to use a feature that gets optimised out. Does Crystal Reports expect an ORDER BY to be there in the view definition?
    – Andriy M
    Commented Aug 18, 2016 at 7:23
  • No, Crystal Reports has issues with taking hold of queries and forcing its own execution plans on them, making them very inefficient and sometimes inaccurate. I prefer to do ALL of my logic and design in the SQL query, and simply make it "pretty" in Crystal. This is why I have, for the most part, migrated to SSIS/SSRS, but there are still legacy reports out there.
    – SQLDevDBA
    Commented Aug 18, 2016 at 13:23
select top(SELECT COUNT(*) FROM YourTable) * From YourTable
  • 1
    The question is about parametrising the TOP clause. The OP already has a parameter (@x) there, and they are now asking what value to pass to @x so that it would mean "all rows" regardless of how many rows the table actually has. Maybe you could figure out how to adapt your solution to match the OP's requirements.
    – Andriy M
    Commented Aug 18, 2016 at 12:27
  • 1
    I've made the adjustments to fit the question though as written this has a race condition if rows are inserted concurrently. This would require a table lock for the duration to resolve that blocks concurrent inserts. This and the extra expense of checking in the first place can be avoided by just using a large number. Commented Aug 18, 2016 at 13:20
  • (SELECT COUNT(*) FROM YourTable) is not a value. Commented Aug 31, 2016 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.