{"id":2072,"date":"2009-04-02T15:06:18","date_gmt":"2009-04-02T22:06:18","guid":{"rendered":"http:\/\/www.sheer.us\/wordpress\/?p=2072"},"modified":"2009-04-02T15:08:34","modified_gmt":"2009-04-02T22:08:34","slug":"t-sql-equivilant-to-mysql-limit-clause","status":"publish","type":"post","link":"http:\/\/www.sheer.us\/weblogs\/uncategorized\/t-sql-equivilant-to-mysql-limit-clause","title":{"rendered":"(geekstuff) T-SQL equivilant to MySql LIMIT clause"},"content":{"rendered":"

As various of you know, one of the things I occasionally do is port code back and forth between using Oracle, MS SQL, and Mysql as database engines. One of the problems that has perpetually been a thorn in my side is that MS SQL didn’t have a equivilant to the handy MySQL LIMIT clause. Oh, you could achive the same effect with sub-selects and use of the TOP keyword, but it was ‘expensive’ – whereas LIMIT querys always execute very quickly. This matters very little when you have 10,000 or 100,000 rows, but get up into the tens of millions and it’s a problem.<\/p>\n

Well, as of SQL Server 2005, there is a solution that executes, to all appearances, as quickly as the MySQL LIMIT clause. It’s detailed in this blog<\/a>, but I’ll give the overview.<\/p>\n

There’s now a function called ROW_NUMBER() that returns the current row number of the query. You can’t *directly* use this to define a range (as in SELECT ROW_NUMBER() OVER (ORDER BY column) AS RowNum WHERE RowNum > 1000 AND RowNum < 500) but you can create a temporary object, and then select from the output of it, like this:<\/p>\n

WITH testRN AS (SELECT ROW_NUMBER() OVER (ORDER BY field1) AS RowNum, * FROM test) SELECT * FROM testRN WHERE RowNum BETWEEN 500 AND 1000;<\/p>\n

I’m currently using this function to dump a 219 million row table, a million rows at a time, and I don’t see any performance degredataion as I move deeper into the table, so I’d say it’s a pretty good solution, unlike the many LIMIT clause suggestions out there in network land that use sub-selects.<\/p>\n","protected":false},"excerpt":{"rendered":"

As various of you know, one of the things I occasionally do is port code back and forth between using Oracle, MS SQL, and Mysql as database engines. One of the problems that has perpetually been a thorn in my side is that MS SQL didn’t have a equivilant to the handy MySQL LIMIT clause. […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/www.sheer.us\/weblogs\/wp-json\/wp\/v2\/posts\/2072"}],"collection":[{"href":"http:\/\/www.sheer.us\/weblogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.sheer.us\/weblogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.sheer.us\/weblogs\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.sheer.us\/weblogs\/wp-json\/wp\/v2\/comments?post=2072"}],"version-history":[{"count":0,"href":"http:\/\/www.sheer.us\/weblogs\/wp-json\/wp\/v2\/posts\/2072\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.sheer.us\/weblogs\/wp-json\/wp\/v2\/media?parent=2072"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.sheer.us\/weblogs\/wp-json\/wp\/v2\/categories?post=2072"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.sheer.us\/weblogs\/wp-json\/wp\/v2\/tags?post=2072"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}