Sebelumnya

Berikutnya

Membuat fungsi yang ditentukan pengguna

Selesai

Fungsi yang ditentukan pengguna (UDF) mirip dengan prosedur tersimpan karena disimpan secara terpisah dari tabel dalam database. Fungsi ini menerima parameter, melakukan tindakan, lalu menampilkan hasil tindakan sebagai nilai tunggal (skalar) atau tataan hasil (bernilai tabel). Anda kemudian dapat menggunakan fungsi sebagai pengganti tabel saat menulis pernyataan SELECT. Fungsi yang ditentukan pengguna dimaksudkan untuk melakukan penghitungan dan menggunakan hasil tersebut dalam pernyataan lain. Sedangkan prosedur tersimpan dapat merangkum fungsi dan pernyataan, dan bahkan memodifikasi data dalam database.

Kami akan meninjau tiga jenis fungsi yang ditentukan pengguna. Untuk detail selengkapnya tentang berbagai fungsi, tinjau Dokumentasi referensi T-SQL.

Fungsi bernilai tabel sebaris

Fungsi bernilai tabel sebaris (TVF) adalah fungsi paling sederhana yang dibuat berdasarkan pernyataan SELECT, dan fungsi tersebut adalah pilihan yang lebih disukai untuk performa.

Dalam contoh berikut, fungsi bernilai tabel dibuat dengan parameter input untuk unitprice.

CREATE FUNCTION SalesLT.ProductsListPrice(@cost money)  
RETURNS TABLE  
AS  
RETURN  
    SELECT ProductID, Name, ListPrice  
    FROM SalesLT.Product  
    WHERE ListPrice > @cost; 

Saat fungsi bernilai tabel dijalankan dengan nilai untuk parameter, semua produk dengan harga satuan lebih dari nilai ini akan dikembalikan.

Kode berikut menggunakan fungsi bernilai tabel sebagai ganti tabel.

SELECT Name, ListPrice  
FROM SalesLT.ProductsListPrice(500);

Fungsi bernilai tabel multipernyataan

Tidak seperti TVF sebaris, fungsi bernilai tabel multipernyataan (MSTVF) dapat memiliki lebih dari satu pernyataan dan memiliki persyaratan sintaksis yang berbeda.

Perhatikan bagaimana dalam kode berikut, kami menggunakan BEGIN/END selain RETURN:

CREATE FUNCTION Sales.mstvf_OrderStatus ()
RETURNS
@Results TABLE
     ( CustomerID int, OrderDate datetime )
AS
BEGIN
     INSERT INTO @Results
     SELECT SC.CustomerID, OrderDate
     FROM Sales.Customer AS SC
     INNER JOIN Sales.SalesOrderHeader AS SOH
        ON SC.CustomerID = SOH.CustomerID
     WHERE Status >= 5
 RETURN;
END;

Setelah dibuat, Anda mereferensikan MSTVF sebagai pengganti tabel seperti fungsi sebaris sebelumnya di atas. Anda juga dapat mereferensikan output dalam klausul FROM dan menggabungkannya dengan tabel lain.

SELECT *
FROM Sales.mstvf_OrderStatus();

Pertimbangan performa

Pengoptimal Kueri tidak dapat memperkirakan berapa banyak baris yang akan dikembalikan untuk fungsi bernilai tabel multipernyataan, tetapi dapat memperkirakan untuk fungsi bernilai tabel sebaris. Oleh karena itu, gunakan TVF sebaris jika memungkinkan untuk performa yang lebih baik. Jika Anda tidak perlu menggabungkan MSTVF dengan tabel lain dan/atau Anda tahu hasilnya hanya akan berupa beberapa baris, dampak performanya tidak memprihatinkan. Jika Anda mengharapkan tataan hasil besar dan perlu bergabung dengan tabel lain, pertimbangkan untuk menggunakan tabel sementara untuk menyimpan hasilnya lalu bergabung ke tabel sementara.

Dalam versi SQL Server 2017 dan yang lebih tinggi, Microsoft memperkenalkan fitur untuk pemrosesan kueri cerdas guna meningkatkan performa untuk MSTVF. Lihat detail selengkapnya tentang fitur Pemrosesan Kueri Cerdas dalam Dokumentasi Referensi T-SQL.

Fungsi skalar yang ditentukan pengguna

Fungsi skalar yang ditentukan pengguna hanya menampilkan satu nilai tidak seperti fungsi bernilai tabel dan karenanya sering digunakan untuk pernyataan sederhana dan sering.

Berikut adalah contoh untuk mendapatkan harga daftar produk untuk produk tertentu pada hari tertentu:

CREATE FUNCTION dbo.ufn_GetProductListPrice
(@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
BEGIN
    DECLARE @ListPrice money;
        SELECT @ListPrice = plph.[ListPrice]
        FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductListPriceHistory] plph 
        ON p.[ProductID] = plph.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND StartDate = @OrderDate
    RETURN @ListPrice;
END;
GO

Untuk fungsi ini, kedua parameter harus disediakan untuk mendapatkan nilainya. Bergantung pada fungsinya, Anda dapat mencantumkan fungsi dalam pernyataan SELECT dalam kueri yang lebih kompleks.

    SELECT dbo.ufn_GetProductListPrice (707, '2011-05-31')

Mengikat fungsi ke objek yang direferensikan

SCHEMABINDING bersifat opsional saat membuat fungsi. Saat Anda menentukan SCHEMABINDING, SCHEMABINDING mengikat fungsi ke objek yang direferensikan, lalu objek tidak dapat dimodifikasi tanpa juga memodifikasi fungsi. Fungsi harus terlebih dahulu dimodifikasi atau dihapus untuk menghapus dependensi sebelum memodifikasi objek.

SCHEMABINDING dihapus jika salah satu hal berikut ini terjadi:

• Fungsi dihapus
• Fungsi dimodifikasi dengan pernyataan ALTER tanpa menentukan SCHEMABINDING

Lanjutkan