POTONG TABEL (Transact-SQL)

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)

Menghapus semua baris dari tabel atau partisi tabel tertentu, tanpa mencatat penghapusan baris individual. TRUNCATE TABLE mirip DELETE dengan pernyataan tanpa WHERE klausul; namun, TRUNCATE TABLE lebih cepat dan menggunakan lebih sedikit sistem dan sumber daya log transaksi.

Konvensi sintaks transact-SQL

Sintaks

Sintaks untuk SQL Server dan Azure SQL Database.

TRUNCATE TABLE
    { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }
    [ , ...n ] ) ) ]
[ ; ]

<range> ::=
<partition_number_expression> TO <partition_number_expression>

Sintaks untuk Azure Synapse Analytics dan Gudang Data Paralel.

TRUNCATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ; ]

Catatan

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

Argumen

database_name

Nama database.

schema_name

Nama skema tempat tabel berada.

table_name

Nama tabel yang akan dipotong atau dari mana semua baris dihapus. table_name harus harfiah. table_name tidak boleh fungsi OBJECT_ID() atau variabel.

WITH ( PARTISI ( { <partition_number_expression | <>range> } [ , ... n ] ) )

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru.

Menentukan partisi yang akan dipotong atau dari mana semua baris dihapus. Jika tabel tidak dipartisi, WITH PARTITIONS argumen akan menghasilkan kesalahan. WITH PARTITIONS Jika klausa tidak disediakan, seluruh tabel dipotong.

<partition_number_expression> dapat ditentukan dengan cara berikut:

  • Berikan jumlah partisi, misalnya: WITH (PARTITIONS (2))

  • Berikan nomor partisi untuk beberapa partisi individual yang dipisahkan oleh koma, misalnya: WITH (PARTITIONS (1, 5))

  • Berikan rentang dan partisi individual, misalnya: WITH (PARTITIONS (2, 4, 6 TO 8))

  • <range> dapat ditentukan sebagai nomor partisi yang dipisahkan oleh kata TO, misalnya: WITH (PARTITIONS (6 TO 8))

Untuk memotong tabel yang dipartisi, tabel dan indeks harus diratakan (dipartisi pada fungsi partisi yang sama).

Keterangan

Dibandingkan DELETE dengan pernyataan, TRUNCATE TABLE memiliki keuntungan berikut:

  • Ruang log transaksi yang digunakan lebih sedikit.

    Pernyataan DELETE menghapus baris satu per satu dan merekam entri dalam log transaksi untuk setiap baris yang dihapus. TRUNCATE TABLE menghapus data dengan membatalkan alokasi halaman data yang digunakan untuk menyimpan data tabel dan hanya merekam alokasi halaman dalam log transaksi.

  • Kunci yang digunakan biasanya lebih sedikit.

    DELETE Saat pernyataan dijalankan menggunakan kunci baris, setiap baris dalam tabel dikunci untuk dihapus. TRUNCATE TABLE selalu mengunci tabel (termasuk kunci skema (SCH-M) dan halaman, tetapi tidak setiap baris.

  • Tanpa pengecualian, nol halaman dibiarkan dalam tabel.

    DELETE Setelah pernyataan dijalankan, tabel masih dapat berisi halaman kosong. Misalnya, halaman kosong dalam timbunan tidak dapat dibatalkan alokasinya tanpa setidaknya kunci tabel eksklusif (LCK_M_X). Jika operasi penghapusan tidak menggunakan kunci tabel, tabel (tumpukan) akan berisi banyak halaman kosong. Untuk indeks, operasi penghapusan dapat meninggalkan halaman kosong, meskipun proses pembersihan latar belakang membatalkan alokasi halaman ini dengan cepat.

TRUNCATE TABLE menghapus semua baris dari tabel, tetapi struktur tabel dan kolomnya, batasan, indeks, dan sebagainya, tetap ada. Untuk menghapus definisi tabel selain datanya, gunakan DROP TABLE pernyataan .

Jika tabel berisi kolom identitas, penghitung untuk kolom tersebut diatur ulang ke nilai awal yang ditentukan untuk kolom. Jika tidak ada seed yang ditentukan, nilai 1 default akan digunakan. Untuk mempertahankan penghitung identitas, gunakan DELETE sebagai gantinya.

TRUNCATE TABLE Operasi dapat digulung balik dalam transaksi.

Batasan

Anda tidak dapat menggunakan TRUNCATE TABLE pada tabel yang:

  • Dirujuk oleh FOREIGN KEY batasan. Anda dapat memotong tabel yang memiliki kunci asing yang merujuk dirinya sendiri.

  • Berpartisipasi dalam tampilan terindeks.

  • Diterbitkan dengan menggunakan replikasi transaksional atau replikasi penggabungan.

  • Berversi sistem sementara.

  • Dirujuk oleh EDGE batasan.

Untuk tabel dengan satu atau beberapa karakteristik ini, gunakan pernyataan sebagai gantinya DELETE .

TRUNCATE TABLE tidak dapat mengaktifkan pemicu karena operasi tidak mencatat penghapusan baris individual. Untuk informasi selengkapnya, lihat CREATE TRIGGER (Transact-SQL).

Di Azure Synapse Analytics and Analytics Platform System (PDW):

  • TRUNCATE TABLE tidak diizinkan dalam EXPLAIN pernyataan.

  • TRUNCATE TABLE tidak dapat dijalankan di dalam transaksi.

Memotong tabel besar

Microsoft SQL Server memiliki kemampuan untuk menghilangkan atau memotong tabel yang memiliki lebih dari 128 jangkauan tanpa memegang kunci simultan pada semua tingkat yang diperlukan untuk penghilangan.

Izin

Izin minimum yang diperlukan ada ALTER di table_name. TRUNCATE TABLE izin default ke pemilik tabel, anggota peran server tetap sysadmin , dan db_owner peran database tetap dan db_ddladmin , dan tidak dapat ditransfer. Namun, Anda dapat memasukkan pernyataan TRUNCATE TABLE di dalam modul, seperti prosedur tersimpan, dan memberikan izin yang sesuai ke modul menggunakan klausul EXECUTE AS.

Contoh

J. Memotong tabel

Contoh berikut menghapus semua data dari JobCandidate tabel. SELECT pernyataan disertakan sebelum dan sesudah pernyataan untuk membandingkan TRUNCATE TABLE hasil.

USE AdventureWorks2022;
GO

SELECT COUNT(*) AS BeforeTruncateCount
FROM HumanResources.JobCandidate;
GO

TRUNCATE TABLE HumanResources.JobCandidate;
GO

SELECT COUNT(*) AS AfterTruncateCount
FROM HumanResources.JobCandidate;
GO

B. Memotong partisi tabel

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru.

Contoh berikut memotong partisi tertentu dari tabel yang dipartisi. Sintaks WITH (PARTITIONS (2, 4, 6 TO 8)) menyebabkan nomor partisi 2, 4, 6, 7, dan 8 dipotong.

TRUNCATE TABLE PartitionTable1
WITH (PARTITIONS (2, 4, 6 TO 8));
GO

C. Mengembalikan operasi pemotongan

Contoh berikut menunjukkan bahwa TRUNCATE TABLE operasi di dalam transaksi dapat digulung balik.

  1. Buat tabel pengujian dengan tiga baris.

    USE [tempdb];
GO
CREATE TABLE TruncateTest (ID INT IDENTITY (1, 1) NOT NULL);
GO
INSERT INTO TruncateTest DEFAULT VALUES;
GO 3

  2. Periksa data sebelum memotong.

    SELECT * FROM TruncateTest;
GO

  3. Potong tabel dalam transaksi, dan periksa jumlah baris.

    BEGIN TRANSACTION;

TRUNCATE TABLE TruncateTest;

SELECT * FROM TruncateTest;

    Anda melihat bahwa tabel kosong.

  4. Gulung balik transaksi dan periksa data.

    ROLLBACK TRANSACTION;
GO

SELECT * FROM TruncateTest;
GO

    Anda melihat ketiga baris.

  5. Bersihkan meja.

    DROP TABLE TruncateTest;
GO

Konten terkait