tabel (Transact-SQL)

Artikel
07/20/2022
5 menit untuk membaca

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance

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

Berlaku untuk: SQL Server (SQL Server 2008 dan yang lebih baru), Azure SQL Database.

Topic link icon Konvensi Sintaksis T-SQL

Sintaks

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 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

table_type_definition
Adalah 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
Adalah 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

Meja Variabel referensi menurut nama dalam klausa FROM batch, seperti yang ditunjukkan contoh berikut:

SELECT Employee_ID, Department_ID FROM @MyTableVar;

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

SELECT EmployeeID, DepartmentID   
FROM @MyTableVar m  
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND  
   m.DepartmentID = Employee.DepartmentID);

variabel tabel memberikan manfaat berikut 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 dinyatakan.
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.
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 akan memicu kompilasi ulang. Dalam banyak kasus, pengoptimal akan membangun rencana kueri dengan asumsi bahwa variabel tabel tidak memiliki baris. Untuk alasan ini, Anda harus berhati-hati dalam 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 akan menyebabkan pengoptimal menggunakan kardinalitas yang benar untuk variabel tabel.

variabel tabel tidak didukung dalam model penalaran berbasis biaya pengoptimal SQL Server. Dengan demikian, mereka tidak boleh digunakan ketika pilihan berbasis biaya diperlukan untuk mencapai rencana kueri yang efisien. Tabel sementara lebih disukai ketika pilihan berbasis biaya diperlukan. Rencana 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 penetapan antar 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.

Contoh

A. Mendeklarasikan variabel tabel tipe

Contoh berikut membuat table variabel yang menyimpan nilai yang ditentukan dalam klausa OUTPUT dari 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 AdventureWorks2012;  
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 toko seperti YTD Total untuk setiap produk yang dijual ke toko.

USE AdventureWorks2012;  
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   
    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);

Lihat juga

COLLATE (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
Fungsi yang Ditentukan Pengguna
BUAT TABEL (Transact-SQL)
DECLARE @local_variable (Transact-SQL)
Menggunakan Parameter Table-Valued (Mesin Database)
Petunjuk Kueri (T-SQL)