16 April 2009

Menggunakan Common Table Expression (CTE)

Common Table Expression (CTE) merupakan fitur baru SQL Server yang dimulai dari SQL Server 2005. Bagi yang pernah menggunakan variabel bertipe TABLE, mungkin ada kemiripan diantara keduanya. Menurut dokumentasi, CTE dapat digambarkan sebagai hasil query sementara yang ada pada lingkup eksekusi operasi SELECT, INSERT, UPDATE, DELETE, atau CREATE VIEW. CTE mirip dengan tabel turunan yang tidak disimpan sebagai obyek. Tapi tidak seperti tabel turunan, CTE dapat di referensikan ke diri sendiri dan dapat di referensikan beberapa kali pada query yang sama.
Menggunakan CTE memiliki beberapa keuntungan yaitu lebih mudah dibaca dan memudahkan pengaturan pada query yang kompleks. Query dapat dipecah menjadi beberapa query yang lebih sederhana. CTE dapat digunakan pada procedure, fungsi, trigger atau view. Cara penggunaanya:
WITH <expression_name> [<column_name [,...n]>]
AS
(<CTE_query_definition>)
Daftar nama kolom bisa tidak diberikan hanya jika pada definisi query terdapat nama kolom yang masing-masing berbeda. Untuk menjalankan CTE sama seperti SELECT dari sebuah tabel.
SELECT <column_list> FROM expression_name
Dibawah ini adalah contoh penggunaan CTE. Terdapat sebuah tabel yang berisi daftar departemen dan pekerjaan. Sebuah departemen/pekerjaan bisa memiliki sub departemen/pekerjaan. Ini hanya sebagai contoh cara penggunaan saja. Pada penggunaan query yang kompleks akan terlihat keuntungan dari penggunaan CTE.
DECLARE @Job TABLE (
  JobID int PRIMARY KEY,
  JobName varchar(20) NOT NULL,
  SubJobID int NULL);
INSERT INTO @Job VALUES
  (1, 'Keuangan', NULL),
  (2, 'HRD', NULL),
  (3, 'Administrasi', NULL),
  (4, 'Accounting', 1),
  (5, 'IT', 1),
  (6, 'Humas', 2),
  (7, 'EDP', 5),
  (8, 'Programmer', 5);
WITH JobCTE
AS
(
  SELECT * FROM @Job
)
SELECT mst.JobID, mst.JobName, sub.JobName AS SubJobName
FROM JobCTE AS mst
LEFT JOIN JobCTE AS sub ON sub.JobID = mst.SubJobID

Tidak ada komentar:

Posting Komentar