sql-servert-sqlsql-server-2019bold-bi

Is there a maximum number of rows you can specify for TOP on SQL Server?

When writing a query with the TOP clause in Microsoft SQL Server 2019, is there a limit to how high a number you can specify before the query fails? For example:

SELECT TOP 9999999999999999
FROM table
ORDER BY name

I ask because I am using Bold BI with my SQL Server, which restricts the use of ORDER BY unless I also specify TOP. I want to set the maximum possible number for TOP to work around this limitation.

Solution

  • According to: https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver15

    The syntax is:

    [
    TOP ( expression )
    [ WITH TIES ]
]

    And expression is:

    The numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if you specify PERCENT. Otherwise, expression is converted to bigint.

    So I'm assuming the max value would be max bigint (or 9,223,372,036,854,775,807)

    Another option would be to use a top percentage: TOP 100 PERCENT to specify getting all the rows if that can done with Bold BI.