{"id":12993,"date":"2019-08-28T12:48:04","date_gmt":"2019-08-28T12:48:04","guid":{"rendered":"https:\/\/dbtut.com\/?p=12993"},"modified":"2020-11-13T12:46:11","modified_gmt":"2020-11-13T12:46:11","slug":"offset-and-fetch-in-sql-server","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/","title":{"rendered":"OFFSET and FETCH in SQL Server"},"content":{"rendered":"

If you do not want to send the entire query result to the client, you can use OFFSET and FETCH in SQL Server to specify how many rows returned to the client. This feature can be used since SQL Server 2012.<\/p>\n

I think it can be understood more easily by making an example. Let’s examine the code block below.<\/p>\n

OFFSET and FETCH Examples in SQL Server<\/h3>\n

Example1<\/h4>\n

In the below query, we select [AdventureWorks2014].[Person].[Address] table sorting by City column.<\/p>\n

SELECT * FROM [AdventureWorks2014].[Person].[Address] \r\nOrder By City ASC \r\nOPTION(MAXDOP 1)<\/pre>\n
Example2<\/h4>\n
In the second query, we bring all the records except the first 5 records of the same query.<\/p>\n
SELECT * FROM [AdventureWorks2014].[Person].[Address] \r\nOrder By City ASC \r\nOFFSET 5 ROWS \r\nOPTION(MAXDOP 1);<\/pre>\n
Example3<\/h3>\n
In the third query, we bring only 10 records starting from the first record. We put the OPTION (MAXDOP 1) query hint at the end of the query so that the query result is the same each time. If we don’t, and the query has parallelism, the sorting can be different each time.<\/p>\n
SELECT *  FROM [AdventureWorks2014].[Person].[Address]\r\nOrder By City ASC\r\nOFFSET 40 ROWS\r\nFETCH NEXT 10 ROWS ONLY\r\nOPTION(MAXDOP 1);\r\n<\/pre>\n
SQL Server Paging Using Offset and Fetch<\/h3>\n
This feature can be used for paging. For example, with the following code, we bring up the fifth page when the number of lines per page is 10.<\/p>\n
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 5\r\nSELECT *  FROM [AdventureWorks2014].[Person].[Address]\r\n  Order By City ASC  \r\n  OFFSET @RowsPerPage * (@PageNumber-1) ROWS\r\n  FETCH NEXT @RowsPerPage ROWS ONLY\r\n  OPTION(MAXDOP 1);<\/pre>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
If you do not want to send the entire query result to the client, you can use OFFSET and FETCH in SQL Server to specify how many rows returned to the client. This feature can be used since SQL Server 2012. I think it can be understood more easily by making an example. Let’s examine …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":12994,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[4763,4771,4767,4760,4761,4762,10241,4769,4770,4776,4772,4775,4768,4774,4765,4764,4766,4773],"class_list":["post-12993","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-mssql","tag-fetch-in-sql-server","tag-how-do-i-select-top-5-rows-in-sql","tag-how-do-you-use-offset-and-fetch-in-sql-server","tag-offset-and-fetch","tag-offset-and-fetch-in-sql-server","tag-offset-in-sql-server","tag-sql-server-paging-using-offset-and-fetch","tag-what-does-offset-mean-in-sql","tag-what-is-offset-and-fetch-in-sql-server","tag-what-is-offset-and-fetch-in-sql","tag-what-is-offset-and-limit","tag-what-is-paging-in-sql-server","tag-what-is-the-difference-between-limit-and-top-in-sql","tag-what-is-the-difference-between-offset-and-limit","tag-what-is-the-difference-between-top-and-offset","tag-what-is-the-difference-between-top-and-offset-fetch","tag-what-is-the-difference-between-top-and-offset-and-fetch","tag-what-will-be-the-syntax-of-the-offset-fetch-clause-in-sql"],"aioseo_notices":[],"yoast_head":"\nOFFSET and FETCH in SQL Server - Database Tutorials<\/title>\n<meta name=\"description\" content=\"This article contains information and examples about OFFSET and FETCH in SQL Server\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"OFFSET and FETCH in SQL Server - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"This article contains information and examples about OFFSET and FETCH in SQL Server\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-08-28T12:48:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-11-13T12:46:11+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/08\/Ads\u0131z-7.png\" \/>\n\t<meta property=\"og:image:width\" content=\"602\" \/>\n\t<meta property=\"og:image:height\" content=\"415\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"OFFSET and FETCH in SQL Server\",\"datePublished\":\"2019-08-28T12:48:04+00:00\",\"dateModified\":\"2020-11-13T12:46:11+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/\"},\"wordCount\":193,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/08\/Ads\u0131z-7.png\",\"keywords\":[\"FETCH in SQL Server\",\"How do I select top 5 rows in SQL?\",\"How do you use offset and fetch in SQL Server?\",\"OFFSET and FETCH\",\"OFFSET and FETCH in SQL Server\",\"OFFSET in SQL Server\",\"sql server paging using offset and fetch\",\"What does offset mean in SQL?\",\"What is offset and fetch in SQL Server?\",\"What is offset and fetch in SQL?\",\"What is offset and limit?\",\"What is paging in SQL Server?\",\"What is the difference between limit and top in SQL\",\"What is the difference between offset and limit?\",\"What is the Difference between TOP and OFFSET\",\"What is the Difference between TOP and OFFSET & Fetch?\",\"What is the Difference between TOP and OFFSET and Fetch\",\"What will be the syntax of the offset fetch clause in SQL?\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/\",\"name\":\"OFFSET and FETCH in SQL Server - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/08\/Ads\u0131z-7.png\",\"datePublished\":\"2019-08-28T12:48:04+00:00\",\"dateModified\":\"2020-11-13T12:46:11+00:00\",\"description\":\"This article contains information and examples about OFFSET and FETCH in SQL Server\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/08\/Ads\u0131z-7.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/08\/Ads\u0131z-7.png\",\"width\":602,\"height\":415},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"OFFSET and FETCH in SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"OFFSET and FETCH in SQL Server - Database Tutorials","description":"This article contains information and examples about OFFSET and FETCH in SQL Server","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":"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"OFFSET and FETCH in SQL Server - Database Tutorials","og_description":"This article contains information and examples about OFFSET and FETCH in SQL Server","og_url":"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/","og_site_name":"Database Tutorials","article_published_time":"2019-08-28T12:48:04+00:00","article_modified_time":"2020-11-13T12:46:11+00:00","og_image":[{"width":602,"height":415,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/08\/Ads\u0131z-7.png","type":"image\/png"}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"OFFSET and FETCH in SQL Server","datePublished":"2019-08-28T12:48:04+00:00","dateModified":"2020-11-13T12:46:11+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/"},"wordCount":193,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/08\/Ads\u0131z-7.png","keywords":["FETCH in SQL Server","How do I select top 5 rows in SQL?","How do you use offset and fetch in SQL Server?","OFFSET and FETCH","OFFSET and FETCH in SQL Server","OFFSET in SQL Server","sql server paging using offset and fetch","What does offset mean in SQL?","What is offset and fetch in SQL Server?","What is offset and fetch in SQL?","What is offset and limit?","What is paging in SQL Server?","What is the difference between limit and top in SQL","What is the difference between offset and limit?","What is the Difference between TOP and OFFSET","What is the Difference between TOP and OFFSET & Fetch?","What is the Difference between TOP and OFFSET and Fetch","What will be the syntax of the offset fetch clause in SQL?"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/","url":"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/","name":"OFFSET and FETCH in SQL Server - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/08\/Ads\u0131z-7.png","datePublished":"2019-08-28T12:48:04+00:00","dateModified":"2020-11-13T12:46:11+00:00","description":"This article contains information and examples about OFFSET and FETCH in SQL Server","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/08\/Ads\u0131z-7.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/08\/Ads\u0131z-7.png","width":602,"height":415},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/08\/28\/offset-and-fetch-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"OFFSET and FETCH in SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/12993","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=12993"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/12993\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/12994"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=12993"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=12993"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=12993"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}