# Limit and offset not working in remote sql server service **URL:** \ **Category:** Connecting to SQL\ **Created:** [July 12, 2014, 3:01pm UTC](https://community.dreamfactory.com/t/limit-and-offset-not-working-in-remote-sql-server-service/160 "2014-07-12T15:01:59Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![isidro](https://avatars.discourse-cdn.com/v4/letter/i/858c86/32.png) [@isidro](https://community.dreamfactory.com/u/isidro)\ **Post date:** [July 12, 2014, 3:02pm UTC](https://community.dreamfactory.com/t/limit-and-offset-not-working-in-remote-sql-server-service/160/1 "2014-07-12T15:02:00Z")
When using a sql local database (the db api) I can use limit and offset with success, but when using my own service to a remote SQL Server database offset property doesn’t work. Limit works, but whatever offset I use I always get the first records . ---
**Author:** ![benbusse](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.dreamfactory.com/benbusse/32/4_2.png) [@benbusse](https://community.dreamfactory.com/u/benbusse)\ **Post date:** [July 13, 2014, 4:05pm UTC](https://community.dreamfactory.com/t/limit-and-offset-not-working-in-remote-sql-server-service/160/2 "2014-07-13T16:05:47Z")
Not sure why that’s happening. I’ve logged an issue on GitHub [https://github.com/dreamfactorysoftware/dsp-core/issues/29](https://github.com/dreamfactorysoftware/dsp-core/issues/29). The engineering team will look into it and I’ll post an answer once I hear back. ---
**Author:** ![leehicks](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.dreamfactory.com/leehicks/32/55_2.png) [@leehicks](https://community.dreamfactory.com/u/leehicks)\ **Post date:** [July 21, 2014, 5:11pm UTC](https://community.dreamfactory.com/t/limit-and-offset-not-working-in-remote-sql-server-service/160/3 "2014-07-21T17:11:29Z")
Our service for SQL Server uses the older method for paging where SQL Server requires the ORDER BY clause, (not the OFFSET and FETCH method used in the 2012/2014 versions as documented [here] ([http://msdn.microsoft.com/en-us/library/ms188385.aspx](http://msdn.microsoft.com/en-us/library/ms188385.aspx)). So, if you wanted to get the second set/page of records, limiting 5 at a time, your request would look like this… (Note, I chose the “id” field to order by but any sortable field would do.) /rest/\/\?limit=5&offset=2&order=id%20ASC Or if you are more familiar with the Microsoft aliases like top and skip… /rest/\/\?top=5&skip=2&order=id%20ASC We look to support the OFFSET/FETCH method in the future, but for now, most of our clients were running 2008R2 or earlier. ---
**Author:** ![glowingembersons](https://avatars.discourse-cdn.com/v4/letter/g/0ea827/32.png) [@glowingembersons](https://community.dreamfactory.com/u/glowingembersons)\ **Post date:** [December 1, 2015, 9:16pm UTC](https://community.dreamfactory.com/t/limit-and-offset-not-working-in-remote-sql-server-service/160/4 "2015-12-01T21:16:31Z")
Anyone with an idea on how to bring down all the records? when I use a limit that is more then 1000, it still limits it to 1000. ---
**Author:** ![juniorconte](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.dreamfactory.com/juniorconte/32/1007_2.png) [@juniorconte](https://community.dreamfactory.com/u/juniorconte)\ **Post date:** [December 2, 2015, 12:58am UTC](https://community.dreamfactory.com/t/limit-and-offset-not-working-in-remote-sql-server-service/160/5 "2015-12-02T00:58:04Z")
Hello @glowingembersons , I’m not sure if there’s another way to change that, but you can change the default value for the maximum number of records per request. In version 1.9.x look for the following line in the file: [config/common.config.php](https://github.com/dreamfactorysoftware/dsp-core/blob/master/config/common.config.php#L239) / \*\* The default number of records to return at once for database queries \* / ‘dsp.db\_max\_records returned’ =\> 1000, In version 2.0.x procute the line below in the file: [config/df.php](https://github.com/dreamfactorysoftware/dreamfactory/blob/master/config/df.php#L23) // The default number of records to return at once for database queries ’records returned max’ =\> env (‘DF\_DB\_MAX\_RECORDS\_RETURNED’, 1000) Another alternative would be to create a stored procedure and there treat the limit of records, as well as other applicable filters. The downside here is that you can not count on them so comforting parameters and resources of the database API. On the other hand, precisely because there is a minimal treatment by the JSON generated in the execution, the performance is quite high. I’ve used procedures to return JSON’s with 5000+ records, and in my tests, it began to complicate when exceeded the 12000+ records, but does not recommend doing so great requests. Best regards.