{"id":652,"date":"2012-04-04T23:18:29","date_gmt":"2012-04-04T23:18:29","guid":{"rendered":"http:\/\/madprogrammer76.wordpress.com\/?p=652"},"modified":"2013-10-10T20:56:39","modified_gmt":"2013-10-10T20:56:39","slug":"sql-pagination","status":"publish","type":"post","link":"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/","title":{"rendered":"SQL Pagination"},"content":{"rendered":"<p>I have often encountered the culture of query for thousands of results, estimate the paging and then only display a small segment of the results that were retrieved from the database.  This is particularly bad in a web environment.  This results in the following issues:<\/p>\n<ul>\n<li>A large amount of data to be transferred from the database to the website<\/li>\n<li>Longer than necessary database queries<\/li>\n<li>Script Errors and timeouts<\/li>\n<li>Frustrated users\/customers<\/li>\n<\/ul>\n<p><!--more-->The better approach is to allow the database to fetch only the results that are required for paging.  Here is an example of what a query may look like.<\/p>\n<pre style=\"color:blue;padding:10px;\">\r\nDeclare @PageNumber int\r\nDeclare @PageSize int\r\n\r\n--Assume we need page 6 i.e. records from 51-60\r\nSet @PageNumber = 6\r\nSet @PageSize = 10\r\n<\/pre>\n<p><\/p>\n<pre style=\"color:blue;padding:10px;\">\r\nSelect Top(@PageSize) * from\r\n(\r\nSelect\r\n       rowNumber=ROW_NUMBER() OVER (ORDER BY descriptionOfGoods),\r\n       productID,descriptionOfGoods,itemNumber,MSRPrice,listPrice,\r\n       totalRows=Count(*) OVER() \u2013-Count all records\r\n    from Products\r\n) A\r\nWhere A.rowNumber &gt; ((@PageNumber-1)*@PageSize)\r\n<\/pre>\n<p><strong>Note<\/strong>:  This works best when indexes are up-to-date and configured properly.  While this seems obvious, it is also a step that I often see missed.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I have often encountered the culture of query for thousands of results, estimate the paging and then only display a small segment of the results that were retrieved from the database. This is particularly bad in a web environment. This results in the following issues: A large amount of data to be transferred from the &hellip; <a href=\"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">SQL Pagination<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,78],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v23.9 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\r\n<title>SQL Pagination - LDNDeveloper<\/title>\r\n<meta name=\"description\" content=\"SQL Pagination by Andrew Pallant - a Software Developer in London Ontario\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"SQL Pagination - LDNDeveloper\" \/>\r\n<meta property=\"og:description\" content=\"SQL Pagination by Andrew Pallant - a Software Developer in London Ontario\" \/>\r\n<meta property=\"og:url\" content=\"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/\" \/>\r\n<meta property=\"og:site_name\" content=\"LDNDeveloper\" \/>\r\n<meta property=\"article:published_time\" content=\"2012-04-04T23:18:29+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2013-10-10T20:56:39+00:00\" \/>\r\n<meta name=\"author\" content=\"andrewpallant\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@ldnDeveloper\" \/>\r\n<meta name=\"twitter:site\" content=\"@LdnDeveloper\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"andrewpallant\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/#article\",\"isPartOf\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/\"},\"author\":{\"name\":\"andrewpallant\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/0e7b5e71751000e8f66b17b69ef4ab97\"},\"headline\":\"SQL Pagination\",\"datePublished\":\"2012-04-04T23:18:29+00:00\",\"dateModified\":\"2013-10-10T20:56:39+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/\"},\"wordCount\":127,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84\"},\"articleSection\":[\"Better Coding\",\"SQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/\",\"url\":\"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/\",\"name\":\"SQL Pagination - LDNDeveloper\",\"isPartOf\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#website\"},\"datePublished\":\"2012-04-04T23:18:29+00:00\",\"dateModified\":\"2013-10-10T20:56:39+00:00\",\"description\":\"SQL Pagination by Andrew Pallant - a Software Developer in London Ontario\",\"breadcrumb\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/andrewpallant.ca\/wordpress\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Pagination\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#website\",\"url\":\"http:\/\/andrewpallant.ca\/wordpress\/\",\"name\":\"LDNDeveloper\",\"description\":\"Learning, Growing and Sharing.\",\"publisher\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/andrewpallant.ca\/wordpress\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84\",\"name\":\"ldnDeveloper\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/andrewpallant.ca\/wordpress\/wp-content\/uploads\/2017\/05\/cropped-AAEAAQAAAAAAAAXQAAAAJDQxMGRlMzFjLWM4ODctNDk1NC05M2EyLWE1NDNhNTRiZjVlYw-2.jpg\",\"contentUrl\":\"https:\/\/andrewpallant.ca\/wordpress\/wp-content\/uploads\/2017\/05\/cropped-AAEAAQAAAAAAAAXQAAAAJDQxMGRlMzFjLWM4ODctNDk1NC05M2EyLWE1NDNhNTRiZjVlYw-2.jpg\",\"width\":512,\"height\":512,\"caption\":\"ldnDeveloper\"},\"logo\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/\"},\"description\":\"Andrew Pallant (@LdnDeveloper) has been a web, database and desktop developer for over 16 years. Andrew has worked on projects that ranged from factory automation to writing business applications. Most recently he has been heavily involved in various forms for ecommerce projects. Over the years Andrew has worn many hats: Project Manager, IT Manager, Lead Developer, Supervisor of Developers and many more - See more at: http:\/\/www.unlatched.com\/#sthash.8DiTkpKy.dpuf\",\"sameAs\":[\"http:\/\/www.andrewpallant.ca\",\"https:\/\/x.com\/LdnDeveloper\"]},{\"@type\":\"Person\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/0e7b5e71751000e8f66b17b69ef4ab97\",\"name\":\"andrewpallant\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/1.gravatar.com\/avatar\/?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/1.gravatar.com\/avatar\/?s=96&d=mm&r=g\",\"caption\":\"andrewpallant\"},\"url\":\"http:\/\/andrewpallant.ca\/wordpress\/author\/andrewpallant\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Pagination - LDNDeveloper","description":"SQL Pagination by Andrew Pallant - a Software Developer in London Ontario","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/","og_locale":"en_US","og_type":"article","og_title":"SQL Pagination - LDNDeveloper","og_description":"SQL Pagination by Andrew Pallant - a Software Developer in London Ontario","og_url":"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/","og_site_name":"LDNDeveloper","article_published_time":"2012-04-04T23:18:29+00:00","article_modified_time":"2013-10-10T20:56:39+00:00","author":"andrewpallant","twitter_card":"summary_large_image","twitter_creator":"@ldnDeveloper","twitter_site":"@LdnDeveloper","twitter_misc":{"Written by":"andrewpallant","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/#article","isPartOf":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/"},"author":{"name":"andrewpallant","@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/0e7b5e71751000e8f66b17b69ef4ab97"},"headline":"SQL Pagination","datePublished":"2012-04-04T23:18:29+00:00","dateModified":"2013-10-10T20:56:39+00:00","mainEntityOfPage":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/"},"wordCount":127,"commentCount":0,"publisher":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84"},"articleSection":["Better Coding","SQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/#respond"]}]},{"@type":"WebPage","@id":"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/","url":"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/","name":"SQL Pagination - LDNDeveloper","isPartOf":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/#website"},"datePublished":"2012-04-04T23:18:29+00:00","dateModified":"2013-10-10T20:56:39+00:00","description":"SQL Pagination by Andrew Pallant - a Software Developer in London Ontario","breadcrumb":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/andrewpallant.ca\/wordpress\/sql-pagination\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/andrewpallant.ca\/wordpress\/"},{"@type":"ListItem","position":2,"name":"SQL Pagination"}]},{"@type":"WebSite","@id":"http:\/\/andrewpallant.ca\/wordpress\/#website","url":"http:\/\/andrewpallant.ca\/wordpress\/","name":"LDNDeveloper","description":"Learning, Growing and Sharing.","publisher":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/andrewpallant.ca\/wordpress\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84","name":"ldnDeveloper","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/","url":"https:\/\/andrewpallant.ca\/wordpress\/wp-content\/uploads\/2017\/05\/cropped-AAEAAQAAAAAAAAXQAAAAJDQxMGRlMzFjLWM4ODctNDk1NC05M2EyLWE1NDNhNTRiZjVlYw-2.jpg","contentUrl":"https:\/\/andrewpallant.ca\/wordpress\/wp-content\/uploads\/2017\/05\/cropped-AAEAAQAAAAAAAAXQAAAAJDQxMGRlMzFjLWM4ODctNDk1NC05M2EyLWE1NDNhNTRiZjVlYw-2.jpg","width":512,"height":512,"caption":"ldnDeveloper"},"logo":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/"},"description":"Andrew Pallant (@LdnDeveloper) has been a web, database and desktop developer for over 16 years. Andrew has worked on projects that ranged from factory automation to writing business applications. Most recently he has been heavily involved in various forms for ecommerce projects. Over the years Andrew has worn many hats: Project Manager, IT Manager, Lead Developer, Supervisor of Developers and many more - See more at: http:\/\/www.unlatched.com\/#sthash.8DiTkpKy.dpuf","sameAs":["http:\/\/www.andrewpallant.ca","https:\/\/x.com\/LdnDeveloper"]},{"@type":"Person","@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/0e7b5e71751000e8f66b17b69ef4ab97","name":"andrewpallant","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/","url":"http:\/\/1.gravatar.com\/avatar\/?s=96&d=mm&r=g","contentUrl":"http:\/\/1.gravatar.com\/avatar\/?s=96&d=mm&r=g","caption":"andrewpallant"},"url":"http:\/\/andrewpallant.ca\/wordpress\/author\/andrewpallant\/"}]}},"_links":{"self":[{"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/posts\/652"}],"collection":[{"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/comments?post=652"}],"version-history":[{"count":1,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/posts\/652\/revisions"}],"predecessor-version":[{"id":1049,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/posts\/652\/revisions\/1049"}],"wp:attachment":[{"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/media?parent=652"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/categories?post=652"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/tags?post=652"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}