CREATE FUNCTION (Transact-SQL)
Berlaku untuk:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Membuat fungsi yang ditentukan pengguna. Fungsi yang ditentukan pengguna adalah rutinitas Transact-SQL atau common language runtime (CLR) yang menerima parameter, melakukan tindakan, seperti perhitungan kompleks, dan mengembalikan hasil tindakan tersebut sebagai nilai. Nilai yang dikembalikan dapat berupa nilai skalar (tunggal) atau tabel. Gunakan pernyataan ini untuk membuat rutinitas yang dapat digunakan kembali yang dapat digunakan dengan cara berikut:
Dalam pernyataan Transact-SQL seperti SELECT
Dalam aplikasi yang memanggil fungsi
Dalam definisi fungsi lain yang ditentukan pengguna
Untuk membuat parameter tampilan atau meningkatkan fungsionalitas tampilan terindeks
Untuk menentukan kolom dalam tabel
Untuk menentukan batasan CHECK pada kolom
Untuk mengganti prosedur tersimpan
Menggunakan fungsi sebaris sebagai predikat filter untuk kebijakan keamanan
Catatan
- Integrasi .NET Framework CLR ke SQL Server dibahas dalam topik ini. Integrasi CLR tidak berlaku untuk Azure SQL Database.
- Untuk Azure Synapse Analytics atau Microsoft Fabric, lihat CREATE FUNCTION (Azure Synapse Analytics dan Microsoft Fabric).
Sintaksis
-- Transact-SQL Scalar Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]
-- Transact-SQL Inline Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]
-- Transact-SQL Multi-Statement Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] [READONLY] }
[ ,...n ]
]
)
RETURNS @return_variable TABLE <table_type_definition>
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
[ ; ]
-- Transact-SQL Function Clauses
<function_option>::=
{
[ ENCRYPTION ]
| [ SCHEMABINDING ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
| [ INLINE = { ON | OFF }]
}
<table_type_definition>:: =
( { <column_definition> <column_constraint>
| <computed_column_definition> }
[ <table_constraint> ] [ ,...n ]
)
<column_definition>::=
{
{ column_name data_type }
[ [ DEFAULT constant_expression ]
[ COLLATE collation_name ] | [ ROWGUIDCOL ]
]
| [ IDENTITY [ (seed , increment ) ] ]
[ <column_constraint> [ ...n ] ]
}
<column_constraint>::=
{
[ NULL | NOT NULL ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ , ...n ] )
[ ON { filegroup | "default" } ]
| [ CHECK ( logical_expression ) ] [ ,...n ]
}
<computed_column_definition>::=
column_name AS computed_column_expression
<table_constraint>::=
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column_name [ ASC | DESC ] [ ,...n ]
[ WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , ...n ] )
| [ CHECK ( logical_expression ) ] [ ,...n ]
}
<index_option>::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS ={ ON | OFF }
}
-- CLR Scalar Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] }
[ ,...n ]
)
RETURNS { return_data_type }
[ WITH <clr_function_option> [ ,...n ] ]
[ AS ] EXTERNAL NAME <method_specifier>
[ ; ]
-- CLR Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] }
[ ,...n ]
)
RETURNS TABLE <clr_table_type_definition>
[ WITH <clr_function_option> [ ,...n ] ]
[ ORDER ( <order_clause> ) ]
[ AS ] EXTERNAL NAME <method_specifier>
[ ; ]
-- CLR Function Clauses
<order_clause> ::=
{
<column_name_in_clr_table_type_definition>
[ ASC | DESC ]
} [ ,...n]
<method_specifier>::=
assembly_name.class_name.method_name
<clr_function_option>::=
}
[ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
}
<clr_table_type_definition>::=
( { column_name data_type } [ ,...n ] )
-- In-Memory OLTP: Syntax for natively compiled, scalar user-defined function
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ NULL | NOT NULL ] [ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS return_data_type
WITH <function_option> [ ,...n ]
[ AS ]
BEGIN ATOMIC WITH (set_option [ ,... n ])
function_body
RETURN scalar_expression
END
<function_option>::=
{
| NATIVE_COMPILATION
| SCHEMABINDING
| [ EXECUTE_AS_Clause ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}
Catatan
Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.
Argumen
ATAU UBAH
Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2016 (13.x) SP1) dan Azure SQL Database
Mengubah fungsi secara kondisional hanya jika sudah ada.
Catatan
Sintaksis [OR ALTER] opsional untuk CLR tersedia dimulai dengan SQL Server 2016 (13.x) SP1 CU1.
schema_name
Adalah nama skema tempat fungsi yang ditentukan pengguna berada.
function_name
Adalah nama fungsi yang ditentukan pengguna. Nama fungsi harus mematuhi aturan untuk pengidentifikasi dan harus unik dalam database dan skemanya.
Catatan
Tanda kurung diperlukan setelah nama fungsi meskipun parameter tidak ditentukan.
@parameter_name
Adalah parameter dalam fungsi yang ditentukan pengguna. Satu atau beberapa parameter dapat dideklarasikan.
Fungsi dapat memiliki maksimum 2.100 parameter. Nilai setiap parameter yang dideklarasikan harus disediakan oleh pengguna ketika fungsi dijalankan, kecuali default untuk parameter ditentukan.
Tentukan nama parameter dengan menggunakan tanda (@) sebagai karakter pertama. Nama parameter harus mematuhi aturan untuk pengidentifikasi. Parameter bersifat lokal untuk fungsi; nama parameter yang sama dapat digunakan dalam fungsi lain. Parameter hanya dapat menggantikan konstanta; mereka tidak dapat digunakan alih-alih nama tabel, nama kolom, atau nama objek database lainnya.
Catatan
ANSI_WARNINGS tidak dihormati saat Anda meneruskan parameter dalam prosedur tersimpan, fungsi yang ditentukan pengguna, atau saat Anda mendeklarasikan dan mengatur variabel dalam pernyataan batch. Misalnya, jika variabel didefinisikan sebagai karakter(3), lalu diatur ke nilai yang lebih besar dari tiga karakter, data dipotong ke ukuran yang ditentukan dan INSERT pernyataan atau UPDATE berhasil.
[ type_schema_name . ] parameter_data_type
Adalah jenis data parameter, dan secara opsional skema tempatnya berada. Untuk fungsi Transact-SQL, semua jenis data, termasuk jenis yang ditentukan pengguna CLR dan jenis tabel yang ditentukan pengguna, diizinkan kecuali jenis data tanda waktu. Untuk fungsi CLR, semua jenis data, termasuk jenis yang ditentukan pengguna CLR, diizinkan kecuali teks, ntext, gambar, jenis tabel yang ditentukan pengguna dan jenis data tanda waktu. Jenis nonscalar, kursor , dan tabel, tidak dapat ditentukan sebagai jenis data parameter dalam fungsi Transact-SQL atau CLR.
Jika type_schema_name tidak ditentukan, Mesin Database mencari scalar_parameter_data_type dalam urutan berikut:
- Skema yang berisi nama jenis data sistem SQL Server.
- Skema default pengguna saat ini dalam database saat ini.
- Skema dbo dalam database saat ini.
[ =default ]
Adalah nilai default untuk parameter . Jika nilai default ditentukan, fungsi dapat dijalankan tanpa menentukan nilai untuk parameter tersebut.
Catatan
Nilai parameter default dapat ditentukan untuk fungsi CLR kecuali untuk jenis data varchar(max) dan varbinary(max ).
Ketika parameter fungsi memiliki nilai default, kata kunci DEFAULT harus ditentukan ketika fungsi dipanggil untuk mengambil nilai default. Perilaku ini berbeda dari menggunakan parameter dengan nilai default dalam prosedur tersimpan di mana menghilangkan parameter juga menyiratkan nilai default. Namun, kata kunci DEFAULT tidak diperlukan saat memanggil fungsi skalar dengan menggunakan pernyataan EXECUTE.
READONLY
Menunjukkan bahwa parameter tidak dapat diperbarui atau dimodifikasi dalam definisi fungsi. READONLY diperlukan untuk parameter jenis tabel (TVP) yang ditentukan pengguna, dan tidak dapat digunakan untuk jenis parameter lainnya.
return_data_type
Adalah nilai pengembalian dari fungsi skalar yang ditentukan pengguna. Untuk fungsi Transact-SQL, semua jenis data, termasuk jenis yang ditentukan pengguna CLR, diizinkan kecuali jenis data tanda waktu. Untuk fungsi CLR, semua jenis data, termasuk jenis yang ditentukan pengguna CLR, diizinkan kecuali jenis data teks, ntext, gambar, dan tanda waktu. Jenis nonscalar, kursor , dan tabel, tidak dapat ditentukan sebagai jenis data pengembalian dalam fungsi Transact-SQL atau CLR.
function_body
Menentukan bahwa serangkaian pernyataan Transact-SQL, yang bersama-sama tidak menghasilkan efek samping seperti memodifikasi tabel, menentukan nilai fungsi. function_body hanya digunakan dalam fungsi skalar dan fungsi bernilai tabel multi-pernyataan (MSTVF).
Dalam fungsi skalar, function_body adalah serangkaian pernyataan Transact-SQL yang bersama-sama mengevaluasi ke nilai skalar.
Dalam MSTVF, function_body adalah serangkaian pernyataan Transact-SQL yang mengisi variabel pengembalian TABLE.
scalar_expression
Menentukan nilai skalar yang dikembalikan fungsi skalar.
TABLE
Menentukan bahwa nilai pengembalian fungsi bernilai tabel (TVF) adalah tabel. Hanya konstanta dan @local_variables yang dapat diteruskan ke TVF.
Dalam TVF sebaris, nilai pengembalian TABLE didefinisikan melalui satu pernyataan SELECT. Fungsi sebaris tidak memiliki variabel pengembalian terkait.
Dalam MSTVF, @return_variable adalah variabel TABLE, digunakan untuk menyimpan dan mengakumulasi baris yang harus dikembalikan sebagai nilai fungsi. @ return_variable hanya dapat ditentukan untuk fungsi Transact-SQL dan bukan untuk fungsi CLR.
select_stmt
Adalah pernyataan SELECT tunggal yang menentukan nilai pengembalian fungsi bernilai tabel sebaris (TVF).
ORDER (<order_clause>)
Menentukan urutan di mana hasil dikembalikan dari fungsi bernilai tabel. Untuk informasi selengkapnya, lihat bagian , "Menggunakan Urutan Sortir di Fungsi bernilai Tabel CLR", nanti dalam topik ini.
ASSEMBLY_NAME METHOD_SPECIFIER> NAMA <EKSTERNAL.class_name.method_name
Berlaku untuk: SQL Server ( SQL Server 2008 (10.0.x) SP1 dan yang lebih baru)
Menentukan assembly dan metode yang akan dirujuk oleh nama fungsi yang dibuat.
assembly_name - harus cocok dengan nilai di
namekolomSELECT * FROM sys.assemblies;.Ini adalah nama yang digunakan pada
CREATE ASSEMBLYpernyataan.class_name - harus cocok dengan nilai di
assembly_namekolomSELECT * FROM sys.assembly_modules;.Sering kali nilai berisi titik atau titik yang disematkan. Dalam kasus seperti itu, sintaks Transact-SQL mengharuskan nilai dibatasi dengan sepasang tanda kurung lurus [], atau dengan sepasang tanda kutip ganda "".
method_name - harus cocok dengan nilai di
method_namekolomSELECT * FROM sys.assembly_modules;.Metode harus statis.
Dalam contoh umum, untuk MyFood.DLL, di mana semua jenis berada di namespace Layanan MyFood, nilainya EXTERNAL NAME bisa menjadi: MyFood.[MyFood.MyClass].MyStaticMethod
Catatan
Secara default, SQL Server tidak dapat menjalankan kode CLR. Anda dapat membuat, memodifikasi, dan menghilangkan objek database yang mereferensikan modul runtime bahasa umum; namun, Anda tidak dapat menjalankan referensi ini di SQL Server sampai Anda mengaktifkan opsi clr diaktifkan. Untuk mengaktifkan opsi ini, gunakan sp_configure. Opsi ini tidak tersedia dalam database mandiri.
<>table_type_definition ( { <column_definition<>column_constraint| <>> computed_column_definition } [ <table_constraint> ] [ ,...n ] )
Menentukan jenis data tabel untuk fungsi Transact-SQL. Deklarasi tabel menyertakan definisi kolom dan batasan kolom atau tabel. Tabel selalu dimasukkan ke dalam grup file utama.
<> clr_table_type_definition ( { column_name**data_type } [ ,...n ] )
Berlaku untuk: SQL Server (SQL Server 2008 (10.0.x) SP1 dan yang lebih baru) dan Azure SQL Database (Pratinjau di beberapa wilayah).
Menentukan jenis data tabel untuk fungsi CLR. Deklarasi tabel hanya menyertakan nama kolom dan jenis data. Tabel selalu dimasukkan ke dalam grup file utama.
NULL | BUKAN NULL
Hanya didukung untuk fungsi yang dikompilasi secara asli dan ditentukan pengguna skalar. Untuk informasi selengkapnya, lihat Fungsi Yang Ditentukan Pengguna Skalar untuk OLTP Dalam Memori.
NATIVE_COMPILATION
Menunjukkan apakah fungsi yang ditentukan pengguna dikompilasi secara asli. Argumen ini diperlukan untuk fungsi yang dikompilasi secara asli dan ditentukan pengguna skalar.
MULAI ATOM DENGAN
Hanya didukung untuk fungsi yang dikompilasi secara asli dan ditentukan pengguna skalar, dan diperlukan. Untuk informasi selengkapnya, lihat Blok Atomik.
SCHEMABINDING
Argumen SCHEMABINDING diperlukan untuk fungsi yang dikompilasi secara asli dan ditentukan pengguna skalar.
JALANKAN SEBAGAI
EXECUTE AS diperlukan untuk fungsi yang dikompilasi secara asli dan ditentukan pengguna skalar.
<>function_option::= dan <clr_function_option>::=
Menentukan bahwa fungsi akan memiliki satu atau beberapa opsi berikut.
ENKRIPSI
Berlaku untuk: SQL Server ( SQL Server 2008 (10.0.x) SP1 dan yang lebih baru)
Menunjukkan bahwa Mesin Database akan mengonversi teks asli pernyataan CREATE FUNCTION ke format yang dikaburkan. Output obfuscation tidak langsung terlihat dalam tampilan katalog apa pun. Pengguna yang tidak memiliki akses ke tabel sistem atau file database tidak dapat mengambil teks yang dikaburkan. Namun, teks akan tersedia untuk pengguna istimewa yang dapat mengakses tabel sistem melalui port DAC atau langsung mengakses file database. Selain itu, pengguna yang dapat melampirkan debugger ke proses server dapat mengambil prosedur asli dari memori saat runtime. Untuk informasi selengkapnya tentang mengakses metadata sistem, lihat Konfigurasi Visibilitas Metadata.
Menggunakan opsi ini mencegah fungsi diterbitkan sebagai bagian dari replikasi SQL Server. Opsi ini tidak dapat ditentukan untuk fungsi CLR.
SCHEMABINDING
Menentukan bahwa fungsi terikat ke objek database yang dirujuknya. Ketika SCHEMABINDING ditentukan, objek dasar tidak dapat dimodifikasi dengan cara yang akan memengaruhi definisi fungsi. Definisi fungsi itu sendiri harus terlebih dahulu dimodifikasi atau dihilangkan untuk menghapus dependensi pada objek yang akan dimodifikasi.
Pengikatan fungsi ke objek yang dirujuknya dihapus hanya ketika ne dari tindakan berikut terjadi:
- Fungsi dihilangkan.
- Fungsi dimodifikasi dengan menggunakan
ALTERpernyataan dengan opsi yangSCHEMABINDINGtidak ditentukan.
Fungsi dapat terikat skema hanya jika kondisi berikut benar:
- Fungsi ini adalah fungsi Transact-SQL.
- Fungsi dan tampilan yang ditentukan pengguna yang dirujuk oleh fungsi juga terikat skema.
- Objek yang dirujuk oleh fungsi dirujuk menggunakan nama dua bagian.
- Fungsi dan objek yang dirujuknya milik database yang sama.
- Pengguna yang menjalankan
CREATE FUNCTIONpernyataan memilikiREFERENCESizin pada objek database yang dirujuk fungsi.
MENGEMBALIKAN NULL PADA INPUT NULL | DIPANGGIL PADA INPUT NULL
Menentukan atribut OnNULLCall dari fungsi skalar. Jika tidak ditentukan, INPUT NULL CALLED ON disiratkan secara default. Ini berarti bahwa isi fungsi dijalankan bahkan jika NULL diteruskan sebagai argumen.
Jika RETURNS NULL ON NULL INPUT ditentukan dalam fungsi CLR, itu menunjukkan bahwa SQL Server dapat mengembalikan NULL ketika salah satu argumen yang diterimanya adalah NULL, tanpa benar-benar memanggil isi fungsi. Jika metode fungsi CLR yang ditentukan dalam <method_specifier> sudah memiliki atribut kustom yang menunjukkan RETURNS NULL ON NULL INPUT, tetapi pernyataan CREATE FUNCTION menunjukkan CALLED ON NULL INPUT, pernyataan CREATE FUNCTION lebih diutamakan. Atribut OnNULLCall tidak dapat ditentukan untuk fungsi bernilai tabel CLR.
JALANKAN SEBAGAI
Menentukan konteks keamanan tempat fungsi yang ditentukan pengguna dijalankan. Oleh karena itu, Anda dapat mengontrol akun pengguna mana yang digunakan SQL Server untuk memvalidasi izin pada objek database apa pun yang dirujuk oleh fungsi.
Catatan
EXECUTE AS tidak dapat ditentukan untuk fungsi bernilai tabel sebaris.
Untuk informasi selengkapnya, lihat EXECUTE AS Clause (Transact-SQL).
INLINE = { AKTIF | NONAKTIF }
Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2019 (15.x)) dan Azure SQL Database.
Menentukan apakah UDF skalar ini harus di-inlin atau tidak. Klausa ini hanya berlaku untuk fungsi skalar yang ditentukan pengguna. Klausul INLINE ini tidak wajib. Jika INLINE klausa tidak ditentukan, klausa secara otomatis diatur ke ON atau OFF berdasarkan apakah UDF tidak sebaris. Jika INLINE = ON ditentukan tetapi UDF ditemukan tidak sebaris, kesalahan akan dilemparkan. Untuk informasi selengkapnya, lihat Scalar UDF Inlining.
<>column_definition ::=
Menentukan jenis data tabel. Deklarasi tabel mencakup definisi kolom dan batasan. Untuk fungsi CLR, hanya column_name dan data_type yang dapat ditentukan.
column_name
Adalah nama kolom dalam tabel. Nama kolom harus mematuhi aturan untuk pengidentifikasi dan harus unik dalam tabel. column_name dapat terdiri dari 1 hingga 128 karakter.
data_type
Menentukan jenis data kolom. Untuk fungsi Transact-SQL, semua jenis data, termasuk jenis yang ditentukan pengguna CLR, diizinkan kecuali tanda waktu. Untuk fungsi CLR, semua jenis data, termasuk jenis yang ditentukan pengguna CLR, diizinkan kecuali teks, ntext, gambar, karakter, varchar, varchar(maks), dan tanda waktu. Kursor jenis nonscalar tidak dapat ditentukan sebagai jenis data kolom dalam fungsi Transact-SQL atau CLR.
constant_expression DEFAULT
Menentukan nilai yang disediakan untuk kolom ketika nilai tidak disediakan secara eksplisit selama penyisipan. constant_expression adalah nilai fungsi konstanta, NULL, atau sistem. Definisi DEFAULT dapat diterapkan ke kolom apa pun kecuali yang memiliki properti IDENTITY. DEFAULT tidak dapat ditentukan untuk fungsi bernilai tabel CLR.
MENYUSUN collation_name
Menentukan kolas untuk kolom. Jika tidak ditentukan, kolom diberi kolase default database. Nama kolase dapat berupa nama kolase Windows atau nama kolase SQL. Untuk daftar dan informasi selengkapnya tentang kolase, lihat Nama Kolase Windows (Transact-SQL) dan Nama Kolase SQL Server (Transact-SQL).
Klausa COLLATE dapat digunakan untuk mengubah kolase hanya kolom jenis data karakter, varchar, nchar, dan nvarchar .
Catatan
COLLATE tidak dapat ditentukan untuk fungsi bernilai tabel CLR.
ROWGUIDCOL
Menunjukkan bahwa kolom baru adalah kolom pengidentifikasi unik secara global baris. Hanya satu kolom pengidentifikasi unik per tabel yang dapat ditetapkan sebagai kolom ROWGUIDCOL. Properti ROWGUIDCOL hanya dapat ditetapkan ke kolom pengidentifikasi unik.
Properti ROWGUIDCOL tidak memberlakukan keunikan nilai yang disimpan dalam kolom. Ini juga tidak secara otomatis menghasilkan nilai untuk baris baru yang disisipkan ke dalam tabel. Untuk menghasilkan nilai unik untuk setiap kolom, gunakan fungsi NEWID pada pernyataan INSERT. Nilai default dapat ditentukan; namun, NEWID tidak dapat ditentukan sebagai default.
IDENTITY
Menunjukkan bahwa kolom baru adalah kolom identitas. Saat baris baru ditambahkan ke tabel, SQL Server menyediakan nilai unik dan bertahap untuk kolom tersebut. Kolom identitas biasanya digunakan bersama dengan batasan KUNCI PRIMER untuk berfungsi sebagai pengidentifikasi baris unik untuk tabel. Properti IDENTITY dapat ditetapkan ke kolom tinyint, smallint, int, bigint, decimal(p,0), atau numerik(p,0 ). Hanya satu kolom identitas yang dapat dibuat per tabel. Default terikat dan batasan DEFAULT tidak dapat digunakan dengan kolom identitas. Anda harus menentukan benih dan kenaikan atau tidak. Jika tidak ditentukan, defaultnya adalah (1,1).
IDENTITY tidak dapat ditentukan untuk fungsi bernilai tabel CLR.
Benih
Adalah nilai bilangan bulat yang akan ditetapkan ke baris pertama dalam tabel.
Kenaikan
Adalah nilai bilangan bulat untuk ditambahkan ke nilai seed untuk baris berturut-turut dalam tabel.
<>column_constraint ::= dan < table_constraint>::=
Menentukan batasan untuk kolom atau tabel tertentu. Untuk fungsi CLR, satu-satunya jenis batasan yang diizinkan adalah NULL. Batasan bernama tidak diperbolehkan.
NULL | BUKAN NULL
Menentukan apakah nilai null diizinkan dalam kolom. NULL bukan batasan yang ketat tetapi dapat ditentukan sama seperti NOT NULL. NOT NULL tidak dapat ditentukan untuk fungsi bernilai tabel CLR.
KUNCI PRIMER
Adalah batasan yang memberlakukan integritas entitas untuk kolom tertentu melalui indeks unik. Dalam fungsi yang ditentukan pengguna bernilai tabel, batasan KUNCI PRIMER hanya dapat dibuat pada satu kolom per tabel. PRIMARY KEY tidak dapat ditentukan untuk fungsi bernilai tabel CLR.
UNIQUE
Adalah batasan yang menyediakan integritas entitas untuk kolom atau kolom tertentu melalui indeks unik. Tabel dapat memiliki beberapa batasan UNIK. UNIQUE tidak dapat ditentukan untuk fungsi bernilai tabel CLR.
BERKLUSTER | NONCLUSTERED
Menunjukkan bahwa indeks berkluster atau non-kluster dibuat untuk batasan KUNCI PRIMER atau UNIK. Batasan KUNCI PRIMER menggunakan batasan CLUSTERED, dan UNIQUE menggunakan NONCLUSTERED.
CLUSTERED hanya dapat ditentukan untuk satu batasan. Jika CLUSTERED ditentukan untuk batasan UNIK dan batasan KUNCI PRIMER juga ditentukan, KUNCI PRIMER menggunakan NONCLUSTERED.
CLUSTERED dan NONCLUSTERED tidak dapat ditentukan untuk fungsi bernilai tabel CLR.
CHECK
Adalah batasan yang memberlakukan integritas domain dengan membatasi kemungkinan nilai yang dapat dimasukkan ke dalam kolom atau kolom. Batasan CHECK tidak dapat ditentukan untuk fungsi bernilai tabel CLR.
logical_expression
Adalah ekspresi logis yang mengembalikan TRUE atau FALSE.
<>computed_column_definition::=
Menentukan kolom komputasi. Untuk informasi selengkapnya tentang kolom komputasi, lihat CREATE TABLE (Transact-SQL).
column_name
Adalah nama kolom komputasi.
computed_column_expression
Adalah ekspresi yang menentukan nilai kolom komputasi.
<>index_option::=
Menentukan opsi indeks untuk indeks KUNCI PRIMER atau UNIK. Untuk informasi selengkapnya tentang opsi indeks, lihat CREATE INDEX (Transact-SQL).
PAD_INDEX = { AKTIF | NONAKTIF }
Menentukan padding indeks. Defaultnya adalah NONAKTIF.
FILLFACTOR = fillfactor
Menentukan persentase yang menunjukkan seberapa lengkap Mesin Database harus membuat tingkat daun setiap halaman indeks selama pembuatan atau perubahan indeks. fillfactor harus berupa nilai bilangan bulat dari 1 hingga 100. Defaultnya adalah 0.
IGNORE_DUP_KEY = { AKTIF | NONAKTIF }
Menentukan respons kesalahan saat operasi sisipkan mencoba menyisipkan nilai kunci duplikat ke dalam indeks unik. Opsi IGNORE_DUP_KEY hanya berlaku untuk menyisipkan operasi setelah indeks dibuat atau dibangun kembali. Defaultnya adalah NONAKTIF.
STATISTICS_NORECOMPUTE = { AKTIF | NONAKTIF }
Menentukan apakah statistik distribusi dikomputasi ulang. Defaultnya adalah NONAKTIF.
ALLOW_ROW_LOCKS = { AKTIF | NONAKTIF }
Menentukan apakah kunci baris diizinkan. Defaultnya adalah ON.
ALLOW_PAGE_LOCKS = { AKTIF | NONAKTIF }
Menentukan apakah kunci halaman diizinkan. Defaultnya adalah ON.
Praktik Terbaik
Jika fungsi yang ditentukan pengguna tidak dibuat dengan SCHEMABINDING klausul, perubahan yang dilakukan pada objek yang mendasar dapat memengaruhi definisi fungsi dan menghasilkan hasil yang tidak terduga saat dipanggil. Kami menyarankan agar Anda menerapkan salah satu metode berikut untuk memastikan bahwa fungsi tidak menjadi usang karena perubahan pada objek yang mendasarnya:
WITH SCHEMABINDINGTentukan klausa saat Anda membuat fungsi. Ini memastikan bahwa objek yang dirujuk dalam definisi fungsi tidak dapat dimodifikasi kecuali fungsi juga dimodifikasi.- Jalankan prosedur tersimpan sp_refreshsqlmodule setelah memodifikasi objek apa pun yang ditentukan dalam definisi fungsi.
Penting
Untuk informasi selengkapnya dan pertimbangan performa tentang fungsi bernilai tabel sebaris (TVF sebaris) dan fungsi bernilai tabel multi-pernyataan (MSTVF), lihat Membuat Fungsi yang Ditentukan Pengguna (Mesin Database).
Jenis Data
Jika parameter ditentukan dalam fungsi CLR, parameter tersebut harus berupa jenis SQL Server seperti yang didefinisikan sebelumnya untuk scalar_parameter_data_type. Untuk informasi tentang membandingkan jenis data sistem SQL Server dengan jenis data integrasi CLR atau jenis data runtime bahasa umum .NET Framework, lihat Memetakan Data Parameter CLR.
Agar SQL Server mereferensikan metode yang benar ketika kelebihan beban di kelas, metode yang ditunjukkan dalam <method_specifier> harus memiliki karakteristik berikut:
- Menerima jumlah parameter yang sama seperti yang ditentukan dalam [ ,...n ].
- Terima semua parameter menurut nilai, bukan dengan referensi.
- Gunakan jenis parameter yang kompatibel dengan yang ditentukan dalam fungsi SQL Server.
Jika jenis data pengembalian dari fungsi CLR menentukan jenis tabel (RETURNS TABLE), jenis data pengembalian metode dalam <method_specifier> harus berjenis IEnumerator atau IEnumerable, dan diasumsikan bahwa antarmuka diimplementasikan oleh pembuat fungsi. Tidak seperti fungsi Transact-SQL, fungsi CLR tidak dapat menyertakan batasan PRIMARY KEY, UNIQUE, atau CHECK dalam <table_type_definition>. Jenis data kolom yang ditentukan dalam <table_type_definition> harus cocok dengan jenis kolom yang sesuai dari kumpulan hasil yang dikembalikan oleh metode dalam <method_specifier> pada waktu eksekusi. Pemeriksaan tipe ini tidak dilakukan pada saat fungsi dibuat.
Untuk informasi selengkapnya tentang cara memprogram fungsi CLR, lihat Fungsi yang Ditentukan Pengguna CLR.
Keterangan Umum
Fungsi skalar dapat dipanggil di mana ekspresi skalar digunakan. Ini termasuk kolom komputasi dan definisi batasan CHECK. Fungsi skalar juga dapat dijalankan dengan menggunakan pernyataan EXECUTE . Fungsi skalar harus dipanggil dengan menggunakan setidaknya nama dua bagian fungsi (<skema.<>fungsi>). Untuk informasi selengkapnya tentang nama multipihak, lihat Konvensi Sintaks Transact-SQL (Transact-SQL). Fungsi bernilai tabel dapat dipanggil di mana ekspresi tabel diizinkan dalam klausul SELECTpernyataan , , INSERTUPDATE, atau DELETE .FROM Untuk informasi selengkapnya, lihat Menjalankan Fungsi yang ditentukan pengguna.
Interoperabilitas
Pernyataan berikut valid dalam fungsi:
- Pernyataan penugasan.
- Pernyataan Control-of-Flow kecuali
TRY...CATCHpernyataan. DECLAREpernyataan yang menentukan variabel data lokal dan kursor lokal.SELECTpernyataan yang berisi daftar pilihan dengan ekspresi yang menetapkan nilai ke variabel lokal.- Operasi kursor mereferensikan kursor lokal yang dideklarasikan, dibuka, ditutup, dan dibatalkan alokasinya dalam fungsi. Hanya
FETCHpernyataan yang menetapkan nilai ke variabel lokal menggunakanINTOklausul yang diizinkan;FETCHpernyataan yang mengembalikan data ke klien tidak diizinkan. INSERT,UPDATE, danDELETEpernyataan yang memodifikasi variabel tabel lokal.EXECUTEpernyataan yang memanggil prosedur tersimpan yang diperluas.
Untuk informasi selengkapnya, lihat Membuat Fungsi yang Ditentukan Pengguna (Mesin Database).
Interoperabilitas Kolom Komputasi
Fungsi memiliki properti berikut. Nilai properti ini menentukan apakah fungsi dapat digunakan dalam kolom komputasi yang dapat dipertahankan atau diindeks.
| Properti | Deskripsi | Catatan |
|---|---|---|
| IsDeterministik | Fungsi deterministik atau nondeterministik. | Akses data lokal diizinkan dalam fungsi deterministik. Misalnya, fungsi yang selalu mengembalikan hasil yang sama setiap kali dipanggil dengan menggunakan sekumpulan nilai input tertentu dan dengan status database yang sama akan diberi label deterministik. |
| IsPrecise | Fungsinya tepat atau tidak tepat. | Fungsi yang tidak tepat berisi operasi seperti operasi floating point. |
| IsSystemVerified | Properti presisi dan determinisme fungsi dapat diverifikasi oleh SQL Server. | |
| SystemDataAccess | Fungsi mengakses data sistem (katalog sistem atau tabel sistem virtual) dalam instans lokal SQL Server. | |
| UserDataAccess | Fungsi mengakses data pengguna dalam instans lokal SQL Server. | Termasuk tabel yang ditentukan pengguna dan tabel sementara, tetapi bukan variabel tabel. |
Properti presisi dan determinisme fungsi Transact-SQL ditentukan secara otomatis oleh SQL Server. Properti akses data dan determinisme fungsi CLR dapat ditentukan oleh pengguna. Untuk informasi selengkapnya, lihat Gambaran Umum Atribut Kustom Integrasi CLR.
Untuk menampilkan nilai saat ini untuk properti ini, gunakan OBJECTPROPERTYEX.
Penting
Fungsi harus dibuat dengan SCHEMABINDING menjadi deterministik.
Kolom komputasi yang memanggil fungsi yang ditentukan pengguna dapat digunakan dalam indeks saat fungsi yang ditentukan pengguna memiliki nilai properti berikut:
- IsDeterministic = true
- IsSystemVerified = true (kecuali kolom komputasi dipertahankan)
- UserDataAccess = false
- SystemDataAccess = false
Untuk informasi selengkapnya, lihat Indeks pada Kolom Komputasi.
Memanggil Prosedur Tersimpan yang Diperluas dari Fungsi
Prosedur tersimpan yang diperluas, ketika dipanggil dari dalam fungsi, tidak dapat mengembalikan tataan hasil ke klien. API ODS apa pun yang mengembalikan tataan hasil ke klien akan mengembalikan FAIL. Prosedur tersimpan yang diperluas dapat terhubung kembali ke instans SQL Server; namun, tidak boleh mencoba menggabungkan transaksi yang sama dengan fungsi yang memanggil prosedur tersimpan yang diperluas.
Mirip dengan pemanggilan dari batch atau prosedur tersimpan, prosedur tersimpan yang diperluas akan dijalankan dalam konteks akun keamanan Windows tempat SQL Server berjalan. Pemilik prosedur tersimpan harus mempertimbangkan hal ini saat memberikan izin EXECUTE kepada pengguna.
Batasan dan Pembatasan
Fungsi yang ditentukan pengguna tidak dapat digunakan untuk melakukan tindakan yang mengubah status database.
Fungsi yang ditentukan pengguna tidak boleh berisi OUTPUT INTO klausul yang memiliki tabel sebagai targetnya.
Pernyataan Service Broker berikut tidak dapat disertakan dalam definisi fungsi yang ditentukan pengguna Transact-SQL:
BEGIN DIALOG CONVERSATIONEND CONVERSATIONGET CONVERSATION GROUPMOVE CONVERSATIONRECEIVESEND
Fungsi yang ditentukan pengguna dapat ditumpuk; artinya, satu fungsi yang ditentukan pengguna dapat memanggil fungsi lain. Tingkat berlapis bertahpa ketika fungsi yang disebut memulai eksekusi, dan dikurangi ketika fungsi yang dipanggil menyelesaikan eksekusi. Fungsi yang ditentukan pengguna dapat ditumpuk hingga 32 tingkat. Melebihi tingkat maksimum bersarang menyebabkan seluruh rantai fungsi panggilan gagal. Referensi apa pun ke kode terkelola dari fungsi yang ditentukan pengguna Transact-SQL dihitung sebagai satu tingkat terhadap batas berlapis 32 tingkat. Metode yang dipanggil dari dalam kode terkelola tidak dihitung terhadap batas ini.
Menggunakan Urutan Sortir dalam Fungsi bernilai Tabel CLR
Saat menggunakan ORDER klausa dalam fungsi bernilai tabel CLR, ikuti panduan berikut:
Anda harus memastikan bahwa hasil selalu diurutkan dalam urutan yang ditentukan. Jika hasilnya tidak dalam urutan yang ditentukan, SQL Server akan menghasilkan pesan kesalahan saat kueri dijalankan.
ORDERJika klausa ditentukan, output fungsi bernilai tabel harus diurutkan sesuai dengan kolase kolom (eksplisit atau implisit). Misalnya, jika kolase kolom adalah Bahasa Tionghoa (ditentukan dalam DDL untuk fungsi bernilai tabel atau diperoleh dari kolase database), hasil yang dikembalikan harus diurutkan sesuai dengan aturan pengurutan Tionghoa.Klausa
ORDER, jika ditentukan, selalu diverifikasi oleh SQL Server saat mengembalikan hasil, apakah itu digunakan oleh prosesor kueri atau tidak untuk melakukan pengoptimalan lebih lanjut. Hanya gunakanORDERklausa jika Anda tahu itu berguna untuk prosesor kueri.Prosesor kueri SQL Server memanfaatkan
ORDERklausul secara otomatis dalam kasus berikut:- Sisipkan kueri di mana
ORDERklausa kompatibel dengan indeks. ORDER BYklausa yang kompatibel denganORDERklausa.- Agregat, di mana
GROUP BYkompatibel denganORDERklausul. DISTINCTagregat di mana kolom yang berbeda kompatibel denganORDERklausa.
- Sisipkan kueri di mana
Klausa ORDER tidak menjamin hasil yang diurutkan saat kueri SELECT dijalankan, kecuali ORDER BY juga ditentukan dalam kueri. Lihat sys.function_order_columns (Transact-SQL) untuk informasi tentang cara mengkueri kolom yang disertakan dalam urutan pengurutan untuk fungsi bernilai tabel.
Metadata
Tabel berikut mencantumkan tampilan katalog sistem yang bisa Anda gunakan untuk mengembalikan metadata tentang fungsi yang ditentukan pengguna.
| Tampilan Sistem | Deskripsi |
|---|---|
| sys.sql_modules | Lihat contoh E di bagian Contoh di bawah ini. |
| sys.assembly_modules | Menampilkan informasi tentang fungsi yang ditentukan pengguna CLR. |
| sys.parameters | Menampilkan informasi tentang parameter yang ditentukan dalam fungsi yang ditentukan pengguna. |
| sys.sql_expression_dependencies | Menampilkan objek yang mendasar yang dirujuk oleh fungsi. |
Izin
CREATE FUNCTION Memerlukan izin dalam database dan ALTER izin pada skema tempat fungsi sedang dibuat. Jika fungsi menentukan jenis yang ditentukan pengguna, memerlukan EXECUTE izin pada jenis tersebut.
Contoh
Catatan
Untuk contoh dan pertimbangan performa selengkapnya tentang UDF, lihat Membuat Fungsi yang Ditentukan Pengguna (Mesin Database).
J. Menggunakan fungsi bernilai skalar yang ditentukan pengguna yang menghitung minggu ISO
Contoh berikut membuat fungsi ISOweekyang ditentukan pengguna . Fungsi ini mengambil argumen tanggal dan menghitung nomor minggu ISO. Agar fungsi ini dihitung dengan benar, SET DATEFIRST 1 harus dipanggil sebelum fungsi dipanggil.
Contoh ini juga menunjukkan penggunaan klausul EXECUTE AS untuk menentukan konteks keamanan di mana prosedur tersimpan dapat dijalankan. Dalam contoh, opsi CALLER menentukan bahwa prosedur akan dijalankan dalam konteks pengguna yang memanggilnya. Opsi lain yang dapat Anda tentukan adalah SELF, , OWNERdan user_name.
Berikut adalah panggilan fungsi. Perhatikan bahwa DATEFIRST diatur ke 1.
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @ISOweek int;
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1;
RETURN(@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';
Berikut adalah hasil yang ditetapkan.
ISO Week
----------------
52
B. Membuat fungsi bernilai tabel sebaris
Contoh berikut mengembalikan fungsi bernilai tabel sebaris dalam database AdventureWorks2022. Ini mengembalikan tiga kolom ProductID, Name dan agregat total tahun ke tanggal berdasarkan penyimpanan seperti YTD Total untuk setiap produk yang dijual ke toko.
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
);
GO
Untuk memanggil fungsi, jalankan kueri ini.
SELECT * FROM Sales.ufn_SalesByStore (602);
C. Membuat fungsi bernilai tabel multi-pernyataan
Contoh berikut membuat fungsi fn_FindReports(InEmpID) bernilai tabel dalam AdventureWorks2022 database. Ketika disediakan dengan ID karyawan yang valid, fungsi mengembalikan tabel yang sesuai dengan semua karyawan yang melaporkan kepada karyawan baik secara langsung maupun tidak langsung. Fungsi ini menggunakan ekspresi tabel umum rekursif (CTE) untuk menghasilkan daftar hierarkis karyawan. Untuk informasi selengkapnya tentang CTE rekursif, lihat WITH common_table_expression (Transact-SQL).
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
EmployeeID int primary key NOT NULL,
FirstName nvarchar(255) NOT NULL,
LastName nvarchar(255) NOT NULL,
JobTitle nvarchar(50) NOT NULL,
RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
AS (
-- Get the initial list of Employees for Manager n
SELECT e.BusinessEntityID, OrganizationNode = ISNULL(e.OrganizationNode, CAST('/' AS hierarchyid))
, p.FirstName, p.LastName, e.JobTitle, 0
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = @InEmpID
UNION ALL
-- Join recursive member to anchor
SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1
FROM HumanResources.Employee e
INNER JOIN EMP_cte
ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM EMP_cte
RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1);
GO
D. Membuat fungsi CLR
Contoh membuat fungsi len_sCLR . Sebelum fungsi dibuat, rakitan SurrogateStringFunction.dll terdaftar di database lokal.
Berlaku untuk: SQL Server ( SQL Server 2008 (10.0.x) SP1 dan yang lebih baru)
DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE
( physical_name
, 'Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf'
, 'Microsoft SQL Server\130\Samples\Engine\Programmability\CLR\'
)
FROM master.sys.database_files
WHERE name = 'master';
CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO
Untuk contoh cara membuat fungsi bernilai tabel CLR, lihat Fungsi Bernilai Tabel CLR.
E. Menampilkan definisi fungsi yang ditentukan pengguna
SELECT definition, type
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id
AND type IN ('FN', 'IF', 'TF');
GO
Definisi fungsi yang dibuat dengan menggunakan ENCRYPTION opsi tidak dapat dilihat dengan menggunakan sys.sql_modules; namun, informasi lain tentang fungsi terenkripsi ditampilkan.
Lihat Juga
- Membuat Fungsi yang Ditentukan Pengguna (Mesin Database)
- ALTER FUNCTION (Transact-SQL)
- DROP FUNCTION (Transact-SQL)
- OBJECTPROPERTYEX (Transact-SQL)
- sys.sql_modules (T-SQL)
- sys.assembly_modules (T-SQL)
- EXECUTE (Transact-SQL)
- Fungsi yang Ditentukan Pengguna CLR
- EVENTDATA (Transact-SQL)
- MEMBUAT KEBIJAKAN KEAMANAN (Transact-SQL)
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk