Membuat Query Paging Pada SQL Server

Pembahasan bagaimana cara Membuat Query untuk Paging Pada SQL Server menggunakan T-SQL dengan fungsi ROW_NUMBER dan "OFFSET dan FETCH"


Paging adalah hal yang umum dalam pembuatan suatu aplikasi, yang mana digunakan untuk memisah data antar halaman sehingga  data yang ditampilkan akan lebih optimal dan terlihat lebih rapi. Untuk membuat paging diperlukan query khusus untuk memanggil data dari database seperti SQL Server.

Hal ini sangat penting dalam kondisi bagi perusahaan kecil, dengan perangkat keras atau perangkat lunak terbatas. Tempat-tempat di mana akses internet terbatas dan lambat  sehingga kita juga memiliki kebutuhan untuk mengurangi lalu lintas data jaringan.

Tujuan utama artikel ini adalah untuk menunjukkan bagaimana skrip T-SQL sederhana bisa bermanfaat, bila diimplementasikan dengan baik. Query paging yang akan dibuat akan menggunakan dua cara. Query Paging pada SQL Server berbeda dengan MySQL, jika pada MySQL cukup menggunakan fungsi limit maka pada SQL Server caranya sedikit lebih sulit dibanding dengan MySQL

Membuat tabel untuk pengujian

Kita akan membuat tabel untuk demonstrasi dengan satu juta baris. Sehingga kita bisa menilai kinerjanya dengan cara yang berbeda untuk setiap query.

Pada akhir artikel ini, kita akan mengevaluasi kinerja query dengan ketentuan sebagai berikut:
  1. Query Paging, menggunakan ROW_NUMBER (model tradisional pagination data di SQL Server)
  2. Query Paging, menggunakan klausa OFFSET dan FETCH (model baru, yang digunakan sejak SQL Server 2012+)

[--Membuat sebuah tabel untuk pengujian CREATE TABLE dbo.TB_EXAMPLE( ID_EXAMPLE int NOT NULL IDENTITY(1,1), NM_EXAMPLE varchar(25) NOT NULL, DT_CREATE datetime NULL DEFAULT(GETDATE()) ); GO -- Mengisi 1,000,000 data yang berbeda pada tabel INSERT INTO TB_EXAMPLE (NM_EXAMPLE) VALUES ('Item de paging ' + CONVERT(VARCHAR,ISNULL(@@IDENTITY, 0))) GO 1000000 --Menampilkan 1,000,000 data SELECT * FROM TB_EXAMPLE GO ]

Query Paging menggunakan ROW_NUMBER

Untuk memahami konsep pagination di T-SQL, dengan ROW_NUMBER dan dengan OFFSET / FETCH, mari kita definisikan pola hasil dalam skrip T-SQL, untuk evaluasi kueri di atas.

Kita akan membuat dua variabel untuk memudahkan manipulasi data:
  • @PageNumber - Mengatur jumlah halaman yang akan ditampilkan
  • @RowspPage - Menetapkan nomor baris setiap halaman
Setelah mengatur variabel-variabel ini, kita akan mulai menggunakan fungsi ROW_NUMBER, dan menyortir data dengan field ID_EXAMPLE.
Lihat script di bawah ini, di mana kita mendefinisikan untuk menampilkan "halaman 2" dengan "5 baris per halaman":

[--Menampilkan halaman "2" dengan 5 baris data DECLARE @PageNumber AS INT, @RowspPage AS INT SET @PageNumber = 2 SET @RowspPage = 5 SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ID_EXAMPLE) AS NUMBER, ID_EXAMPLE, NM_EXAMPLE, DT_CREATE FROM TB_EXAMPLE ) AS TBL WHERE NUMBER BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage) ORDER BY ID_EXAMPLE]

Dalam hal ini kita mendefinisikan variabel @PageNumber dengan nilai sama dengan 2 dan variabel @RowspPage dengan nilai 5. Sehingga kita berada di "halaman 2":
  • Baris pertama dengan alias "NUMBER" sama dengan 6
  • Baris terakhir dengan alias "NUMBER" sama dengan 10

Query Paging menggunakan OFFSET dan FETCH

Sejak SQL Server 2012, terdapat fitur baru yang ditambahkan dalam klausa ORDER BY untuk mempermudah pekerjaan dengan paging data bagi siapa saja yang menulis di T-SQL.
Di bawah skrip T-SQL dengan logika yang sama digunakan pada contoh sebelumnya.

[--Membuat paging dengan OFFSET dan FETCH di "SQL SERVER 2012" DECLARE @PageNumber AS INT, @RowspPage AS INT SET @PageNumber = 2 SET @RowspPage = 10 SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE FROM TB_EXAMPLE ORDER BY ID_EXAMPLE OFFSET ((@PageNumber - 1) * @RowspPage) ROWS FETCH NEXT @RowspPage ROWS ONLY;]

Contoh ini menggunakan fungsi OFFSET, maka penting untuk mengetahui bahwa record pertama dimulai pada posisi "nol".

Dalam hal ini kita mendefinisikan variabel @PageNumber dengan nilai 2 dan variabel @RowspPage dengan nilai 10. Sehingga pada halaman ini:

  • Baris pertama di posisi 11
  • Baris terakhir di posisi 20

Dalam pernyataan SELECT diatas, query dalam tabel ini menggunakan fungsi OFFSET membuat pagination lebih sederhana. Query baru ini lebih efisien daripada query pertama yang menggunakan ROW_NUMBER.

COMMENTS

Nama

Apache,4,Bisnis,1,Bisnis Online,1,Blog,10,Blogger,6,Cpanel,1,CSS,1,Database,1,Framework,1,Hacking,3,HTML,1,HTML and CSS,2,Info,2,Javascript,2,Javascript and AJAX,5,Keamanan Web,1,linux,2,MySQL,3,Networking,1,Open GL,1,Optimasi Website,7,Pemrograman java,1,Pemrograman web,5,PHP,7,phpMyAdmin,1,SEO,4,SQL Injection,3,SQL Server,13,SQLMap,3,UMKM,2,Website,1,Wordpress,4,Xampp,1,
ltr
item
Nanang Gunawan Blog: Membuat Query Paging Pada SQL Server
Membuat Query Paging Pada SQL Server
Pembahasan bagaimana cara Membuat Query untuk Paging Pada SQL Server menggunakan T-SQL dengan fungsi ROW_NUMBER dan "OFFSET dan FETCH"
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZT1xp8VavcL6sLO8qhoNKT-kWUb-F7CL_G4oR_pFF6ghRIS0j_s8DEOA-90KBJiV1BuCCXM2p7UHuMnuNjpIboq67Q3wILXr129vU6t935Gq6bxhgUDGRrP15lW_FNZzKGmRFdVYsvyo/s1600/Query+Paging+di+SQL+Server.jpg
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZT1xp8VavcL6sLO8qhoNKT-kWUb-F7CL_G4oR_pFF6ghRIS0j_s8DEOA-90KBJiV1BuCCXM2p7UHuMnuNjpIboq67Q3wILXr129vU6t935Gq6bxhgUDGRrP15lW_FNZzKGmRFdVYsvyo/s72-c/Query+Paging+di+SQL+Server.jpg
Nanang Gunawan Blog
https://www.nananggunawan.com/2017/07/membuat-query-paging-pada-sql-server.html
https://www.nananggunawan.com/
https://www.nananggunawan.com/
https://www.nananggunawan.com/2017/07/membuat-query-paging-pada-sql-server.html
true
4195327445586723627
UTF-8
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content