{"id":112,"date":"2009-10-07T23:24:37","date_gmt":"2009-10-07T22:24:37","guid":{"rendered":"http:\/\/muratyaman.co.uk\/wp\/?p=112"},"modified":"2020-04-01T13:06:24","modified_gmt":"2020-04-01T12:06:24","slug":"paging-records-in-ms-sql-server-2005","status":"publish","type":"post","link":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/2009\/10\/paging-records-in-ms-sql-server-2005\/","title":{"rendered":"Paging records in MS SQL Server 2005"},"content":{"rendered":"<p>After years of torturing their SQL Server users, Microsoft provided a &#8220;better&#8221; way of paging records in version 2005, and it is a weird one! The following example is from their website, an article about <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms186734%28SQL.90%29.aspx\">ROW_NUMBER (Transact-SQL)<\/a>.<\/p>\n<pre lang=\"sql\">\r\nUSE AdventureWorks;\r\nGO\r\nWITH OrderedOrders AS\r\n(\r\n    SELECT SalesOrderID, OrderDate,\r\n    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'\r\n    FROM Sales.SalesOrderHeader \r\n) \r\nSELECT * \r\nFROM OrderedOrders \r\nWHERE RowNumber BETWEEN 50 AND 60;\r\n<\/pre>\n<p>It is simply unacceptable, non-standard, impractical! By the way, you can not use column numbers (e.g. ORDER BY 1, 2 DESC); you must specify column names.<\/p>\n<p>As usual, Microsoft programmers do not &#8220;think&#8221; and do not expect to work on millions of records, they simply say: &#8220;Buy more RAM, faster CPU, a better machine!&#8221; etc. Upgrade, upgrade, upgrade! They still do not understand that they will not be able to replace <strong>international standards organisation<\/strong> or something like that.<\/p>\n<p>Unfortunately, we can not force our users not to ask for <strong>all<\/strong> the records; if they want to see them, we need to provide the facility, and show the records page by page.<\/p>\n<p>Possibly, <strong>MySQL <\/strong>developers thought about the speed of the database engine while they were developing it, and <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/4.1\/en\/select.html\">LIMIT<\/a> clause was a part of it since middle ages!<\/p>\n<pre lang=\"sql\">\r\nSELECT SalesOrderID, OrderDate\r\nFROM SalesOrderHeader\r\nORDER BY OrderDate\r\nLIMIT 10 OFFSET 49\r\n<\/pre>\n<p>Similarly, Firebird SQL has a very simple clause to get a page of records like this, using <a href=\"http:\/\/www.firebirdsql.org\/refdocs\/langrefupd15-select.html#langrefupd15-first-skip\">FIRST m SKIP n<\/a> syntax:<\/p>\n<pre lang=\"sql\">\r\nSELECT\r\nFIRST 10 SKIP 49\r\nSalesOrderID, OrderDate\r\nFROM SalesOrderHeader\r\nORDER BY OrderDate\r\n<\/pre>\n<p><a href=\"http:\/\/www.postgresql.org\/docs\/7.4\/static\/sql-select.html\">PostgreSQL<\/a> works same as MySQL:<\/p>\n<pre lang=\"sql\">\r\nSELECT SalesOrderID, OrderDate\r\nFROM SalesOrderHeader\r\nORDER BY OrderDate\r\nLIMIT 10 OFFSET 49\r\n<\/pre>\n<p>Although Oracle SQL has a function (similar to MS SQL Server 2005) called <a href=\"http:\/\/download.oracle.com\/docs\/cd\/B28359_01\/server.111\/b28286\/functions144.htm#i86310\">ROW_NUMBER()<\/a>, they also offer a pseudocolumn called <a href=\"http:\/\/download.oracle.com\/docs\/cd\/B28359_01\/server.111\/b28286\/pseudocolumns009.htm#i1006297\">ROWNUM<\/a> to achieve the same:<\/p>\n<pre lang=\"sql\">\r\nSELECT SalesOrderID, OrderDate\r\nFROM SalesOrderHeader\r\nWHERE ROWNUM BETWEEN 50 AND 60\r\nORDER BY OrderDate\r\n<\/pre>\n<p>Note that this method requires modification of the criteria (WHERE clause) of the original query. I do not want to comment on who is mimicing who, but it is known that Microsoft do it more often, or simply buy the rival company owning a popular software application.<\/p>\n<p>You can easily get <strong>lost<\/strong> inside the terrible documentation of IBM for <strong>DB2<\/strong> database: it supports the approach using ROW_NUMBER() function.<\/p>\n<p>My favourite database management systems are: Firebird, MySQL and Oracle. Sometimes you are not given any options and have to use what is available on the application server, like MS SQL Server 2000\/2005 on a Windows server, MySQL 5 usually on a Linux machine.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>After years of torturing their SQL Server users, Microsoft provided a &#8220;better&#8221; way of paging records in version 2005, and it is a weird one! The following example is from their website, an article about ROW_NUMBER (Transact-SQL).<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[55,56,57,38,58,33],"class_list":["post-112","post","type-post","status-publish","format-standard","hentry","category-technology","tag-firebird","tag-ms-sql-server","tag-mysql","tag-oracle","tag-postresql","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/112","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=112"}],"version-history":[{"count":8,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/112\/revisions"}],"predecessor-version":[{"id":1004,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/112\/revisions\/1004"}],"wp:attachment":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=112"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=112"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=112"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}