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:

Query Paging, menggunakan ROW_NUMBER (model tradisional pagination data di SQL Server)
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.