{"id":79,"date":"2014-01-02T23:02:49","date_gmt":"2014-01-02T15:02:49","guid":{"rendered":"http:\/\/suherman.asia\/w2\/?p=79"},"modified":"2014-10-19T23:39:34","modified_gmt":"2014-10-19T15:39:34","slug":"paging-windows-in-t-sql","status":"publish","type":"post","link":"http:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html","title":{"rendered":"PAGING windows in T-SQL"},"content":{"rendered":"

di SQL Server 2012 ada metode baru untuk mendapatkan beberapa baris data melalui klausa Select sehingga didapatkan jumlah baris data sesuai yang diinginkan sesuai dengan nilai row offset dan row value yang sudah ditentukan sehingga jumlah record yang di presentasikan di client dapat diatur dengan mekanisme paging.<\/p>\n

Sebenarnya dalam hal ini kita memanfaatkan kombinasi OFFSET\/FETCH NEXT<\/strong> dengan klausa ORDER BY<\/strong><\/p>\n

untuk lebih jelasnya kita langsung praktek aja. Disini saya menggunakan SQL Server 2012<\/strong> Developer dan database Adventureworks2012<\/strong>.<\/p>\n

Didalam database AdventureWorks2012 ada table yang bernama Production.Product. untuk melihat seluruh baris data yang ada di table tersebut kita gunakan klausa SELECT<\/strong> dengan mengurutkannya berdasarkan field PRODUCTID<\/strong>.<\/p>\n

\n
SELECT ProductID,Name,SafetyStockLevel,ReorderPoint\r\n  FROM [Production].[Product] order by ProductID<\/pre>\n
T-SQL diatas akan mengembalikan 504 baris data yang berurut berdasarkan field PRODUCTID.<\/p>\n<\/div>\n
jika kita ingin mendapatkan baris-baris data tersebut berdasarkan urutan data yang sudah ditentukan atau misal kita ingin mendapatkan baris data dimana di setiap PAGE akan dimunculkan 10 baris data dapat dibuat dengan T-SQL berikut :<\/p>\n
misalkan N adalah angka PAGE yang diinginkan maka :<\/p>\n
\n
declare @N int;\r\n  set @N = 1 ;\r\n  declare @offset_increment int = 10;\r\n  declare @current_offset int = (0 + ((@N-1) * @offset_increment));\r\n\r\nSELECT ProductID,Name,SafetyStockLevel,ReorderPoint\r\n  FROM [Production].[Product] order by ProductID\r\noffset\r\n@current_offset rows\r\nfetch next\r\n@offset_increment rows only\r\ngo\r\n<\/pre>\n
Contoh T-SQL diatas adalah bagaimana jika kita ingin menampilkan data pada PAGE 1 dengan banyak baris data sebanyak 10 record.<\/p>\n<\/div>\n
<\/div>\n
Lalu bagaimana jika ingin memanggil 10 record yang berada pada PAGE 10 ?<\/div>\n
yaitu dengan menggunakan T-SQL berikut :<\/div>\n
<\/div>\n
\n
declare @N int;\r\n  set @N = 10 ;\r\n  declare @offset_increment int = 10;\r\n  declare @current_offset int = (0 + ((@N-1) * @offset_increment));\r\n\r\nSELECT ProductID,Name,SafetyStockLevel,ReorderPoint\r\n  FROM [Production].[Product] order by ProductID\r\noffset\r\n@current_offset rows\r\nfetch next\r\n@offset_increment rows only\r\ngo<\/pre>\n
data yang ditampilkan akan seperti ini<\/p>\n<\/div>\n
\"image\"<\/a><\/div>\n
<\/div>\n
demikian semoga bermanfaat.<\/div>\n","protected":false},"excerpt":{"rendered":"
di SQL Server 2012 ada metode baru untuk mendapatkan beberapa baris data melalui klausa Select sehingga didapatkan jumlah baris data sesuai yang diinginkan sesuai dengan nilai row offset dan row value yang sudah ditentukan sehingga jumlah record yang di presentasikan di client dapat diatur dengan mekanisme paging.<\/p>\n
 Continue reading<\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":106,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[27],"class_list":["post-79","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-paging-windows"],"yoast_head":"\nPAGING windows in T-SQL - Suherman Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PAGING windows in T-SQL - Suherman Blog\" \/>\n<meta property=\"og:description\" content=\"di SQL Server 2012 ada metode baru untuk mendapatkan beberapa baris data melalui klausa Select sehingga didapatkan jumlah baris data sesuai yang diinginkan sesuai dengan nilai row offset dan row value yang sudah ditentukan sehingga jumlah record yang di presentasikan di client dapat diatur dengan mekanisme paging. Continue reading\" \/>\n<meta property=\"og:url\" content=\"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html\" \/>\n<meta property=\"og:site_name\" content=\"Suherman Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-02T15:02:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-10-19T15:39:34+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/paging.png\" \/>\n\t<meta property=\"og:image:width\" content=\"433\" \/>\n\t<meta property=\"og:image:height\" content=\"246\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\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:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#article\",\"isPartOf\":{\"@id\":\"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html\"},\"author\":{\"name\":\"admin\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"headline\":\"PAGING windows in T-SQL\",\"datePublished\":\"2014-01-02T15:02:49+00:00\",\"dateModified\":\"2014-10-19T15:39:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html\"},\"wordCount\":202,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"image\":{\"@id\":\"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/paging.png\",\"keywords\":[\"Paging Windows\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html\",\"url\":\"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html\",\"name\":\"PAGING windows in T-SQL - Suherman Blog\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#primaryimage\"},\"image\":{\"@id\":\"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/paging.png\",\"datePublished\":\"2014-01-02T15:02:49+00:00\",\"dateModified\":\"2014-10-19T15:39:34+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#primaryimage\",\"url\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/paging.png\",\"contentUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/paging.png\",\"width\":433,\"height\":246},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/suherman.asia\/w2\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PAGING windows in T-SQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/suherman.asia\/w2\/#website\",\"url\":\"http:\/\/suherman.asia\/w2\/\",\"name\":\"Suherman Blog\",\"description\":\"Just Another Geek Site\",\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/suherman.asia\/w2\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"caption\":\"admin\"},\"logo\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\"},\"sameAs\":[\"http:\/\/suherman.asia\",\"https:\/\/www.facebook.com\/emantin34\",\"https:\/\/x.com\/emantin34\"],\"url\":\"http:\/\/suherman.asia\/w2\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PAGING windows in T-SQL - Suherman Blog","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:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html","og_locale":"en_US","og_type":"article","og_title":"PAGING windows in T-SQL - Suherman Blog","og_description":"di SQL Server 2012 ada metode baru untuk mendapatkan beberapa baris data melalui klausa Select sehingga didapatkan jumlah baris data sesuai yang diinginkan sesuai dengan nilai row offset dan row value yang sudah ditentukan sehingga jumlah record yang di presentasikan di client dapat diatur dengan mekanisme paging. Continue reading","og_url":"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html","og_site_name":"Suherman Blog","article_publisher":"https:\/\/www.facebook.com\/emantin34","article_author":"https:\/\/www.facebook.com\/emantin34","article_published_time":"2014-01-02T15:02:49+00:00","article_modified_time":"2014-10-19T15:39:34+00:00","og_image":[{"width":433,"height":246,"url":"https:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/paging.png","type":"image\/png"}],"author":"admin","twitter_misc":{"Written by":"admin","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#article","isPartOf":{"@id":"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html"},"author":{"name":"admin","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"headline":"PAGING windows in T-SQL","datePublished":"2014-01-02T15:02:49+00:00","dateModified":"2014-10-19T15:39:34+00:00","mainEntityOfPage":{"@id":"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html"},"wordCount":202,"commentCount":0,"publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"image":{"@id":"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/paging.png","keywords":["Paging Windows"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#respond"]}]},{"@type":"WebPage","@id":"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html","url":"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html","name":"PAGING windows in T-SQL - Suherman Blog","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/#website"},"primaryImageOfPage":{"@id":"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#primaryimage"},"image":{"@id":"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/paging.png","datePublished":"2014-01-02T15:02:49+00:00","dateModified":"2014-10-19T15:39:34+00:00","breadcrumb":{"@id":"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#primaryimage","url":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/paging.png","contentUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/paging.png","width":433,"height":246},{"@type":"BreadcrumbList","@id":"https:\/\/suherman.asia\/w2\/paging-windows-in-t-sql.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/suherman.asia\/w2"},{"@type":"ListItem","position":2,"name":"PAGING windows in T-SQL"}]},{"@type":"WebSite","@id":"http:\/\/suherman.asia\/w2\/#website","url":"http:\/\/suherman.asia\/w2\/","name":"Suherman Blog","description":"Just Another Geek Site","publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/suherman.asia\/w2\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","caption":"admin"},"logo":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/suherman.asia","https:\/\/www.facebook.com\/emantin34","https:\/\/x.com\/emantin34"],"url":"http:\/\/suherman.asia\/w2\/author\/admin"}]}},"_links":{"self":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/79","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/comments?post=79"}],"version-history":[{"count":4,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/79\/revisions"}],"predecessor-version":[{"id":460,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/79\/revisions\/460"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media\/106"}],"wp:attachment":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media?parent=79"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/categories?post=79"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/tags?post=79"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}