14

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.

Improve this question
2
  • Is it TOP ... ORDER BY something? Is the ORDER BY something still required in the case that you select all?
    – Martin Smith
    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
    Aug 18, 2016 at 19:29

4 Answers 4

Reset to default
19

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.

Improve this answer
6
  • 7
    Assuming the variable @x is a BIGINT then SET @x = 0x7fffffffffffffff may be clearer to some. It's easier to remember anyway.
    – Martin Smith
    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?
    – Kenneth Fisher
    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.
    – Martin Smith
    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
    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.
    – Martin Smith
    Aug 18, 2016 at 10:34
12

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.

Improve this answer
0
8

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

Improve this answer
2
  • 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
    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
    Aug 18, 2016 at 13:23
-2 
select top(SELECT COUNT(*) FROM YourTable) * From YourTable
Improve this answer
3
  • 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
    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.
    – Martin Smith
    Aug 18, 2016 at 13:20
  • (SELECT COUNT(*) FROM YourTable) is not a value.
    – ypercubeᵀᴹ
    Aug 31, 2016 at 8:53

Your Answer

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

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