Bagikan melalui

Menggunakan parameter bernilai tabel (Mesin Database)

  • Artikel

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

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 dapat berpartisipasi dalam operasi berbasis set.

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.

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.
  • Di-cache seperti tabel sementara saat digunakan dalam prosedur tersimpan. Dimulai dengan SQL Server 2012 (11.x) dan versi yang lebih baru, parameter bernilai tabel juga di-cache untuk kueri berparameter.

Izin

Untuk membuat instans jenis tabel yang ditentukan pengguna, atau memanggil prosedur tersimpan dengan parameter bernilai tabel, pengguna harus memiliki izin EXECUTE dan REFERENCES pada jenis, atau pada skema atau database yang berisi jenis tersebut.

Batasan

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 FROM klausul SELECT INTO atau dalam INSERT EXEC string atau prosedur tersimpan.

Parameter bernilai tabel 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 1.000 baris.

Parameter bernilai tabel yang digunakan kembali mendapat manfaat dari penembolokan tabel sementara. Penembolokan tabel ini memungkinkan skalabilitas yang lebih baik daripada operasi penyisipan massal yang setara. Operasi penyisipan baris kecil mungkin memberikan manfaat performa kecil dengan menggunakan daftar parameter atau pernyataan batch, alih-alih BULK INSERT operasi atau parameter bernilai tabel. Namun, metode ini kurang nyaman untuk diprogram, dan performa menurun dengan cepat saat baris meningkat.

Parameter bernilai tabel berkinerja 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 dalam database sampel AdventureWorks .

/* 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 AdventureWorks2022.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 AdventureWorks2022.Person.StateProvince;
  
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;

Hasil yang diharapkan adalah:

(181 rows affected)

Konten terkait