13

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? – Martin Smith Aug 17 '16 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 '16 at 19:29
17

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. – Martin Smith Aug 17 '16 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 '16 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 '16 at 20:16
  • @MartinSmith Cool! Learned a couple of new things from this question. – Kenneth Fisher Aug 17 '16 at 20:20
  • 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 '16 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.

9

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 Aug 18 '16 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. – MguerraTorres Aug 18 '16 at 13:23
-2
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 Aug 18 '16 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 '16 at 13:20
  • (SELECT COUNT(*) FROM YourTable) is not a value. – ypercubeᵀᴹ Aug 31 '16 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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