Menggunakan Parameter Table-Valued (Mesin Basis Data)

Parameter bernilai tabel dideklarasikan dengan menggunakan jenis tabel yang ditentukan pengguna. Anda dapat menggunakan parameter bernilai tabel untuk mengirim beberapa baris data ke pernyataan Transact-SQL atau rutinitas, seperti prosedur atau fungsi tersimpan, tanpa membuat tabel sementara atau banyak parameter.

Parameter bernilai tabel seperti array parameter di OLE DB dan ODBC, tetapi menawarkan lebih banyak fleksibilitas dan integrasi yang lebih dekat dengan Transact-SQL. Parameter bernilai tabel juga memiliki manfaat untuk berpartisipasi dalam operasi berbasis kumpulan.

Transact-SQL meneruskan parameter bernilai tabel ke rutinitas dengan referensi untuk menghindari pembuatan salinan data input. Anda dapat membuat dan menjalankan rutinitas Transact-SQL dengan parameter bernilai tabel, dan memanggilnya dari kode Transact-SQL, klien terkelola dan asli dalam bahasa terkelola apa pun.

Dalam Topik ini:

Keuntungan

Pembatasan

ParameterTable-Valued vs. Operasi Pemasukan Massal

Contoh

Keuntungan

Parameter bernilai tabel dilingkup ke prosedur tersimpan, fungsi, atau teks Transact-SQL dinamis, persis seperti parameter lainnya. Demikian pula, variabel jenis tabel memiliki cakupan seperti variabel lokal lainnya yang dibuat dengan menggunakan pernyataan DECLARE. Anda dapat mendeklarasikan variabel bernilai tabel dalam pernyataan Transact-SQL dinamis dan meneruskan variabel ini sebagai parameter bernilai tabel ke prosedur dan fungsi tersimpan.

Parameter bernilai tabel menawarkan lebih banyak fleksibilitas dan dalam beberapa kasus performa yang lebih baik daripada tabel sementara atau cara lain untuk meneruskan daftar parameter. Parameter bernilai tabel menawarkan manfaat berikut:

• Jangan memperoleh kunci untuk populasi awal data dari klien.

• Menyediakan model pemrograman sederhana.

• Memungkinkan Anda menyertakan logika bisnis yang kompleks dalam satu rutinitas.

• Kurangi perjalanan pulang pergi ke server.

• Dapat memiliki struktur tabel kardinalitas yang berbeda.

• Dititik dengan kuat.

• Aktifkan klien untuk menentukan urutan pengurutan dan kunci unik.

• Dicache seperti tabel sementara, ketika digunakan dalam prosedur simpan. Dimulai dengan SQL Server 2012, parameter bernilai tabel juga di-cache untuk kueri berparameter.

Pembatasan

Parameter bernilai tabel memiliki batasan berikut:

• SQL Server tidak mempertahankan statistik pada kolom parameter bernilai tabel.

• Parameter bernilai tabel harus diteruskan sebagai parameter READONLY input ke rutinitas Transact-SQL. Anda tidak dapat melakukan operasi DML seperti UPDATE, DELETE, atau INSERT pada parameter bernilai tabel dalam isi rutinitas.

• Anda tidak dapat menggunakan parameter bernilai tabel sebagai target pernyataan SELECT INTO atau INSERT EXEC. Parameter bernilai tabel dapat berada dalam klausul FROM SELECT INTO, dalam perintah INSERT EXEC, atau dalam prosedur tersimpan.

Parameter Table-Valued vs. Operasi INSERT MASSAL

Menggunakan parameter bernilai tabel sebanding dengan cara lain menggunakan variabel berbasis set; namun, sering menggunakan parameter bernilai tabel dapat lebih cepat untuk himpunan data besar. Dibandingkan dengan operasi massal yang memiliki biaya startup yang lebih besar daripada parameter bernilai tabel, parameter bernilai tabel berkinerja baik untuk menyisipkan kurang dari 1000 baris.

Parameter bernilai tabel yang digunakan kembali mendapat manfaat dari penyimpanan sementara tabel. Mekanisme caching tabel ini memungkinkan skalabilitas yang lebih baik dibandingkan dengan operasi INSERT MASSAL yang setara. Dengan menggunakan operasi penyisipan baris kecil, keuntungan kinerja kecil dapat diperoleh dengan menggunakan daftar parameter atau pernyataan yang dibundel, bukan operasi INSERT MASSAL atau parameter bernilai tabel. Namun, metode ini kurang mudah diprogram, dan kinerja menurun dengan cepat saat jumlah baris meningkat.

Parameter bernilai-tabel memiliki kinerja yang sama baiknya atau lebih baik daripada implementasi array parameter yang setara.

Contoh

Contoh berikut menggunakan Transact-SQL dan menunjukkan kepada Anda cara membuat jenis parameter bernilai tabel, mendeklarasikan variabel untuk mereferensikannya, mengisi daftar parameter, lalu meneruskan nilai ke prosedur tersimpan.

USE AdventureWorks2012;  
GO  
  
/* Create a table type. */  
CREATE TYPE LocationTableType AS TABLE   
( LocationName VARCHAR(50)  
, CostRate INT );  
GO  
  
/* Create a procedure to receive data for the table-valued parameter. */  
CREATE PROCEDURE dbo. usp_InsertProductionLocation  
    @TVP LocationTableType READONLY  
    AS   
    SET NOCOUNT ON  
    INSERT INTO AdventureWorks2012.Production.Location  
           (Name  
           ,CostRate  
           ,Availability  
           ,ModifiedDate)  
        SELECT *, 0, GETDATE()  
        FROM  @TVP;  
        GO  
  
/* Declare a variable that references the type. */  
DECLARE @LocationTVP AS LocationTableType;  
  
/* Add data to the table variable. */  
INSERT INTO @LocationTVP (LocationName, CostRate)  
    SELECT Name, 0.00  
    FROM AdventureWorks2012.Person.StateProvince;  
  
/* Pass the table variable data to a stored procedure. */  
EXEC usp_InsertProductionLocation @LocationTVP;  
GO  

Lihat Juga

CREATE TYPE (Transact-SQL)
DECLARE @local_variable (Transact-SQL)
sys.type (Transact-SQL)
sys.parameters (Transact-SQL)
sys.parameter_type_usages (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
CREATE FUNCTION (Transact-SQL)