Gunakan Parameter Bernilai Tabel (Mesin Database)

  • Artikel

Berlaku untuk: SQL Server Azure SQL DatabaseNot supported.Azure Synapse Analytics Analytics Not supported. Platform System (PDW)

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.

Dalam Topik ini:

Keuntungan

Pembatasan

Parameter Bernilai Tabel vs. Operasi INSERT 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.
  • Di-cache seperti tabel sementara saat digunakan dalam prosedur tersimpan. Dimulai dengan SQL Server 2012 (11.x), 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 klausul FROM SELECT INTO atau dalam string INSERT EXEC 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 1000 baris.

Parameter bernilai tabel yang digunakan kembali mendapat manfaat dari penembolokan tabel sementara. Penembolokan tabel ini memungkinkan skalabilitas yang lebih baik daripada operasi INSERT MASSAL yang setara. Dengan menggunakan operasi penyisipan baris kecil, manfaat performa kecil mungkin diperoleh dengan menggunakan daftar parameter atau pernyataan batch, bukan operasi INSERT MASSAL 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 di database 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;

Lihat Juga