Month: February 2016

Membuat query lebih simple dengan Common Table Expression

setelah melihat bagaimana menggunakan paging di sqlserver, sekarang saya akan mencoba mengulas sedikit mengenai kemampuan sqlserver yang lain yaitu Common Table Expression (CTE) [1]. Dengan CTE ini kita dapat membuat query kita lebih simple.

Contoh Sederhana CTE

mari ambil contoh salah satu query dari postingan saya sebelumnya, yaitu query

pada query itu kita akan menambahkan kolom nomor baris, dan selanjutnya akan dilanjutkan dengan query

yang akan melakukan seleksi baris yang akan kita ambil dan tampilkan.

nah sekarang kita coba dengan menggunakan cara yang sedikit berebeda , yaitu menggunakan CTE. Untuk melakukan itu, kita membutuhkan keyword with.

berikut langkah – langkah sederahananya :

Buat dahulu CTE tersebut yang akan menggunakan nama alias tertentu
Buat query yang memanggil alias tersebut sebagai sebuah table.

tahap 1 dimulai dari kita membuat CTE nya, seperti pada query :

setelah itu kita dapat menambahkan query dibawahnya dengan memanggil CTE tersebut, seperti pada query penuh seperti :

maka kita dapat menggunakan CTE sebagai alternatif dalam menuliskan paging tanpa nested query.

Menggunakan CTE untuk mengaggregasi

contoh pada web microsoft [1] memberikan contoh yang baik untuk hal ini , misalnya saja kita mempunyai query untuk menghasilkan jumlah penjualan (transaksi) per Sales, maka dapat kita selesaikan dengan query

dan untuk menghitung rata – rata penjualan per sales maka akan kita dapatkan dengan query

yang mana sama dengan query

jika kita tidak menggunakan CTE.

Multiple CTE

misalkan saja berdasarkan contoh di atas, kita ingin melihat siapa saja yang sales yang diatas rata – rata maka dengan mudah kita mendefinisikan query sebagai berikut :

jauh lebih simple daripada kita menggunakan nested query berikut :

Hirarki dengan CTE

masalah ini sangat terkait dengan masalah yang terkenal di dunia design yaitu masalah rekursif, contohnya adalah design pada masalah employee-manager [2]. Dalam gambar kira2 bisa digambarkan seperti :

recursive-uml

untuk menggenerate masalah ini sebelumnya kita perlu membuat tabel untuk itu, karena di adventure works 2012 tidak ada permasalahan rekursif ini secara mudah untuk dilihat. Untuk itu kembali kita lihat contoh dari website microsoft [1]. Yaitu menggunakan query :

dengan ini kita akan membuat table myEmployees yang berisikan beberapa data dummy.

maka untuk menampilkan list employee beserta managernya kita bisa memanfaatkan query

di dalam query itu kita menggunakan (memanggil ) CTE di dalam deklarasi CTE itu sendiri

so asik bukan.. silakan untuk melihat – lihat contoh lain pada [1].

Referensi

[1]. https://msdn.microsoft.com/en-us/library/ms175972.aspx

[2] http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php

Paging di SQLSERVER

seandainya kita akan membuat halaman web untuk menampilkan data penjualan dari database adventurework 2012 [1] seperti pada query :

maka kit memerlukan solusi paging untuk itu. Karena jika query itu dijalankan maka akan menampilkan data sebanyak 121.317 baris.. tentu hal yang mustahil menampilkan data sebanyak itu dalam 1 halaman web saja.

pada sistem database yang lain misalnya mysql , postgresql atau sqlite maka akan dikenal keyword limit dan offset untuk melakukan paging [2], namun hal itu tidak berlaku di sqlserver.

untuk melakukan paging di sqlserver kita memerlukan beberapa langkah secara umum :

Jadikan query menjadi nested query
Berikan nomor pada masing – masing baris hasil nested query pada query baru kita
filter hasil query berdasarkan nomor tersebut

untuk proses nomor 2 , kita dapat memanfaatkan function dari sqlserver yaitu row_number() [3], seperti pada query :

setelah melakukan number maka kita dapat jadikan hal tersebut sebagai nested query dan memfilter datanya berdasarkan kolom number seperti pada query :

Paging pada SQLSERVER 2012

khusus pada sqlserver 2012 ke atas, paging dapat dilakukan dengan lebih simple dengan memanfaatkan keyword offset fetch [4], dengan langkah – langkah secara umum sebagai berikut :