table (Transact-SQL)

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

tabel adalah jenis data khusus yang digunakan untuk menyimpan tataan hasil untuk diproses di lain waktu. tabel terutama digunakan untuk menyimpan sekumpulan baris yang dikembalikan sementara sebagai kumpulan hasil fungsi bernilai tabel. Fungsi dan variabel dapat dinyatakan berjenis tabel. variabel tabel dapat digunakan dalam fungsi, prosedur tersimpan, dan batch. Untuk mendeklarasikan variabel tabel jenis, gunakan DECLARE @local_variable.

Konvensi sintaks transact-SQL

Sintaksis

table_type_definition ::=
    TABLE ( { <column_definition> | <table_constraint> } [ , ...n ] )

<column_definition> ::=
    column_name scalar_data_type
    [ COLLATE <collation_definition> ]
    [ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ]
    [ ROWGUIDCOL ]
    [ column_constraint ] [ ...n ]

 <column_constraint> ::=
    { [ NULL | NOT NULL ]
    | [ PRIMARY KEY | UNIQUE ]
    | CHECK ( logical_expression )
    }

<table_constraint> ::=
     { { PRIMARY KEY | UNIQUE } ( column_name [ , ...n ] )
     | CHECK ( logical_expression )
     }

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

table_type_definition

Subset informasi yang sama yang digunakan untuk menentukan tabel di CREATE TABLE. Deklarasi tabel mencakup definisi kolom, nama, jenis data, dan batasan. Satu-satunya jenis batasan yang diizinkan adalah KUNCI PRIMER, KUNCI UNIK, dan NULL.

Untuk informasi selengkapnya tentang sintaks, lihat CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL), dan DECLARE @local_variable (Transact-SQL).

collation_definition

Kolase kolom yang terdiri dari lokal Microsoft Windows dan gaya perbandingan, lokal Windows, dan notasi biner, atau kolase Microsoft SQL Server. Jika collation_definition tidak ditentukan, kolom mewarisi kolase database saat ini. Atau jika kolom didefinisikan sebagai jenis yang ditentukan pengguna runtime bahasa umum (CLR), kolom mewarisi kolase jenis yang ditentukan pengguna.

Keterangan

tabel Variabel referensi menurut nama dalam klausa FROM batch, seperti yang diperlihatkan contoh berikut:

SELECT Employee_ID, Department_ID FROM @MyTableVar;

Di luar klausa FROM, variabel tabel harus dirujuk dengan menggunakan alias, seperti yang ditunjukkan dalam contoh berikut:

SELECT EmployeeID,
    DepartmentID
FROM @MyTableVar m
INNER JOIN Employee
    ON m.EmployeeID = Employee.EmployeeID
    AND m.DepartmentID = Employee.DepartmentID;

variabel tabel memberikan manfaat berikut daripada tabel sementara untuk kueri skala kecil yang memiliki rencana kueri yang tidak berubah dan ketika masalah kompilasi ulang dominan:

• Variabel tabel berulah seperti variabel lokal. Ini memiliki cakupan yang terdefinisi dengan baik. Variabel ini dapat digunakan dalam fungsi, prosedur tersimpan, atau batch tempat variabel dideklarasikan.

  Dalam cakupannya, variabel tabel dapat digunakan seperti tabel biasa. Ini dapat diterapkan di mana saja ekspresi tabel atau tabel digunakan dalam pernyataan SELECT, INSERT, UPDATE, dan DELETE. Namun, tabel tidak dapat digunakan dalam pernyataan berikut:

SELECT select_list INTO table_variable;

variabel tabel secara otomatis dibersihkan di akhir fungsi, prosedur tersimpan, atau batch tempat variabel didefinisikan.

• variabel tabel yang digunakan dalam prosedur tersimpan menyebabkan lebih sedikit kompilasi ulang prosedur tersimpan daripada ketika tabel sementara digunakan ketika tidak ada pilihan berbasis biaya yang memengaruhi performa.

  Variabel tabel sepenuhnya terisolasi ke batch yang membuatnya sehingga tidak ada resolusi ulang yang harus terjadi ketika pernyataan CREATE atau ALTER terjadi, yang mungkin terjadi dengan tabel sementara. Tabel sementara memerlukan resolusi ulang ini sehingga tabel dapat direferensikan dari prosedur tersimpan berlapis. Variabel tabel menghindari langkah ini sepenuhnya, sehingga prosedur tersimpan dapat menggunakan rencana yang sudah dikompilasi, sehingga menghemat sumber daya untuk memproses prosedur tersimpan.

• Transaksi yang melibatkan variabel tabel hanya berlangsung selama durasi pembaruan pada variabel tabel . Dengan demikian, variabel tabel memerlukan lebih sedikit sumber daya penguncian dan pengelogan.

Pembatasan dan batasan

variabel tabel tidak memiliki statistik distribusi. Mereka tidak memicu kompilasi ulang. Dalam banyak kasus, pengoptimal membangun rencana kueri dengan asumsi bahwa variabel tabel tidak memiliki baris. Untuk alasan ini, Anda harus berhati-hati menggunakan variabel tabel jika Anda mengharapkan jumlah baris yang lebih besar (lebih dari 100). Tabel sementara mungkin merupakan solusi yang lebih baik dalam hal ini. Untuk kueri yang menggabungkan variabel tabel dengan tabel lain, gunakan petunjuk RECOMPILE, yang menyebabkan pengoptimal menggunakan kardinalitas yang benar untuk variabel tabel.

variabel tabel tidak didukung dalam model penalaran berbasis biaya pengoptimal SQL Server. Dengan demikian, pilihan tersebut tidak boleh digunakan saat pilihan berbasis biaya diperlukan untuk mencapai rencana kueri yang efisien. Tabel sementara lebih disukai ketika pilihan berbasis biaya diperlukan. Paket ini biasanya mencakup kueri dengan gabungan, keputusan paralelisme, dan pilihan pemilihan indeks.

Kueri yang mengubah variabel tabel tidak menghasilkan rencana eksekusi kueri paralel. Performa dapat terpengaruh ketika variabel tabel besar, atau variabel tabel dalam kueri kompleks, dimodifikasi. Pertimbangkan untuk menggunakan tabel sementara sebagai gantinya dalam situasi di mana variabel tabel dimodifikasi. Untuk informasi selengkapnya, lihat CREATE TABLE (Transact-SQL). Kueri yang membaca variabel tabel tanpa memodifikasinya masih dapat diparalelkan.

Penting

Tingkat kompatibilitas database 150 meningkatkan performa variabel tabel dengan pengenalan kompilasi yang ditangguhkan variabel tabel. Untuk informasi selengkapnya, lihat Kompilasi yang ditangguhkan variabel tabel.

Indeks tidak dapat dibuat secara eksplisit pada variabel tabel , dan tidak ada statistik yang disimpan pada variabel tabel . Dimulai dengan SQL Server 2014 (12.x), sintaks baru diperkenalkan yang memungkinkan Anda membuat jenis indeks tertentu sebaris dengan definisi tabel. Dengan menggunakan sintaks baru ini, Anda dapat membuat indeks pada variabel tabel sebagai bagian dari definisi tabel. Dalam beberapa kasus, performa dapat meningkat dengan menggunakan tabel sementara sebagai gantinya, yang memberikan dukungan dan statistik indeks penuh. Untuk informasi selengkapnya tentang tabel sementara dan pembuatan indeks sebaris, lihat CREATE TABLE (Transact-SQL).

Batasan CHECK, nilai DEFAULT, dan kolom komputasi dalam deklarasi jenis tabel tidak dapat memanggil fungsi yang ditentukan pengguna. Operasi penugasan antara variabel tabel tidak didukung. Karena variabel tabel memiliki cakupan terbatas dan bukan bagian dari database persisten, pembatalan transaksi tidak memengaruhinya. Variabel tabel tidak dapat diubah setelah pembuatan.

Variabel tabel tidak dapat digunakan dalam pernyataan INSERT EXEC atau SELECT INTO.

Anda tidak dapat menggunakan pernyataan EXEC atau sp_executesql prosedur tersimpan untuk menjalankan kueri SQL Server dinamis yang merujuk variabel tabel, jika variabel tabel dibuat di luar pernyataan EXEC atau prosedur tersimpan sp_executesql . Karena variabel tabel hanya dapat dirujuk dalam cakupan lokalnya, pernyataan EXEC dan prosedur tersimpan sp_executesql akan berada di luar cakupan variabel tabel. Namun, Anda dapat membuat variabel tabel dan melakukan semua pemrosesan di dalam pernyataan EXEC atau sp_executesql prosedur tersimpan karena kemudian cakupan lokal variabel tabel ada dalam pernyataan EXEC atau prosedur tersimpan sp_executesql .

Variabel tabel bukan struktur khusus memori. Karena variabel tabel mungkin menyimpan lebih banyak data daripada yang dapat pas dalam memori, variabel harus memiliki tempat pada disk untuk menyimpan data. Variabel tabel dibuat dalam database yang tempdb mirip dengan tabel sementara. Jika memori tersedia, variabel tabel dan tabel sementara dibuat dan diproses saat dalam memori (cache data).

Variabel tabel vs tabel sementara

Memilih antara variabel tabel dan tabel sementara tergantung pada faktor-faktor berikut:

• Jumlah baris yang disisipkan ke tabel.
• Jumlah kompilasi ulang tempat kueri disimpan.
• Jenis kueri dan dependensinya pada indeks dan statistik untuk performa.

Dalam beberapa situasi, melanggar prosedur tersimpan dengan tabel sementara menjadi prosedur tersimpan yang lebih kecil sehingga kompilasi ulang terjadi pada unit yang lebih kecil sangat membantu.

Secara umum, Anda menggunakan variabel tabel jika memungkinkan kecuali ketika ada volume data yang signifikan dan ada penggunaan tabel berulang. Dalam hal ini, Anda dapat membuat indeks pada tabel sementara untuk meningkatkan performa kueri. Namun, setiap skenario mungkin berbeda. Microsoft menyarankan agar Anda menguji apakah variabel tabel lebih membantu daripada tabel sementara untuk kueri tertentu atau prosedur tersimpan.

Contoh

J. Mendeklarasikan variabel tabel tipe

Contoh berikut membuat variabel tabel yang menyimpan nilai yang ditentukan dalam klausa OUTPUT pernyataan UPDATE. Dua SELECT pernyataan mengikuti, yang mengembalikan nilai di @MyTableVar dan hasil operasi pembaruan dalam Employee tabel. Hasil dalam INSERTED.ModifiedDate kolom berbeda dari nilai dalam ModifiedDate kolom dalam Employee tabel. Perbedaan ini karena pemicu AFTER UPDATE , yang memperbarui nilai ModifiedDate ke tanggal saat ini, ditentukan pada Employee tabel. Namun, kolom yang dikembalikan dari OUTPUT mencerminkan data sebelum pemicu diaktifkan. Untuk informasi selengkapnya, lihat Klausul OUTPUT (Transact-SQL).

USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    OldVacationHours INT,
    NewVacationHours INT,
    ModifiedDate DATETIME
);

UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.BusinessEntityID,
    DELETED.VacationHours,
    INSERTED.VacationHours,
    INSERTED.ModifiedDate
INTO @MyTableVar;

--Display the result set of the table variable.
SELECT EmpID,
    OldVacationHours,
    NewVacationHours,
    ModifiedDate
FROM @MyTableVar;
GO

--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID,
    VacationHours,
    ModifiedDate
FROM HumanResources.Employee;
GO

B. Membuat fungsi bernilai tabel sebaris

Contoh berikut mengembalikan fungsi bernilai tabel sebaris. Ini mengembalikan tiga kolom ProductID, Name, dan agregat total tahun ke tanggal berdasarkan penyimpanan untuk YTD Total setiap produk yang dijual ke toko.

USE AdventureWorks2022;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
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
    INNER JOIN Sales.SalesOrderDetail AS SD
        ON SD.ProductID = P.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SH
        ON SH.SalesOrderID = SD.SalesOrderID
    INNER 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);

Baca juga

• COLLATE (Transact-SQL)
• CREATE FUNCTION (Transact-SQL)
• Fungsi yang Ditentukan Pengguna
• BUAT TABEL (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• Gunakan Parameter Bernilai Tabel (Mesin Database)
• Petunjuk Kueri (T-SQL)