Published on December 5, 2022.

One of the questions that I frequently get asked about the NetSuite SuiteQL Query Tool involves the number of rows that the tool can return. There's a misconception that you can get a maximum of 5,000 rows in a single query response.

I'm happy to report that you can get more than 5,000 rows in a response. In fact, you can have the tool return all of a query response's rows (assuming that it doesn't timeout first). You can also specify a specific range of rows to be returned (for example, rows 25 through 50).

To use these options, click the tool's "Enable Pagination Options" checkbox. You can then indicate if you want a range of rows to be returned, or if you'd like all rows be returned.

Here's a screen shot showing the tool with the pagination options enabled.

Click the image to view a larger version.

If you specify a range of rows, then you can optionally have the tool return the total number of rows that would have been returned if you'd have chosen to return all of them. You'll see this displayed in the Results are. For example, "Retrieved 7500 of 29635 rows in 301ms." (The reason that this is an option, by the way, is that calculating the total number of rows can be resource intensive and slow the query down quite a bit.)

If you don't use the tool's pagination options, then by default, a maximum of 5,000 rows will be returned. That's because the tool uses the N/query module's query.runSuiteQL method, and it does have a 5,000 row limit.

Normally, an alternative to the runSuiteQL method (that let's you get beyond its 5,000 row limit) is query.runSuiteQLPaged. However, the issue with query.runSuiteQLPaged is that if you use it with an adhoc SuiteQL query (i.e. a string representation of a query), then you cannot access the column names in the query result. To get to the column names with runSuiteQLPaged, you have to build a query.Query object. And due to the nature of the SuiteQL Query Tool, where it is designed specifically for running adhoc queries, building an object isn't a good option.

One more thing... Earlier I mentioned that you can use the pagination options to return all possible results of a query. Technically, there is a limit. In the Suitelet, I've capped the maximum number of rows to be returned at 999,999. If you want to take a chance and return more than that, simply update the Suitelet. Search for "999999" and replace it with the value you prefer.