latihan sql

SQL
Stored Procedure  |  Cursor  |  Trigger  |  Function  |  Pivot  |  Optimizing  |  Job



Oleh        : Kresno

Date        : 09-03-2015



Stored Procedure



Kalau kata mssqltips.com, stored procedure (SP) adalah query yg sudah disiapkan dan disimpan, untuk dapat digunakan kembali berkali-kali. Jadi kita simpan suatu query yg sering kita pakai dalam bentuk SP, jika akan digunakan tinggal panggil SP tersebut.

Format :

Create Procedure/Proc Nama SP

               @Parameter 1   tipedata,

               @Parameter 2    tipedata,

               dst

As

Select field1, field2, dst

From nama table

Where filter 1 = @Parameter1

And filter2 = @Parameter2

And dst..



Bisa berupa select, insert, update, delete. Cara memanggilnya adalah EXEC NamaSP.

Contoh :

Create Proc BooksRetrieve

               @BookCode varchar(10)

As

Select BookCode, BookName, Author, Price

From BookList

Where BookCode = @BookCode



Exec BooksRetrieve ‘B150001’





Create Proc BooksInsert

               @BookCode varchar(10),

               @BookName varchar(50),

               @Author varchar(20),

               @Price numeric(8,2)

AS

Insert into BookList(BookCode, BookName, Author, Price)

Values(@BookCode, @BookName, @Author, @Price)



Exec BooksInsert ‘B150002’, ’Hanya Titik’, ’Saya’, 15000







Cursor



Disadur dari codeproject.com, cursor adalah kumpulan baris-baris data dengan penanda(pointer) yang menunjuk baris tertentu. Kalau mau dibayangin mah fungsinya mirip-mirip dengan FOR.

Format :

Declare        @ParameterPenampung1 varchar(10),

                       @ParameterPenampung2 varchar(10),

                       dst..

Declare NamaCursor Cursor For

Select Field1, Field2, dst..

From NamaTable

Where Filter1

And Filter2

And dst

Open NamaCursor

Fetch Next from NamaCursor

Into @ParameterPenampung1, @ParameterPenampung2, dst..



While @@FetchStatus =  0

Begin

               …

Fetch Next from NamaCursor

Into @ParameterPenampung1, @ParameterPenampung2, dst..

End

Close NamaCursor

Deallocate NamaCursor



Tipe Cursor :



Static Cursor

Bisa bergerak mundur dan maju. Data yg dihasilkan hanya ketika cursor di-open, jika setelah open ada data yg di-insert, update, delete tidak akan masuk ke cursor kecuali cursor di close dan di-

open kembali.



Dynamic Cursor

Hampir mirip seperti static cursor, bedanya adalah datanya ikut berubah jika ada insert update

delete.



Forward-Only Cursor

Mirip seperti dynamic cursor, namun tidak dapat bergerak mundur.



Key-Set Driven Cursor







Syntax :



Open

Membuka Transact-SQL Server cursor



Close

Menutup cursor yang terbuka dengan melepaskan record yang ditunjuk dan melepaskan cursor lock yang

terdapat pada record yang ditunjuk terakhir kali



Deallocate

Menghilangkan cursor reference





FetchStatus :



0

Fetch statement sukses

-

1

Fetch statement gagal

-

2

Row fetched is missing







Trigger



Baca-baca dari sqlteam.com, Trigger adalah objek database yg melekat ke table. Kurang lebih mirip seperti Stored Procedure. Bedanya adalah jika SP bebas dipanggil kapan saja, Trigger akan dipanggil jika ada event yg mempergaruhi table yaitu Insert, Update, Delete.

Format :

Create Trigger NamaTrigger

On NamaTable

For Insert, Update, Delete

As

Select *

From Inserted/Updated/Deleted



Contoh :

Insert log transaksi

Create Trigger InsertLog

On Transaction_Hdr

For Insert

As

Insert into TransactionLog(Date, Description, By)

Select TransactionDate, ‘New Transaction’, CreateBy

From Inserted







Function



Nyontek ke dotnet-tricks.com, function adalah kumpulan perintah sql yang menerima inputan, melakukan proses dan mengembalikan nilai dari proses tersebut. Nilai yg dikembalikan dapat berupa nilai tunggal ataupun sebuah table.



User defined function adalah function-function yang dibuat oleh user. Jenisnya :



Scalar Function

Me-return nilai tunggal


Cth :

Create function GetFullName

(

       @FirstName varchar(10),

       @LastName varchar(10)

)

Returns varchar(20)

As

Begin

       Return (select @FirstName + ' ' + @LastName)

End



Select dbo.GetFullName('Nino', 'Aja')






Table Valued Function

Me-return tabel


Cth :

Create function GetBooksByYear

(

       @Year varchar(4)

)

Returns Table

As

Select *

From BookList

Where year(PublishDate) = @Year



Select dbo.GetBooksByYear(‘2015’)







Pivot



Pivot digunakan jika ingin mengubah data dari bentuk baris menjadi bentuk kolom.

Contoh :


SalesPerson

Product

SalesAmount



Bob

Pickles

$100.00



Sue

Oranges

$50.00



Bob

Pickles

$25.00



Bob

Oranges

$300.00



Sue

Oranges

$500.00




SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles

FROM

(SELECT SalesPerson, Product, SalesAmount

FROM ProductSales ) ps

PIVOT

(

SUM (SalesAmount)

FOR Product IN

( [Oranges], [Pickles])

) AS pvt




SalesPerson

Oranges

Pickles



Bob

$300.00

$125.00



Sue

$550.00








Atau bisa juga dengan :

Declare @Query as varchar(1000)

Declare @ColumnName as varchar(1000)

Select @ColumnName = Isnull(@ColumnName + ‘,’,’’) + QuoteName(Product,’[]’)

From (select distinct Product from ProductSales) as prod

Set @Query = ‘Select *

               From

(SELECT SalesPerson, Product, SalesAmount

FROM ProductSales ) ps

PIVOT

(

SUM (SalesAmount)

FOR Product IN( ‘ + @ColumnName   + ‘)

) AS pvt’



Exec (@Query)







Optimizing Database And Queries



Tips untuk meningkatkan performa database :



1.

Pilih tipe data yang cocok





Cth: Untuk string lbh dr 8000 character menggunakan tipe data text, dibawah itu menggunakan varchar.



2.

Hindari menggunakan nchar dan nvarchar





Gunakan nchar atau nvarchar untuk menyimpan Unicode.



3.

Hindari nilai NULL pada field dgn panjang fixed (char)



4.

Hindari select *





Agar sql tidak perlu convert * ke field name.

Select * from BookList

menjadi

Select BookCode, BookName, Author, Price from BookList



5.

Jika data dalam subquery-nya besar/banyak gunakan EXISTS daripada IN, EXISTS lebih cepat.





Select BookCode, BookName, Author, Price from BookList

Where BookCode IN ( select distinct bookcode from Order)

menjadi

Select BookCode, BookName, Author, Price from BookList

Where BookCode EXISTS ( select * from Order where order.bookcode = booklist.bookcode)



6.

Hindari penggunaan HAVING





Gunakan having hanya untuk filter berdasarkan aggregation

Select … from …

Group by …

Having sum(…)



7.

Hindari CURSOR



8.

Gunakan UNION ALL disbanding UNION



9.

Gunakan nama schema sebelum sql object





Select BookCode, BookName, Author, Price from BookList

menjadi

Select BookCode, BookName, Author, Price from dbo.BookList



10.

Set NOCOUNT ON





Sql mengembalikan jumlah baris yang terproses oleh perintah Select, Insert, Update, Delete.

Create Proc …

As

Set NOCOUNT ON



11.

Gunakan TRY - CATCH





Untuk menghindari error yang dapat menyebabkan deadlock.



12.

Gunakan Stored Procedure untuk query yg sering dipakai atau yg kompleks.



13.

Hindari prefix “sp_” pada stored procedure yg dibuat sendiri




SP_ merupakan prefix default untuk system defined SP. Sql akan mencari lebih dahulu pada system defined SP baru ke

user defined SP.



14.

Gunakan filter yang akan mengurangi paling banyak baris pada WHERE



15.

Left Join lebih cepat daripada Right Join



16.

(>=) lebih cepat daripada (>)

>= 8 lebih cepat drpd > 7



17.

Hindari menggunakan LIKE



18.

Kadang menggunakan UNION dari 2 query bisa lebih baik daripada 1 query