Petunjuk (Transact-SQL) - Tabel

  • Artikel
  • 22 menit untuk membaca

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

Petunjuk tabel mengambil alih perilaku default Pengoptimal Kueri selama durasi pernyataan bahasa manipulasi data (DML) dengan menentukan metode penguncian, satu atau beberapa indeks, operasi pemrosesan kueri seperti pemindaian tabel atau pencarian indeks, atau opsi lainnya. Petunjuk tabel ditentukan dalam klausa FROM dari pernyataan DML dan hanya memengaruhi tabel atau tampilan yang dirujuk dalam klausa tersebut.

Perhatian

Karena SQL Server Pengoptimal Kueri biasanya memilih rencana eksekusi terbaik untuk kueri, kami menyarankan agar petunjuk hanya digunakan sebagai upaya terakhir oleh pengembang dan administrator database berpengalaman.

Berlaku untuk:

DELETE

INSERT

PILIH

UPDATE

MERGE

Ikon tautan topikKonvensi Sintaks Transact-SQL

Sintaks

WITH  ( <table_hint> [ [, ]...n ] )  
  
<table_hint> ::=   
{ NOEXPAND [ , INDEX ( <index_value> [ ,...n ] ) | INDEX = ( <index_value> ) ]  
  | INDEX ( <index_value> [ ,...n ] ) | INDEX = ( <index_value> )
  | FORCESEEK [ ( <index_value> ( <index_column_name> [,... ] ) ) ] 
  | FORCESCAN  
  | FORCESEEK  
  | HOLDLOCK   
  | NOLOCK   
  | NOWAIT  
  | PAGLOCK   
  | READCOMMITTED   
  | READCOMMITTEDLOCK   
  | READPAST   
  | READUNCOMMITTED   
  | REPEATABLEREAD   
  | ROWLOCK   
  | SERIALIZABLE   
  | SNAPSHOT   
  | SPATIAL_WINDOW_MAX_CELLS = <integer_value>  
  | TABLOCK   
  | TABLOCKX   
  | UPDLOCK   
  | XLOCK   
}   
  
<table_hint_limited> ::=  
{  
    KEEPIDENTITY   
  | KEEPDEFAULTS   
  | HOLDLOCK   
  | IGNORE_CONSTRAINTS   
  | IGNORE_TRIGGERS   
  | NOLOCK   
  | NOWAIT  
  | PAGLOCK   
  | READCOMMITTED   
  | READCOMMITTEDLOCK   
  | READPAST   
  | REPEATABLEREAD   
  | ROWLOCK   
  | SERIALIZABLE   
  | SNAPSHOT   
  | TABLOCK   
  | TABLOCKX   
  | UPDLOCK   
  | XLOCK   
}

Catatan

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

Argumen

WITH (<table_hint>) [ [, ]... n ]
Dengan beberapa pengecualian, petunjuk tabel didukung dalam klausul FROM hanya ketika petunjuk ditentukan dengan kata kunci WITH. Petunjuk tabel juga harus ditentukan dengan tanda kurung.

Penting

Menghilangkan kata kunci WITH adalah fitur yang tidak digunakan lagi: Fitur ini akan dihapus dalam versi Microsoft SQL Server mendatang. Hindari menggunakan fitur ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan fitur ini.

Petunjuk tabel berikut diizinkan dengan dan tanpa WITH kata kunci: NOLOCK, , , READUNCOMMITTEDUPDLOCK, REPEATABLEREADSERIALIZABLE, READCOMMITTED, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, , XLOCK, , SNAPSHOT, dan NOEXPAND. Ketika petunjuk tabel ini ditentukan tanpa kata kunci WITH, petunjuk harus ditentukan sendiri. Contohnya:

FROM t (TABLOCK)

Ketika petunjuk ditentukan dengan opsi lain, petunjuk harus ditentukan dengan kata kunci WITH:

FROM t WITH (TABLOCK, INDEX(myindex))

Sebaiknya gunakan koma di antara petunjuk tabel.

Penting

Memisahkan petunjuk berdasarkan spasi daripada koma adalah fitur yang tidak digunakan lagi: Fitur ini akan dihapus dalam versi Microsoft SQL Server di masa mendatang. Hindari menggunakan fitur ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan fitur ini.

NOEXPAND
Menentukan bahwa setiap tampilan terindeks tidak diperluas untuk mengakses tabel yang mendasar saat pengoptimal kueri memproses kueri. Pengoptimal kueri memperlakukan tampilan seperti tabel dengan indeks berkluster. NOEXPAND hanya berlaku untuk tampilan terindeks. Untuk informasi selengkapnya, lihat Menggunakan NOEXPAND.

INDEX (<index_value> [,... n ] ) | INDEX = ( <index_value>)
Sintaks INDEX() menentukan nama atau ID dari satu atau beberapa indeks yang akan digunakan oleh pengoptimal kueri saat memproses pernyataan. Sintaks alternatif INDEX = menentukan nilai indeks tunggal. Hanya satu petunjuk indeks per tabel yang dapat ditentukan.

Jika indeks berkluster ada, INDEX(0) memaksa pemindaian indeks berkluster dan INDEX(1) memaksa pemindaian atau pencarian indeks berkluster. Jika tidak ada indeks berkluster, INDEX(0) paksa pemindaian tabel dan INDEX(1) ditafsirkan sebagai kesalahan.

Jika beberapa indeks digunakan dalam satu daftar petunjuk, duplikat diabaikan dan sisa indeks yang tercantum digunakan untuk mengambil baris tabel. Urutan indeks dalam petunjuk indeks signifikan. Petunjuk beberapa indeks juga memberlakukan ANDing indeks, dan pengoptimal kueri menerapkan kondisi sebanyak mungkin pada setiap indeks yang diakses. Jika kumpulan indeks yang diisyaratkan tidak menyertakan semua kolom yang direferensikan oleh kueri, pengambilan dilakukan untuk mengambil kolom yang tersisa setelah mesin database SQL Server mengambil semua kolom terindeks.

Catatan

Ketika petunjuk indeks yang mengacu pada beberapa indeks digunakan pada tabel fakta dalam gabungan bintang, pengoptimal mengabaikan petunjuk indeks dan mengembalikan pesan peringatan. Selain itu, indeks ORing tidak diizinkan untuk tabel dengan petunjuk indeks yang ditentukan.

Jumlah maksimum indeks dalam petunjuk tabel adalah 250 indeks nonkluster.

KEEPIDENTITY
Hanya berlaku dalam pernyataan INSERT ketika opsi BULK digunakan dengan OPENROWSET.

Menentukan bahwa nilai identitas atau nilai dalam file data yang diimpor akan digunakan untuk kolom identitas. Jika KEEPIDENTITY tidak ditentukan, nilai identitas untuk kolom ini diverifikasi tetapi tidak diimpor dan pengoptimal kueri secara otomatis menetapkan nilai unik berdasarkan nilai benih dan kenaikan yang ditentukan selama pembuatan tabel.

Penting

Jika file data tidak berisi nilai untuk kolom identitas dalam tabel atau tampilan, dan kolom identitas bukan kolom terakhir dalam tabel, Anda harus melewati kolom identitas. Untuk informasi selengkapnya, lihat Menggunakan File Format untuk Melewati Bidang Data (SQL Server). Jika kolom identitas berhasil dilewati, pengoptimal kueri secara otomatis menetapkan nilai unik untuk kolom identitas ke dalam baris tabel yang diimpor.

Untuk contoh yang menggunakan petunjuk ini dalam INSERT ... SELECT * FROM OPENROWSET(BULK...) pernyataan, lihat Menyimpan Nilai Identitas Saat Mengimpor Data Secara Massal (SQL Server).

Untuk informasi tentang memeriksa nilai identitas untuk tabel, lihat DBCC CHECKIDENT (Transact-SQL).

KEEPDEFAULTS
Hanya berlaku dalam pernyataan INSERT ketika opsi BULK digunakan dengan OPENROWSET.

Menentukan penyisipan nilai default kolom tabel, jika ada, alih-alih NULL saat rekaman data tidak memiliki nilai untuk kolom.

Misalnya yang menggunakan petunjuk ini dalam INSERT ... PERNYATAAN SELECT * FROM OPENROWSET(BULK...), lihat Pertahankan Null atau Gunakan Nilai Default Selama Impor Massal (SQL Server).

FORCESEEK [ (<index_value>(<index_column_name> [ , ...n ] )) ]
Menentukan bahwa pengoptimal kueri hanya menggunakan operasi pencarian indeks sebagai jalur akses ke data dalam tabel atau tampilan.

Catatan

Dimulai dengan SQL Server 2008 R2 SP1, parameter indeks juga dapat ditentukan. Dalam hal ini, pengoptimal kueri hanya mempertimbangkan operasi pencarian indeks melalui indeks yang ditentukan menggunakan setidaknya kolom indeks yang ditentukan.

index_value
Adalah nama indeks atau nilai ID indeks. ID indeks 0 (tumpukan) tidak dapat ditentukan. Untuk mengembalikan nama indeks atau ID, kueri tampilan katalog sys.indexes .

index_column_name
Adalah nama kolom indeks yang akan disertakan dalam operasi pencarian. Menentukan FORCESEEK dengan parameter indeks mirip dengan menggunakan FORCESEEK dengan petunjuk INDEX. Namun, Anda dapat mencapai kontrol yang lebih besar atas jalur akses yang digunakan oleh pengoptimal kueri dengan menentukan indeks yang akan dicari dan kolom indeks untuk dipertimbangkan dalam operasi pencarian. Pengoptimal dapat mempertimbangkan kolom tambahan jika diperlukan. Misalnya, jika indeks non-kluster ditentukan, pengoptimal dapat memilih untuk menggunakan kolom kunci indeks berkluster selain kolom yang ditentukan.

Petunjuk FORCESEEK dapat ditentukan dengan cara berikut.

Sintaks Contoh Deskripsi
Tanpa petunjuk indeks atau INDEKS FROM dbo.MyTable WITH (FORCESEEK) Pengoptimal kueri hanya mempertimbangkan operasi pencarian indeks untuk mengakses tabel atau melihat melalui indeks yang relevan.
Dikombinasikan dengan petunjuk INDEX FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex)) Pengoptimal kueri hanya mempertimbangkan operasi pencarian indeks untuk mengakses tabel atau tampilan melalui indeks yang ditentukan.
Diparameterkan dengan menentukan indeks dan kolom indeks FROM dbo.MyTable WITH (FORCESEEK (MyIndex (col1, col2, col3))) Pengoptimal kueri hanya mempertimbangkan operasi pencarian indeks untuk mengakses tabel atau tampilan melalui indeks yang ditentukan menggunakan setidaknya kolom indeks yang ditentukan.

Saat menggunakan petunjuk FORCESEEK (dengan atau tanpa parameter indeks), pertimbangkan panduan berikut:

  • Petunjuk dapat ditentukan sebagai petunjuk tabel atau sebagai petunjuk kueri. Untuk informasi selengkapnya tentang petunjuk kueri, lihat Petunjuk Kueri (Transact-SQL).
  • Untuk menerapkan FORCESEEK ke tampilan terindeks, petunjuk NOEXPAND juga harus ditentukan.
  • Petunjuk dapat diterapkan paling banyak sekali per tabel atau tampilan.
  • Petunjuk tidak dapat ditentukan untuk sumber data jarak jauh. Kesalahan 7377 dikembalikan ketika FORCESEEK ditentukan dengan petunjuk indeks dan kesalahan 8180 dikembalikan ketika FORCESEEK digunakan tanpa petunjuk indeks.
  • Jika FORCESEEK tidak menyebabkan tidak ada rencana yang ditemukan, kesalahan 8622 dikembalikan.

Ketika FORCESEEK ditentukan dengan parameter indeks, panduan dan pembatasan berikut berlaku:

  • Petunjuk tidak dapat ditentukan untuk tabel yang merupakan target pernyataan INSERT, UPDATE, atau DELETE.
  • Petunjuk tidak dapat ditentukan dalam kombinasi dengan petunjuk INDEX atau petunjuk FORCESEEK lainnya.
  • Setidaknya satu kolom harus ditentukan dan harus menjadi kolom kunci utama.
  • Kolom indeks tambahan dapat ditentukan, namun, kolom kunci tidak dapat dilewati. Misalnya, jika indeks yang ditentukan berisi kolom akunci , , bdan c, sintaks yang valid akan menyertakan FORCESEEK (MyIndex (a)) dan FORCESEEK (MyIndex (a, b). Sintaksis yang tidak valid akan mencakup FORCESEEK (MyIndex (c)) dan FORCESEEK (MyIndex (a, c).
  • Urutan nama kolom yang ditentukan dalam petunjuk harus cocok dengan urutan kolom dalam indeks yang dirujuk.
  • Kolom yang tidak ada dalam definisi kunci indeks tidak dapat ditentukan. Misalnya, dalam indeks non-kluster, hanya kolom kunci indeks yang ditentukan yang dapat ditentukan. Kolom kunci berkluster yang secara otomatis disertakan dalam indeks tidak dapat ditentukan, tetapi dapat digunakan oleh pengoptimal.
  • Indeks penyimpan kolom yang dioptimalkan memori xVelocity tidak dapat ditentukan sebagai parameter indeks. Kesalahan 366 dikembalikan.
  • Memodifikasi definisi indeks (misalnya, dengan menambahkan atau menghapus kolom) mungkin memerlukan modifikasi pada kueri yang mereferensikan indeks tersebut.
  • Petunjuk mencegah pengoptimal mempertimbangkan indeks spasial atau XML pada tabel.
  • Petunjuk tidak dapat ditentukan dalam kombinasi dengan petunjuk FORCESCAN.
  • Untuk indeks yang dipartisi, kolom partisi yang secara implisit ditambahkan oleh SQL Server tidak dapat ditentukan dalam petunjuk FORCESEEK.

Perhatian

Menentukan FORCESEEK dengan parameter membatasi jumlah rencana yang dapat dipertimbangkan oleh pengoptimal lebih dari saat menentukan FORCESEEK tanpa parameter. Ini dapat menyebabkan Plan cannot be generated kesalahan terjadi dalam lebih banyak kasus. Dalam rilis mendatang, modifikasi internal pada pengoptimal kueri dapat memungkinkan lebih banyak rencana untuk dipertimbangkan.

FORCESCAN Berlaku untuk: SQL Server 2008 R2 SP1 dan yang lebih baru. Menentukan bahwa pengoptimal kueri hanya menggunakan operasi pemindaian indeks sebagai jalur akses ke tabel atau tampilan yang dirujuk. Petunjuk FORCESCAN dapat berguna untuk kueri di mana pengoptimal meremehkan jumlah baris yang terpengaruh dan memilih operasi pencarian daripada operasi pemindaian. Ketika ini terjadi, jumlah memori yang diberikan untuk operasi terlalu kecil dan performa kueri terpengaruh.

FORCESCAN dapat ditentukan dengan atau tanpa petunjuk INDEKS. Saat dikombinasikan dengan petunjuk indeks, (INDEX = index_name, FORCESCAN), pengoptimal kueri hanya mempertimbangkan jalur akses pemindaian melalui indeks yang ditentukan saat mengakses tabel yang dirujuk. FORCESCAN dapat ditentukan dengan petunjuk indeks INDEX(0) untuk memaksa operasi pemindaian tabel pada tabel dasar.

Untuk tabel dan indeks yang dipartisi, FORCESCAN diterapkan setelah partisi dihilangkan melalui evaluasi predikat kueri. Ini berarti bahwa pemindaian hanya diterapkan ke partisi yang tersisa dan bukan ke seluruh tabel.

Petunjuk FORCESCAN memiliki batasan berikut:

  • Petunjuk tidak dapat ditentukan untuk tabel yang merupakan target pernyataan INSERT, UPDATE, atau DELETE.
  • Petunjuk tidak dapat digunakan dengan lebih dari satu petunjuk indeks.
  • Petunjuk mencegah Pengoptimal Kueri mempertimbangkan indeks spasial atau XML apa pun pada tabel.
  • Petunjuk tidak dapat ditentukan untuk sumber data jarak jauh.
  • Petunjuk tidak dapat ditentukan dalam kombinasi dengan petunjuk FORCESEEK.

HOLDLOCK
Setara dengan SERIALIZABLE. Untuk informasi selengkapnya, lihat SERIALIZABLE nanti dalam topik ini. HOLDLOCK hanya berlaku untuk tabel atau tampilan yang ditentukan dan hanya untuk durasi transaksi yang ditentukan oleh pernyataan tempat transaksi digunakan. HOLDLOCK tidak dapat digunakan dalam pernyataan SELECT yang menyertakan opsi FOR BROWSE.

IGNORE_CONSTRAINTS
Hanya berlaku dalam pernyataan INSERT ketika opsi BULK digunakan dengan OPENROWSET.

Menentukan bahwa batasan apa pun pada tabel diabaikan oleh operasi impor massal. Secara default, INSERT memeriksa Batasan Unik dan Memeriksa Batasan dan Batasan Kunci Utama dan Asing. Ketika IGNORE_CONSTRAINTS ditentukan untuk operasi impor massal, INSERT harus mengabaikan batasan ini pada tabel target. Perhatikan bahwa Anda tidak dapat menonaktifkan batasan UNIQUE, PRIMARY KEY, atau NOT NULL.

Anda mungkin ingin menonaktifkan batasan CHECK dan FOREIGN KEY jika data input berisi baris yang melanggar batasan. Dengan menonaktifkan batasan CHECK dan FOREIGN KEY, Anda dapat mengimpor data lalu menggunakan pernyataan Transact-SQL untuk membersihkan data.

Namun, ketika batasan CHECK dan FOREIGN KEY diabaikan, setiap batasan yang diabaikan pada tabel ditandai sebagai is_not_trusted dalam tampilan katalog sys.check_constraints atau sys.foreign_keys setelah operasi. Pada titik tertentu, Anda harus memeriksa batasan pada seluruh tabel. Jika tabel tidak kosong sebelum operasi impor massal, biaya validasi ulang batasan dapat melebihi biaya penerapan batasan CHECK dan FOREIGN KEY ke data inkremental.

IGNORE_TRIGGERS
Hanya berlaku dalam pernyataan INSERT ketika opsi BULK digunakan dengan OPENROWSET.

Menentukan bahwa setiap pemicu yang ditentukan pada tabel diabaikan oleh operasi impor massal. Secara default, INSERT menerapkan pemicu.

Gunakan IGNORE_TRIGGERS hanya jika aplikasi Anda tidak bergantung pada pemicu apa pun dan memaksimalkan performa adalah penting.

NOLOCK
Setara dengan READUNCOMMITTED. Untuk informasi selengkapnya, lihat READUNCOMMITTED nanti dalam topik ini.

Catatan

Untuk pernyataan UPDATE atau DELETE: Fitur ini akan dihapus dalam versi Microsoft SQL Server mendatang. Hindari menggunakan fitur ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan fitur ini.

SEKARANGAIT
Menginstruksikan Mesin Database untuk mengembalikan pesan segera setelah kunci ditemui di tabel. NOWAIT setara dengan menentukan SET LOCK_TIMEOUT 0 untuk tabel tertentu. Petunjuk NOWAIT tidak berfungsi ketika petunjuk TABLOCK juga disertakan. Untuk mengakhiri kueri tanpa menunggu saat menggunakan petunjuk TABLOCK, awali kueri dengan SET LOCK_TIMEOUT 0; sebagai gantinya.

PAGLOCK
Mengambil kunci halaman baik di mana kunci individual biasanya diambil pada baris atau kunci, atau di mana kunci tabel tunggal biasanya diambil. Secara default, menggunakan mode kunci yang sesuai untuk operasi. Ketika ditentukan dalam transaksi yang beroperasi pada tingkat isolasi SNAPSHOT, kunci halaman tidak diambil kecuali PAGLOCK dikombinasikan dengan petunjuk tabel lain yang memerlukan kunci, seperti UPDLOCK dan HOLDLOCK.

READCOMMITTED
Menentukan bahwa operasi baca mematuhi aturan untuk tingkat isolasi READ COMMITTED dengan menggunakan penguncian atau penerapan versi baris. Jika opsi database READ_COMMITTED_SNAPSHOT NONAKTIF, Mesin Database memperoleh kunci bersama saat data dibaca dan melepaskan kunci tersebut saat operasi baca selesai. Jika opsi database READ_COMMITTED_SNAPSHOT AKTIF, Mesin Database tidak memperoleh kunci dan menggunakan penerapan versi baris. Untuk informasi selengkapnya tentang tingkat isolasi, lihat MENGATUR TINGKAT ISOLASI TRANSAKSI (Transact-SQL).

Catatan

Untuk pernyataan UPDATE atau DELETE: Fitur ini akan dihapus dalam versi Microsoft SQL Server mendatang. Hindari menggunakan fitur ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan fitur ini.

READCOMMITTEDLOCK
Menentukan bahwa operasi baca mematuhi aturan untuk tingkat isolasi READ COMMITTED dengan menggunakan penguncian. Mesin Database memperoleh kunci bersama saat data dibaca dan melepaskan kunci tersebut ketika operasi baca selesai, terlepas dari pengaturan opsi database READ_COMMITTED_SNAPSHOT. Untuk informasi selengkapnya tentang tingkat isolasi, lihat MENGATUR TINGKAT ISOLASI TRANSAKSI (Transact-SQL). Petunjuk ini tidak dapat ditentukan pada tabel target pernyataan INSERT; kesalahan 4140 dikembalikan.

READPAST
Menentukan bahwa Mesin Database tidak membaca baris yang dikunci oleh transaksi lain. Ketika READPAST ditentukan, kunci tingkat baris dilewati tetapi kunci tingkat halaman tidak dilewati. Artinya, Mesin Database melompat melewati baris alih-alih memblokir transaksi saat ini sampai kunci dilepaskan. Misalnya, asumsikan tabel T1 berisi satu kolom bilangan bulat dengan nilai 1, 2, 3, 4, 5. Jika transaksi A mengubah nilai 3 menjadi 8 tetapi belum berkomitmen, SELECT * FROM T1 (READPAST) menghasilkan nilai 1, 2, 4, 5. READPAST terutama digunakan untuk mengurangi ketidakcocokan penguncian saat menerapkan antrean kerja yang menggunakan tabel SQL Server. Pembaca antrean yang menggunakan READPAST melewati entri antrean yang dikunci oleh transaksi lain ke entri antrean berikutnya yang tersedia, tanpa harus menunggu hingga transaksi lain melepaskan kuncinya.

READPAST dapat ditentukan untuk tabel apa pun yang dirujuk dalam pernyataan UPDATE atau DELETE, dan tabel apa pun yang dirujuk dalam klausa FROM. Ketika ditentukan dalam pernyataan UPDATE, READPAST hanya diterapkan saat membaca data untuk mengidentifikasi rekaman mana yang akan diperbarui, terlepas dari di mana dalam pernyataan itu ditentukan. READPAST tidak dapat ditentukan untuk tabel dalam klausa INTO dari pernyataan INSERT. Memperbarui atau menghapus operasi yang menggunakan READPAST dapat memblokir saat membaca kunci asing atau tampilan terindeks, atau saat memodifikasi indeks sekunder.

READPAST hanya dapat ditentukan dalam transaksi yang beroperasi pada tingkat isolasi READ COMMITTED atau REPEATABLE READ. Ketika ditentukan dalam transaksi yang beroperasi pada tingkat isolasi SNAPSHOT, READPAST harus dikombinasikan dengan petunjuk tabel lain yang memerlukan kunci, seperti UPDLOCK dan HOLDLOCK.

Petunjuk tabel READPAST tidak dapat ditentukan saat opsi database READ_COMMITTED_SNAPSHOT diatur ke AKTIF dan salah satu kondisi berikut ini benar:

  • Tingkat isolasi transaksi sesi adalah READ COMMITTED.
  • Petunjuk tabel READCOMMITTED juga ditentukan dalam kueri.

Untuk menentukan petunjuk READPAST dalam kasus ini, hapus petunjuk tabel READCOMMITTED jika ada, dan sertakan petunjuk tabel READCOMMITTEDLOCK dalam kueri.

READUNCOMMITTED
Menentukan bahwa bacaan kotor diperbolehkan. Tidak ada kunci bersama yang dikeluarkan untuk mencegah transaksi lain memodifikasi data yang dibaca oleh transaksi saat ini, dan kunci eksklusif yang ditetapkan oleh transaksi lain tidak memblokir transaksi saat ini membaca data yang terkunci. Mengizinkan pembacaan kotor dapat menyebabkan konkurensi yang lebih tinggi, tetapi dengan biaya membaca modifikasi data yang kemudian digulung balik oleh transaksi lain. Ini dapat menghasilkan kesalahan untuk transaksi Anda, menyajikan data kepada pengguna yang tidak pernah diterapkan, atau menyebabkan pengguna melihat rekaman dua kali (atau tidak sama sekali).

Petunjuk READUNCOMMITTED dan NOLOCK hanya berlaku untuk kunci data. Semua kueri, termasuk yang memiliki petunjuk READUNCOMMITTED dan NOLOCK, memperoleh kunci Sch-S (stabilitas skema) selama kompilasi dan eksekusi. Karena itu, kueri diblokir ketika transaksi bersamaan memegang kunci Sch-M (modifikasi skema) pada tabel. Misalnya, operasi bahasa definisi data (DDL) memperoleh kunci Sch-M sebelum memodifikasi informasi skema tabel. Setiap kueri bersamaan, termasuk yang berjalan dengan petunjuk READUNCOMMITTED atau NOLOCK, diblokir saat mencoba memperoleh kunci Sch-S. Sebaliknya, kueri yang memegang kunci Sch-S memblokir transaksi bersamaan yang mencoba memperoleh kunci Sch-M.

READUNCOMMITTED dan NOLOCK tidak dapat ditentukan untuk tabel yang dimodifikasi dengan operasi sisipkan, perbarui, atau hapus. Pengoptimal kueri SQL Server mengabaikan petunjuk READUNCOMMITTED dan NOLOCK dalam klausa FROM yang berlaku untuk tabel target pernyataan UPDATE atau DELETE.

Catatan

Dukungan untuk penggunaan petunjuk READUNCOMMITTED dan NOLOCK dalam klausa FROM yang berlaku untuk tabel target pernyataan UPDATE atau DELETE akan dihapus dalam versi SQL Server mendatang. Hindari menggunakan petunjuk ini dalam konteks ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakannya.

Anda dapat meminimalkan ketidakcocokan penguncian saat melindungi transaksi dari pembacaan kotor modifikasi data yang tidak dilakukan dengan menggunakan salah satu hal berikut:

  • Tingkat isolasi READ COMMITTED dengan opsi database READ_COMMITTED_SNAPSHOT diatur AKTIF.
  • Tingkat isolasi SNAPSHOT.

Untuk informasi selengkapnya tentang tingkat isolasi, lihat MENGATUR TINGKAT ISOLASI TRANSAKSI (Transact-SQL).

Catatan

Jika Anda menerima pesan kesalahan 601 saat READUNCOMMITTED ditentukan, selesaikan seperti kesalahan kebuntuan (pesan kesalahan 1205), dan coba lagi pernyataan Anda.

REPEATABLEREAD
Menentukan bahwa pemindaian dilakukan dengan semantik penguncian yang sama dengan transaksi yang berjalan pada tingkat isolasi REPEATABLE READ. Untuk informasi selengkapnya tentang tingkat isolasi, lihat MENGATUR TINGKAT ISOLASI TRANSAKSI (Transact-SQL).

ROWLOCK
Menentukan bahwa kunci baris diambil ketika kunci halaman atau tabel biasanya diambil. Ketika ditentukan dalam transaksi yang beroperasi pada tingkat isolasi SNAPSHOT, kunci baris tidak diambil kecuali ROWLOCK dikombinasikan dengan petunjuk tabel lain yang memerlukan kunci, seperti UPDLOCK dan HOLDLOCK. ROWLOCK tidak dapat digunakan dengan tabel yang memiliki indeks penyimpan kolom berkluster. Contoh berikut mengembalikan kesalahan 651 ke aplikasi.

UPDATE [dbo].[FactResellerSalesXL_CCI] WITH (ROWLOCK)
SET UnitPrice = 50
WHERE ProductKey = 150;

SERIALIZABLE
Setara dengan HOLDLOCK. Membuat kunci bersama lebih ketat dengan menahannya hingga transaksi selesai, alih-alih melepaskan kunci bersama segera setelah tabel atau halaman data yang diperlukan tidak lagi diperlukan, apakah transaksi telah selesai atau belum. Pemindaian dilakukan dengan semantik yang sama dengan transaksi yang berjalan pada tingkat isolasi SERIALIZABLE. Untuk informasi selengkapnya tentang tingkat isolasi, lihat MENGATUR TINGKAT ISOLASI TRANSAKSI (Transact-SQL).

SNAPSHOT
Berlaku untuk: SQL Server 2014 (12.x) dan yang lebih baru.

Tabel yang dioptimalkan memori diakses di bawah isolasi SNAPSHOT. SNAPSHOT hanya dapat digunakan dengan tabel yang dioptimalkan memori (bukan dengan tabel berbasis disk), seperti yang terlihat dalam contoh berikut. Untuk informasi selengkapnya, lihat Pengantar Tabel Memory-Optimized.

SELECT * 
FROM dbo.Customers AS c WITH (SNAPSHOT)   
LEFT JOIN dbo.[Order History] AS oh   
    ON c.customer_id=oh.customer_id;

SPATIAL_WINDOW_MAX_CELLS = <integer_value>
Berlaku untuk: SQL Server 2012 (11.x) dan yang lebih baru.
Menentukan jumlah maksimum sel yang akan digunakan untuk memisahkan objek geometri atau geografi. <>integer_value adalah nilai antara 1 dan 8192.

Opsi ini memungkinkan penyempurnaan waktu eksekusi kueri dengan menyesuaikan tradeoff antara waktu eksekusi filter primer dan sekunder. Jumlah yang lebih besar mengurangi waktu eksekusi filter sekunder, tetapi meningkatkan waktu filter eksekusi utama dan jumlah yang lebih kecil mengurangi waktu eksekusi filter utama, tetapi meningkatkan eksekusi filter sekunder. Untuk data spasial yang lebih padat, angka yang lebih tinggi harus menghasilkan waktu eksekusi yang lebih cepat dengan memberikan perkiraan yang lebih baik dengan filter utama dan mengurangi waktu eksekusi filter sekunder. Untuk data sparser, angka yang lebih rendah akan mengurangi waktu eksekusi filter utama.

Opsi ini berfungsi untuk tessellasi kisi manual dan otomatis.

TABLOCK
Menentukan bahwa kunci yang diperoleh diterapkan pada tingkat tabel. Jenis kunci yang diperoleh tergantung pada pernyataan yang dijalankan. Misalnya, pernyataan SELECT dapat memperoleh kunci bersama. Dengan menentukan TABLOCK, kunci bersama diterapkan ke seluruh tabel, bukan di tingkat baris atau halaman. Jika HOLDLOCK juga ditentukan, kunci tabel ditahan hingga akhir transaksi.

Saat mengimpor data ke dalam heap dengan menggunakan INSERT INTO <target_table> SELECT <columns> FROM <source_table> pernyataan , Anda dapat mengaktifkan pengelogan minimal dan penguncian yang dioptimalkan untuk pernyataan dengan menentukan petunjuk TABLOCK untuk tabel target. Selain itu, model pemulihan database harus diatur ke sederhana atau dicatat secara massal. Petunjuk TABLOCK juga memungkinkan sisipan paralel ke tumpukan atau indeks penyimpan kolom berkluster. Untuk informasi selengkapnya, lihat INSERT (Transact-SQL).

Saat digunakan dengan penyedia set baris massal OPENROWSET untuk mengimpor data ke dalam tabel, TABLOCK memungkinkan beberapa klien memuat data secara bersamaan ke dalam tabel target dengan pengelogan dan penguncian yang dioptimalkan. Untuk informasi selengkapnya, lihat Prasyarat untuk Pengelogan Minimal dalam Impor Massal.

TABLOCKX
Menentukan bahwa kunci eksklusif diambil pada tabel.

UPDLOCK
Menentukan bahwa kunci pembaruan akan diambil dan ditahan hingga transaksi selesai. UPDLOCK mengambil kunci pembaruan untuk operasi baca hanya di tingkat baris atau tingkat halaman. Jika UPDLOCK dikombinasikan dengan TABLOCK, atau kunci tingkat tabel diambil karena alasan lain, kunci eksklusif (X) akan diambil sebagai gantinya.

Ketika UPDLOCK ditentukan, petunjuk tingkat isolasi READCOMMITTED dan READCOMMITTEDLOCK diabaikan. Misalnya, jika tingkat isolasi sesi diatur ke SERIALIZABLE dan kueri menentukan (UPDLOCK, READCOMMITTED), petunjuk READCOMMITTED diabaikan dan transaksi dijalankan menggunakan tingkat isolasi SERIALIZABLE.

XLOCK
Menentukan bahwa kunci eksklusif harus diambil dan ditahan hingga transaksi selesai. Jika ditentukan dengan ROWLOCK, PAGLOCK, atau TABLOCK, kunci eksklusif berlaku untuk tingkat granularitas yang sesuai.

Keterangan

Petunjuk tabel diabaikan jika tabel tidak diakses oleh rencana kueri. Ini mungkin disebabkan oleh pengoptimal yang memilih untuk tidak mengakses tabel sama sekali, atau karena tampilan terindeks diakses sebagai gantinya. Dalam kasus terakhir, mengakses tampilan terindeks dapat dicegah dengan menggunakan petunjuk kueri OPTION (EXPAND VIEWS).

Semua petunjuk kunci disebarkan ke semua tabel dan tampilan yang diakses oleh rencana kueri, termasuk tabel dan tampilan yang direferensikan dalam tampilan. Selain itu, SQL Server melakukan pemeriksaan konsistensi kunci yang sesuai.

Petunjuk kunci ROWLOCK, UPDLOCK, DAN XLOCK yang memperoleh kunci tingkat baris dapat menempatkan kunci pada kunci indeks daripada baris data aktual. Misalnya, jika tabel memiliki indeks nonkluster, dan pernyataan SELECT menggunakan petunjuk kunci ditangani oleh indeks penutup, kunci diperoleh pada kunci indeks dalam indeks penutup daripada pada baris data dalam tabel dasar.

Jika tabel berisi kolom komputasi yang dihitung oleh ekspresi atau fungsi yang mengakses kolom di tabel lain, petunjuk tabel tidak digunakan pada tabel tersebut dan tidak disebarluaskan. Misalnya, petunjuk tabel NOLOCK ditentukan pada tabel dalam kueri. Tabel ini memiliki kolom komputasi yang dihitung oleh kombinasi ekspresi dan fungsi yang mengakses kolom dalam tabel lain. Tabel yang direferensikan oleh ekspresi dan fungsi tidak menggunakan petunjuk tabel NOLOCK saat diakses.

SQL Server tidak mengizinkan lebih dari satu petunjuk tabel dari setiap grup berikut untuk setiap tabel dalam klausa FROM:

  • Petunjuk granularitas: PAGLOCK, NOLOCK, READCOMMITTEDLOCK, ROWLOCK, TABLOCK, atau TABLOCKX.
  • Petunjuk tingkat isolasi: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.

Petunjuk Indeks Terfilter

Indeks yang difilter dapat digunakan sebagai petunjuk tabel, tetapi akan menyebabkan pengoptimal kueri menghasilkan kesalahan 8622 jika tidak mencakup semua baris yang dipilih kueri. Berikut ini adalah contoh petunjuk indeks yang difilter tidak valid. Contoh membuat indeks FIBillOfMaterialsWithComponentID yang difilter lalu menggunakannya sebagai petunjuk indeks untuk pernyataan SELECT. Predikat indeks yang difilter mencakup baris data untuk ComponentID 533, 324, dan 753. Predikat kueri juga menyertakan baris data untuk ComponentID 533, 324, dan 753 tetapi memperluas tataan hasil untuk menyertakan ComponentID 855 dan 924, yang tidak ada dalam indeks yang difilter. Oleh karena itu, pengoptimal kueri tidak dapat menggunakan petunjuk indeks yang difilter dan menghasilkan kesalahan 8622. Untuk informasi selengkapnya, lihat Membuat Indeks Yang Difilter.

IF EXISTS (SELECT name FROM sys.indexes  
    WHERE name = N'FIBillOfMaterialsWithComponentID'   
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))  
DROP INDEX FIBillOfMaterialsWithComponentID  
    ON Production.BillOfMaterials;  
GO  
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithComponentID"  
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)  
    WHERE ComponentID IN (533, 324, 753);  
GO  
SELECT StartDate, ComponentID FROM Production.BillOfMaterials  
    WITH( INDEX (FIBillOfMaterialsWithComponentID) )  
    WHERE ComponentID in (533, 324, 753, 855, 924);  
GO

Pengoptimal Kueri tidak akan mempertimbangkan petunjuk indeks jika opsi SET tidak memiliki nilai yang diperlukan untuk indeks yang difilter. Untuk informasi selengkapnya, lihat MEMBUAT INDEKS (Transact-SQL).

Menggunakan NOEXPAND

NOEXPAND hanya berlaku untuk tampilan terindeks. Tampilan terindeks adalah tampilan dengan indeks berkluster unik yang dibuat di dalamnya. Jika kueri berisi referensi ke kolom yang ada baik dalam tampilan terindeks maupun tabel dasar, dan Pengoptimal Kueri menentukan bahwa menggunakan tampilan terindeks menyediakan metode terbaik untuk mengeksekusi kueri, pengoptimal kueri menggunakan indeks pada tampilan. Fungsionalitas ini disebut pencocokan tampilan terindeks. Sebelum SQL Server 2016 (13.x) SP1, penggunaan otomatis tampilan terindeks oleh Pengoptimal Kueri hanya didukung dalam edisi SQL Server tertentu. Untuk daftar fitur yang didukung oleh edisi SQL Server, lihat Fitur yang Didukung oleh Edisi SQL Server 2016, Fitur yang Didukung oleh Edisi SQL Server 2017, dan Fitur yang Didukung oleh Edisi SQL Server 2019.

Namun, agar Pengoptimal Kueri mempertimbangkan tampilan terindeks untuk pencocokan, atau menggunakan tampilan terindeks yang direferensikan dengan petunjuk NOEXPAND, opsi SET berikut harus diatur ke AKTIF.

Catatan

Azure SQL Database mendukung penggunaan otomatis tampilan terindeks tanpa menentukan petunjuk NOEXPAND.

  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • ARITHABORT1
  • CONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER

1 ARITHABORT secara implisit diatur ke AKTIF saat ANSI_WARNINGS diatur ke AKTIF. Oleh karena itu, Anda tidak perlu menyesuaikan pengaturan ini secara manual.

Selain itu, opsi NUMERIC_ROUNDABORT harus diatur ke NONAKTIF.

Untuk memaksa Pengoptimal Kueri menggunakan indeks untuk tampilan terindeks, tentukan opsi NOEXPAND. Petunjuk ini hanya dapat digunakan jika tampilan juga dinamai dalam kueri. SQL Server tidak memberikan petunjuk untuk memaksa tampilan terindeks tertentu untuk digunakan dalam kueri yang tidak menamai tampilan secara langsung dalam klausa FROM; namun, Pengoptimal Kueri mempertimbangkan untuk menggunakan tampilan terindeks, meskipun tidak direferensikan langsung dalam kueri. Mesin Database SQL Server hanya akan secara otomatis membuat statistik pada tampilan terindeks saat petunjuk tabel NOEXPAND digunakan. Menghilangkan petunjuk ini dapat menyebabkan peringatan rencana eksekusi tentang statistik yang hilang yang tidak dapat diselesaikan dengan membuat statistik secara manual. Selama pengoptimalan kueri, Mesin Database akan menggunakan statistik tampilan yang dibuat secara otomatis atau manual saat kueri mereferensikan tampilan secara langsung dan petunjuk NOEXPAND digunakan.

Menggunakan Petunjuk Tabel sebagai Petunjuk Kueri

Petunjuk tabel juga dapat ditentukan sebagai petunjuk kueri dengan menggunakan klausa OPTION (TABLE HINT). Sebaiknya gunakan petunjuk tabel sebagai petunjuk kueri hanya dalam konteks panduan rencana. Untuk kueri ad-hoc, tentukan petunjuk ini hanya sebagai petunjuk tabel. Untuk informasi selengkapnya, lihat Petunjuk Kueri (Transact-SQL).

Izin

Petunjuk KEEPIDENTITY, IGNORE_CONSTRAINTS, dan IGNORE_TRIGGERS memerlukan ALTER izin pada tabel.

Contoh

A. Menggunakan petunjuk TABLOCK untuk menentukan metode penguncian

Contoh berikut menentukan bahwa kunci bersama diambil pada Production.Product tabel dalam database AdventureWorks2012 dan ditahan hingga akhir pernyataan UPDATE.

UPDATE Production.Product  
WITH (TABLOCK)  
SET ListPrice = ListPrice * 1.10  
WHERE ProductNumber LIKE 'BK-%';  
GO

B. Menggunakan petunjuk FORCESEEK untuk menentukan operasi pencarian indeks

Contoh berikut menggunakan petunjuk FORCESEEK tanpa menentukan indeks untuk memaksa pengoptimal kueri melakukan operasi pencarian indeks pada Sales.SalesOrderDetail tabel dalam database AdventureWorks2012.

SELECT *  
FROM Sales.SalesOrderHeader AS h  
INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)  
    ON h.SalesOrderID = d.SalesOrderID   
WHERE h.TotalDue > 100  
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);  
GO

Contoh berikut menggunakan petunjuk FORCESEEK dengan indeks untuk memaksa pengoptimal kueri melakukan operasi pencarian indeks pada indeks dan kolom indeks yang ditentukan.

SELECT h.SalesOrderID, h.TotalDue, d.OrderQty  
FROM Sales.SalesOrderHeader AS h  
    INNER JOIN Sales.SalesOrderDetail AS d   
    WITH (FORCESEEK (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID (SalesOrderID)))   
    ON h.SalesOrderID = d.SalesOrderID   
WHERE h.TotalDue > 100  
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);   
GO

C. Menggunakan petunjuk FORCESCAN untuk menentukan operasi pemindaian indeks

Contoh berikut menggunakan petunjuk FORCESCAN untuk memaksa pengoptimal kueri melakukan operasi pemindaian pada Sales.SalesOrderDetail tabel dalam database AdventureWorks2012.

SELECT h.SalesOrderID, h.TotalDue, d.OrderQty  
FROM Sales.SalesOrderHeader AS h  
    INNER JOIN Sales.SalesOrderDetail AS d   
    WITH (FORCESCAN)   
    ON h.SalesOrderID = d.SalesOrderID   
WHERE h.TotalDue > 100  
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);

Lihat juga

OPENROWSET (Transact-SQL)
Petunjuk (Transact-SQL)
Petunjuk Kueri (T-SQL)