Membuat fungsi skalar

Fungsi skalar adalah alat penting di SQL Server yang memungkinkan Anda merangkum logika yang dapat digunakan kembali dan mengembalikan satu nilai. Anda dapat menggunakannya secara langsung dalam SELECT pernyataan, WHERE klausul, dan ekspresi T-SQL lainnya, membuat kueri Anda lebih dapat dipertahankan dan kode Anda lebih modular.

Memahami dasar-dasar fungsi skalar

Fungsi skalar menerima nol atau lebih parameter dan mengembalikan satu nilai dari jenis data tertentu. Tidak seperti prosedur tersimpan, fungsi skalar dapat disematkan langsung dalam ekspresi SQL di mana pun Anda akan menggunakan kolom atau variabel.

Karakteristik utama fungsi skalar mencakup kemampuannya untuk menerima parameter input, melakukan perhitungan atau transformasi, dan mengembalikan tepat satu nilai. Anda menentukan jenis data pengembalian secara eksplisit dalam definisi fungsi, yang divalidasi SQL Server pada waktu pembuatan.

Saat Anda membuat fungsi skalar, Anda membuat bagian logika yang dapat digunakan kembali yang dapat dipanggil pengembang lain di seluruh database. Ini mempromosikan penggunaan kembali kode dan membantu menjaga konsistensi di seluruh aplikasi Anda.

Menentukan sintaks fungsi skalar

Untuk membuat fungsi skalar, Anda menggunakan CREATE FUNCTION pernyataan dengan komponen sintaks tertentu. Struktur dasar mencakup nama fungsi, parameter, jenis pengembalian, dan isi fungsi.

Berikut adalah pola sintaks dasarnya:

CREATE FUNCTION schema_name.function_name 
(
    @parameter1 datatype,
    @parameter2 datatype
)
RETURNS return_datatype
AS
BEGIN
    -- Function logic here
    RETURN @result
END

Klausa RETURNS menentukan jenis data dari nilai tunggal yang dikembalikan fungsi. Dalam blok BEGIN...END, Anda menulis logika T-SQL Anda dan menggunakan perintah RETURN untuk mengirim kembali hasilnya.

Misalnya, Anda dapat membuat fungsi sederhana yang menghitung pajak penjualan:

CREATE FUNCTION dbo.CalculateSalesTax
(
    @Amount DECIMAL(10,2),
    @TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @TaxAmount DECIMAL(10,2)
    SET @TaxAmount = @Amount * @TaxRate
    RETURN @TaxAmount
END

Fungsi ini menerima dua parameter dan mengembalikan jumlah pajak yang dihitung. Anda dapat menggunakan fungsi ini dalam pernyataan apa pun SELECT .

Menerapkan fungsi skalar dengan logika bisnis

Fungsi skalar unggul dalam merangkum aturan dan perhitungan bisnis yang perlu Anda terapkan secara konsisten di seluruh database Anda. Dengan fungsi skalar, Anda memusatkan logika yang mungkin diduplikasi dalam beberapa kueri atau kode aplikasi.

Pertimbangkan skenario di mana Anda perlu menghitung masa kerja karyawan dalam beberapa tahun. Anda membuat fungsi skalar yang menerima tanggal sewa dan mengembalikan jumlah tahun lengkap:

CREATE FUNCTION dbo.GetEmployeeTenure
(
    @HireDate DATE
)
RETURNS INT
AS
BEGIN
    DECLARE @Tenure INT
    SET @Tenure = DATEDIFF(YEAR, @HireDate, GETDATE())
    RETURN @Tenure
END

Anda dapat menggunakan fungsi ini dalam kueri untuk menampilkan informasi penyewaan:

SELECT 
    EmployeeName,
    HireDate,
    dbo.GetEmployeeTenure(HireDate) AS YearsOfService
FROM Employees
WHERE dbo.GetEmployeeTenure(HireDate) >= 5

Pendekatan ini memastikan perhitungan tenure yang konsisten di seluruh database Anda. Jika aturan bisnis berubah, Anda mengubah fungsi sekali daripada memperbarui beberapa kueri.

Menerapkan praktik terbaik untuk fungsi skalar

Saat Anda membuat fungsi skalar, beberapa praktik terbaik membantu memastikan performa dan pemeliharaan yang optimal. Memahami praktik ini membantu Anda menghindari perangkap umum dan menciptakan fungsi yang efisien dan andal.

Pertama, jaga deterministik fungsi skalar Anda jika memungkinkan. Fungsi deterministik selalu mengembalikan hasil yang sama mengingat parameter input yang sama. Fungsi yang mereferensikan fungsi atau tabel tanggal/waktu sistem tidak deterministik dan dapat mencegah pengoptimalan kueri tertentu.

Selain itu, hindari efek samping dalam fungsi Anda. Fungsi skalar tidak boleh mengubah status database atau memiliki dependensi pada sumber daya eksternal. Pembatasan ini ada karena SQL Server dapat menjalankan fungsi beberapa kali atau dalam urutan yang berbeda dari yang Anda harapkan.

Terakhir, perhatikan implikasi performa. Saat Anda menggunakan fungsi skalar dalam WHERE klausa atau SELECT daftar dengan tabel besar, SQL Server dapat menjalankan fungsi untuk setiap baris. Hal ini dapat berdampak signifikan pada performa kueri. Untuk skenario tersebut, pertimbangkan fungsi bernilai tabel sebaris sebagai alternatif.

Berikut adalah contoh fungsi skalar yang dirancang dengan baik yang mengikuti praktik berikut:

CREATE FUNCTION dbo.FormatPhoneNumber
(
    @PhoneNumber VARCHAR(10)
)
RETURNS VARCHAR(14)
AS
BEGIN
    DECLARE @FormattedNumber VARCHAR(14)
    
    IF LEN(@PhoneNumber) = 10
        SET @FormattedNumber = '(' + SUBSTRING(@PhoneNumber, 1, 3) + ') ' +
                               SUBSTRING(@PhoneNumber, 4, 3) + '-' +
                               SUBSTRING(@PhoneNumber, 7, 4)
    ELSE
        SET @FormattedNumber = @PhoneNumber
    
    RETURN @FormattedNumber
END

Fungsi ini deterministik, tidak memiliki efek samping, dan melakukan transformasi yang mudah. Ini menangani input yang tidak valid dengan baik dengan mengembalikan nilai asli ketika nomor telepon tidak cocok dengan format yang diharapkan.

Mengubah dan mengelola fungsi skalar

Setelah membuat fungsi skalar, Anda dapat memodifikasi definisinya menggunakan ALTER FUNCTION pernyataan . Sintaks ALTER FUNCTION mencerminkan CREATE FUNCTION tetapi memungkinkan Anda mengubah fungsi tanpa menghilangkan dan membuatnya kembali, yang mempertahankan izin dan dependensi.

ALTER FUNCTION dbo.CalculateSalesTax
(
    @Amount DECIMAL(10,2),
    @TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @TaxAmount DECIMAL(10,2)
    -- Updated logic with rounding
    SET @TaxAmount = ROUND(@Amount * @TaxRate, 2)
    RETURN @TaxAmount
END

Untuk menghapus fungsi skalar, Anda menggunakan DROP FUNCTION pernyataan :

DROP FUNCTION IF EXISTS dbo.CalculateSalesTax

Klausa IF EXISTS mencegah kesalahan jika fungsi tidak ada, yang berguna dalam skrip penyebaran. Sebelum menghilangkan fungsi, verifikasi bahwa tidak ada objek database lain yang bergantung padanya dengan memeriksa tampilan sistem seperti sys.sql_expression_dependencies.