Skip to main content

Membuat Query Paging Pada SQL Server


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

Popular posts from this blog

Tutorial SQL Injection Menggunakan SQLMap

Kali ini kita akan membahas tutorial hacing dengan teknik SQL Injection menggunakan tool SQLMap untuk melakukan hacking atau serangan pada website berbasis data dengan tujuan untuk menemukan kerentanan keamanan pada website tersebut. SQL Injection adalah teknik injeksi code yang digunakan untuk melakukan serangan pada aplikasi web berbasis data di mana pernyataan malicious SQL dimasukkan ke dalam entri untuk di eksekusi (misalnya untuk menampilkan konten database ke penyerang). SQL injection dapat mengeksploitasi kerentanan keamanan pada perangkat lunak aplikasi, misalnya saat user salah melakukan filter inputan untuk pengiriman karakter yang disematkan dalam pernyataan SQL atau inputan user tidak diketik dengan benar dan tanpa diduga dieksekusi. SQL Injection lebih dikenal sebagai vektor serangan untuk sebuah situs web namun juga dapat digunakan untuk menyerang semua jenis database SQL. Apa itu SQLMap SQLMap adalah salah satu tool otomatis untuk melakukan SQL Injection yang

Penjumlahan dan pengurangan tanggal di PHP

Penjumlahan (tambah) dan pengurangan (kurang) tanggal di PHP sering  kita jumpai di beberapa kasus pembuatan sistem seperti pembuatan fitur peminjaman, perizinan, pemesanan dan lain-lain. Kali ini kita akan membahas bagaimana  suatu tanggal dapat di tambah atau di kurangi dengan hari, bulan dan tahun. Ok langsung saja kita bahas materinya!

Cara membuat Hak Akses (Privileges) pada user SQL Server (Grant dan Revoke)

Pada tutorial kali ini kita akan belajar bagaimana cara membuat atau memberi grant atau revoke hak akses (Privileges) pada user di SQL Server menggunakan syntax Transact SQL (T-SQL). Anda dapat memberi GRANT dan REVOKE hak akses pada berbagai objek database di SQL Server. Kali ini kita akan melihat bagaimana memberi dan menghapus hak akses pada tabel di SQL Server. Memberikan permissions dan menghapus permissions tidak terlalu sulit dilakukan di dalam SQL Server, hanya butuh beberapa waktu untuk menentukan permissions apa yang harus diterapkan. Sebelumnya anda harus membuat user dahulu pada SQL Server, setelah itu baru kita bisa memberi Hak Akses. Berikutnya pastikan anda sudah membuat atau menjalankan script dibawah ini. [USE [nama_database] GO CREATE USER [nama_user] FOR LOGIN [nama_user]] Grant Hak Akses (Privileges) pada  tabel Anda dapat memberi (Grant) berbagai hak akses ke tabel pada user. Hak akses ini bisa berupa kombinasi SELECT, INSERT, UPDATE, DELETE, REFERENCES