Aggregate Function MS SQL Server

Fungsi aggregate merupakan fungsi yang mengembalikan nilai rangkuman atau dengan kata lain melakukan perhitungan pada seperangkat nilai dan mengembalikannya dalam single value (nilai tunggal).

Misalkan kita mempunyai data seperti table dibawah ini :

COUNT()
Untuk menghitung jumlah baris yang dihasilkan  berdasarkan kondisi tertentu.
misalkan :

SELECT COUNT(*) FROM pegawai WHERE gaji_pokok>='200000'

AVG()
Menghasilkan nilai rata-rata, tidak seperti fungsi min dan max hanya dapat menerima data numerik sebagai parameter.

SELECT AVG(gaji_pokok) from pegawai

MIN()
Mengembalikan nilai terkecil dalam satu set.

SELECT MIN(gaji_pokok) from pegawai

MAX()
Mengembalikan nilai terbesar dalam satu set

SELECT MAX(gaji_pokok) from pegawai

SUM()
Berfungsi untuk mengembalikan jumlah dari sebuah parameter nilai, tidak seperti max dan min, sum hanya dapat menerima ekspresi numerik.

SELECT SUM(gaji_pokok) from pegawai 

VAR()
Fungsi ini dipergunakan untuk menghitung varian statistik dari semua nilai, menggunakan bertipe numeric dan menghasilkan nilai float.

SELECT VAR(gaji_pokok) FROM pegawai

STDEV()
Dipergunakan untuk menghitung standar deviasi dan hanya dapat digunakan pada column yang tipe datanya numerik, fungsi stdev selalu mengembalikan nilai float dari hasil perhitungan fungsi.

SELECT STDEV(gaji_pokok) FROM pegawai 

Using View Ms. SQL Server

Mungkin beberapa teman yang belajar SQL Server sudah tidak asing lagi dengan yang namanya View, memang view sangat berguna untuk menyelesaikan beberapa case terutama pada pemprosesan table. View adalah table yang dihasilkan dari hasil query di MS. SQL Server, dibentuk dari satu atau beberapa table sekaligus.

Create View
Untuk membuat sebuah view sintaks penulisannya adalah

CREATE VIEW view_name [nama_kol1, nama_kol2, ...] as  Select_Statement

Alter View
Untuk mengubah atau memodifikasi sebuah view yang sudah dibuat sintaks penulisannya :

ALTER VIEW view_name [nama_kol1, nama_kol2, ...] as  Select_Statement

Drop View
Jika ingin mengapus / delete view gunakan sintaks :

DROP VIEW view_name

Untuk lebih memahami mengenai view, sekarang buat table pegawai dengan nama database dbpemerintahan seperti gambar dibawah ini

 

Misalkan ingin membuat view  manager logistik berisi informasi mengenai pegawai yang ada di divisi logistik.

CREATE VIEW manager_logistik AS

               SELECT * FROM pegawai WHERE divisi='logistik'

 

Jika menginginkan tidak semua column ditampilkan di view dan menentukan nama column yang ada di view bisa dengan menggunakan contoh perintah ini

CREATE VIEW manager_umum (umum_nip,umum_nama_depan, umum_nama_belakang, umum_gaji_pokok) AS select nip, nama_depan, nama_belakang, gaji_pokok from pegawai where divisi='umum' 

 

Semisalkan ditambahkan data baru ke dalam table pegawai maka prosesnya akan berpengaruh kepada view yang telah dibuat, dari hasil yang ada coba ketikan :

SELECT * FROM manager_umum

data akan tampil seperti gambar diatas,  yaitu berisi data pegawai yang berada pada divisi umum.

Sekarang buat view lagi dengan mengetikan :

CREATE VIEW hitung_umum (jlh_peg) AS SELECT count(*) FROM PEGAWAI WHERE divisi='umum' 

Perintah ini dipergunakan untuk menghitung jumlah data yang ditemukan dengan kondisi divisinya sama dengan umum.

SELECT * FROM hitung_umum

hasilnya berjumlah 2,

Coba tambahkan data ke dalam table pegawai dengan mengetikan :

INSERT INTO pegawai values ('1012','roger','smith','80000','umum')

Check kembali view yang telah dibuat, data view otomatis terupdate sesuai dengan kondisi terakhir  dari dua orang bertambah satu menjadi 3 pegawai, begitu juga dengan jumlah pegawai yang ada di view hitung_total_umum.

 

Index Table Clustered and Nonclustered SQL Server

Penggunaan index di table microsoft sql server pada dasarnya berfungsi untuk mengurutkan data sehingga proses pencarian set data bisa lebih cepat karena data sudah tersusun dengan kondisi tertentu.

Command index yang digunakan adalah :

CREATE INDEX [Nama_Index] ON [Nama_Table] ( [Nama_Kolom] [ASC/DESC] )

kebiasaan saya dalam penamaan sebuah index table dengan menyebutkan Idx_ di depan nama table yang saya index itu sendiri.

Contohnya :

CREATE INDEX idx_nip_pegawai ON pegawai (nip ASC )

Untuk melihat index yang telah dibuat melalui SQL Server Management Studio pada pada :

Database -> Tables -> Nama Tabel -> Indexes

Untuk menghapus index yang sudah dibuat bisa menggunakan perintah :

DROP [Nama_Table.Nama_Index]

Dengan melakukan index pada table akan mempercepat proses pencarian atau pemrosesan data akan tetapi penentuan column index juga harus hati-hati, karena tidak semua column perlu dilakukan pengindexan, jika jumlah data record yang ditampung masih sedikit tidak terlihat pengaruhnya akan tetapi jika isi data telah menjadi besar maka akan berdampak pada performance database.

Jika data yang ditampung nantinya besar, lebih elegan jika menggunakan pengindexan clustered maupun nonclustered, tergantung kondisi yang memungkinkan dan dianggap paling cocok.


Ada beberapa perbedaan clustered index dan nonclustered index pada sql server yaitu dalam clustered index hanya terdiri satu dalam satu table atau hanya dapat diterapkan satu kali pada satu table, sedangkan nonclustred boleh lebih dari satu (banyak). Selain itu clustered index lebih cepat karena menyatu dengan fisik table dan tidak memerlukan memory tambahan sedangkan nonclustered index memerlukan memory tambahan.

Clustered Index
Saat create table dan belum ada clusterd index, maka SQL Server secara otomatis menjadikan primary key sebagai clusterd index, di clustred index boleh memiliki 2 kolom yang dikenal dengan composite clustered index.  Jika clustered index yang terbentuk otomatis oleh primary key constraint terhapus maka primary key juga akan ikut terhapus pada table tersebut. Yang harus diperhatikan yaitu clusterd index yang berasal dari primary key tidak bisa dihapus menggunakan query penghapusan. Penggunaan clustered index sangat baik ketika memperbaharui data atau menghasilkan data.

Kalau menurut pemahaman saya pribadi clustered index ini ibarat sebuah buku kamus, yang susunan kata demi kata sudah tersusun sesuai susunan abjad,  sehingga memudahkan pencarian kata ( jadi ingat waktu sma dulu kalau pelajaran bahasa inggris bawa kamus besar)

Command Clustered Index :

CREATE CLUSTERED INDEX [Nama_Index] ON [Nama_Table] ( [Nama_Kolom1] [ASC/DESC], [Nama_Kolom2] [ASC/DESC])


Nonclustered Index
setiap kali membuat nonclustered index maka akan membentuk table tambahan yang berisi data index dalam artian data hasil index terpisah dari table sumber. Jadi jika diasumsikan non clustered index ini seperti bagian index dari sebuah buku atau skripsi, ...

Command Non-clustered Index :

CREATE NONCLUSTERED INDEX [Nama_Index] ON [Nama_Table] ( [Nama_Kolom1] [ASC/DESC], [Nama_Kolom2] [ASC/DESC])

Sebagai contoh :

Buat table dengan nama pegawai pada database dbpemerintah dengan isian data seperti tampilan berikut :

Sebagai contoh misalkan kita memiliki database dengan nama dbpemerintah dan table pegawai, dengan isi data seperti gambar dibawah ini :

Terlihat isi column nip, nama depan nama belakang dan gaji pokok belum tersusun rapi. sekarang coba gunakan perintah clustered index

CREATE CLUSTERED INDEX idx_nm_depan on pegawai(nama_depan)

lihat hasilnya,

ketika melakukan query select table pegawai, sudah tersusun berdasarkan  nama depan secara ASC default jika tidak menyebukan saat melakukan create clustered index. Jika melakukan insert data maka data akan langsung di urutkan ASC berdasarkan kolom nama_depan.

Bagaimana jika ingin mengurutkan nama depan ASC dan nama belakang ASC berdasarkan nama depan. apa kah harus create clustered index lagi ?, jawabannya tidak, karena clustered index hanya bisa dibuat satu kali. Jika ingin melakukan hal tersebut delete dulu index yang pernah dibuat setelah itu baru buat kembali clustered index index multiple column dengan perintah

CREATE CLUSTERED INDEX idx_nm_depan_belakang ON pegawai (nama_depan ASC, nama_belakang ASC)

Setelah ini mari dicoba menggunakan non clustered index, sebenarnya sama saja, hanya saja jika menggunakan non clustered index tidak merubah data misalkan jika saat awal masih terlihat data yang tidak berurutan, setelah menggunakan non clustered index maka data yang ditampilkan masih tetap tidak berurutan (sorting).

CREATE NONCLUSTERED INDEX idx_nm_dpn_nm_blk ON pegawai(nama_depan ASC, nama_belakang ASC)

untuk mengetahui informasi mengenai index dari sebuah table bisa mengetikan perintah

sp_helpindex pegawai

Sekarang coba melakukan query pencarian dengan mengetikan

SELECT * FROM pegawai WHERE nama_depan LIKE '%a'


Keuntungan dan kerugian menggunakan index 
keuntungannya yaitu ketika melakukan query akan terasa lebih cepat daripada tidak melakukan pengindexan, namun kerugiannya jika index yang dibuat banyak jika terjadi insert edit dan delete maka prosesnya akan berulang kali sesuai jumlah index yang dibuat berimbas pada performance,  jika data index  tidak ditemukan maka proses akan dimulai dari awal lagi.

Configuration Function MS SQL Server

Installasi SQL Server 2016 Cepat dan Mudah

Beberapa hari yang lalu versi Microsoft SQL Server 2016 sudah tersedia di website Microsoft, dan seperti yang terdahulu tersedia versi Express Edition. Penasaran dengan apa saja yang baru dari SQL  Server 2016 ini saya coba download.

Link Download tersedia pada https://www.microsoft.com/en-us/download/details.aspx?id=52679

Setelah mendowload beberapa lama, ternyata proses installasi sangat mudah, walau mudah tetap harus memiliki akses internet yang lumayan tinggi agar bisa cepat selesai proses installasinya.

Kebetulan disini menggunakan koneksi internet Indihome dari Telkom 10 Mbps, perlu waktu kurang lebih sekitar 25 menit untuk installasinya.
Perlu diperhatikan pada saat installasi, untuk Microsoft SQL Server Management Studio tidak include secara default, akan tetapi dimudahkan dengan adanya pilihan untuk melakukan installasi tersendiri.

Untuk pilihan saat ini ada dua yang versi 16.5 untuk penggunaan di production, satunya lagi versi 17.0 RC1 , tapi tidak direkomendasikan. Jadi pilih saja yang bersi 16.5 nanti akan ada proses download dan proses installasi awal segera berlanjut.

Union Except Intersect Table MS SQL Server

Pada MS SQL Server tersedia fasilitas Union, Excepet dan Intersect yang digunakan untuk mempermudah query dalam sebuah table, ada beberapa perbedaan penggunaan pada ketiga fasilitas tersebut.

• Union

Dipergunakan untuk menggabungkan dua atau lebih hasil query select kedalam satu buah output.

untuk memahami pernggunaan union sql server perhatikan contoh dibawah ini, buat table pelanggan dan table perusahaan yang nantinya dari kedua table tersebut akan digabungkan

CREATE TABLE pelanggan
(
   id_pelanggan int,
   nama_depan varchar(64),
   nama_belakang varchar(64),
   nomor_telpon varchar(30),
   alamat varchar(100)
)

CREATE TABLE perusahaan
(
  id_perusahaan int,
  nama_pemilik varchar(64),
  nama_perusahaan varchar(64),
  nomor_telpon varchar(30),
  alamat varchar(100)
)

setelah membuat table di MS SQL Server setelah itu isikan, dengan dengan data yang ada dibawah ini pada table pelanggan :

INSERT INTO  pelanggan (id_pelanggan,nama_depan,nama_belakang,nomor_telpon,alamat) VALUES ('1','maman','suparman','(+62)8111','jawa barat')

INSERT INTO pelanggan (id_pelanggan,nama_depan,nama_belakang,nomor_telpon,alamat) VALUES ('2','anang','gulali','(+62)8213','kalimantan selatan')

INSERT INTO pelanggan (id_pelanggan,nama_depan,nama_belakang,nomor_telpon,alamat) VALUES ('3','donnie','subara','(+62)8111','jakarta')

INSERT INTO pelanggan (id_pelanggan,nama_depan,nama_belakang,nomor_telpon,alamat) VALUES ('4','imade','nyoman','(+62)8111','denpasar')

dan ini pada table perusahaan

INSERT INTO  perusahaan (id_perusahaan,nama_pemilik,nama_perusahaan,nomor_telpon,alamat) VALUES  ('100', 'jaja', 'bandung enterprise', '(+62)9992','indonesia')

INSERT INTO perusahaan (id_perusahaan,nama_pemilik,nama_perusahaan,nomor_telpon,alamat) VALUES ('200', 'joe', 'banjarlab tbk', '(+62)9995','indonesia')

sehingga terlihat 2 buah table dengan data seperti ini

table pelanggan

table perusahaan

setelah itu baru coba perintah union di ms sql server dengan perintah

SELECT 'pelanggan' as jenis,
                     nomor_telpon,
     alamat
FROM pelanggan
UNION
SELECT 'perusahaan', 
                     nomor_telpon,
                     alamat
FROM perusahaan

hasil yang tampil adalah :

mungkin ada yang bertanya, bagaimana menyatukan kedua table jika ada field yang berbeda, caranya cukup dengan menjadikan alias dari field yang ingin dibuat begitu juga jika field yang berbeda lebih dari satu dapat digabungkan. perhatikan contoh dibawah ini, field nama depan dan nama belakaang dijadikan field kontak yang ada di tabel pelanggan dan field nama_pemilik dan nama_perusahaan digabung juga menjadi field kontak


SELECT 'pelanggan' as jenis,
                     nama_depan+'  '+nama_belakang as kontak,
     nomor_telpon, 
                     alamat
FROM pelanggan
UNION
SELECT 'perusahaan', 
                     nama_pemilik+'  '+nama_perusahaan as kontak,
                     nomor_telpon, 
                     alamat
FROM perusahaan

hasil query penggunaan union akan tampil seperti ini

• Except

Membandingkan hasil dari dua statemen select dan mengembalikan baris hasil dari statemen select pertama yang tidak ada dalam hasil statemen select yang kedua

untuk memahami maksud dari except buat table dengan struktur dibawah ini

table gudang

create table gudang
(
  kode_brg varchar(5),
  nama_brg varchar(24),
  
)

dan juga buat table penjualan


create table penjualan
(
   id_trans int,
   kode_brg varchar(5),
   jlh_jual int,
  
)

masukan data datanya dengan mengetika query insert ms sql server dibawah ini :

INSERT INTO gudang (kode_brg,nama_brg) VALUES ('A1', 'buku')
INSERT INTO gudang (kode_brg,nama_brg) VALUES ('A2', 'pencil')
INSERT INTO gudang (kode_brg,nama_brg) VALUES ('A3', 'spidol')
INSERT INTO gudang (kode_brg,nama_brg) VALUES ('A4', 'penggaris')
INSERT INTO gudang (kode_brg,nama_brg) VALUES ('A5', 'penghapus')

INSERT INTO penjualan (id_trans,kode_brg,jlh_jual) VALUES ('1','A1','2')
INSERT INTO penjualan (id_trans,kode_brg,jlh_jual) VALUES ('2','A1','1')
INSERT INTO penjualan (id_trans,kode_brg,jlh_jual) VALUES ('3','A2','1')
INSERT INTO penjualan (id_trans,kode_brg,jlh_jual) VALUES ('4','A1','1')
INSERT INTO penjualan (id_trans,kode_brg,jlh_jual) VALUES ('5','A4','2')

menghasilkan hasil data sepeperti ini :

sekarang ketik perintah dibawah ini untuk mengetahui salah satu penggunaan query select except

SELECT 
                 kode_brg   
FROM gudang
EXCEPT
SELECT 
                 kode_brg

FROM penjualan

akan terlihat hasil dari perintah select except dari dua table

• Intersect

Membandingkan hasil dari dua statemen select dan mengembalikan hasil statemen yang ditemukan pada statemen select pertama dan statemen select yang kedua.

perintah sql intersect yang digunakan seperti ini :

SELECT 
                 kode_brg   
FROM gudang
INTERSECT
SELECT 
                 kode_brg

FROM penjualan

pada percobaan intersect cukup menganti perintah except menjadi intersect seperti perintah dibawah ini maka hasilnya akan terlihat :

terlihat kalau kode_barang yang ditampilkan adalah kode barang yang merupakan hasil dari intersect dari kedua query table.

Demikian tutorial Select Union Except Intersect Table dari Microsoft SQL Server ini, semoga dapat dipahami dan dapat bermanfaat bagi semuanya yang memerlukan informasi ini.