Bagikan melalui

Petunjuk tabel (Transact-SQL)

  • Artikel

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

Petunjuk tabel digunakan untuk mengambil alih perilaku default pengoptimal kueri selama pernyataan bahasa manipulasi data (DML). Anda dapat 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 pengoptimal kueri SQL Server biasanya memilih rencana eksekusi terbaik untuk kueri, kami menyarankan agar petunjuk hanya digunakan sebagai upaya terakhir oleh pengembang dan administrator database yang berpengalaman.

Berlaku untuk:

Konvensi sintaks transact-SQL

Sintaks

WITH  ( <table_hint> [ [ , ] ...n ] )

<table_hint> ::=
{ NOEXPAND
  | INDEX ( <index_value> [ , ...n ] ) | INDEX = ( <index_value> )
  | FORCESEEK [ ( <index_value> ( <index_column_name> [ , ... ] ) ) ]
  | FORCESCAN
  | 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
}

Argumen

DENGAN ( <table_hint> ) [ [ , ] ...n ]

Dengan beberapa pengecualian, petunjuk tabel didukung dalam klausa 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 SQL Server di masa 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, UPDLOCKREADUNCOMMITTED, REPEATABLEREADSERIALIZABLE, READCOMMITTED, TABLOCK, ROWLOCKTABLOCKXPAGLOCK, NOWAIT, READPAST, , XLOCK, , SNAPSHOTdan .NOEXPAND Ketika petunjuk tabel ini ditentukan tanpa WITH kata kunci, 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 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> )

INDEX() Sintaks 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 indeks lain 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 nonclustered.

KEEPIDENTITY

Hanya berlaku dalam pernyataan INSERT ketika opsi MASSAL 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 MASSAL digunakan dengan OPENROWSET.

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

Untuk contoh yang menggunakan petunjuk ini dalam INSERT ... PERNYATAAN SELECT * FROM OPENROWSET(BULK...), lihat Menyimpan Null atau Menggunakan 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 (10.50.x) Paket Layanan 1, 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

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

  • index_column_name

    Nama kolom indeks yang akan disertakan dalam operasi pencarian. Menentukan FORCESEEK dengan parameter indeks mirip dengan menggunakan FORCESEEK dengan INDEX petunjuk. 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 yang perlu 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.

FORCESEEK Petunjuk dapat ditentukan dengan cara berikut.

Sintaks Contoh Deskripsi
Tanpa indeks atau INDEX petunjuk FROM dbo.MyTable WITH (FORCESEEK) Pengoptimal kueri hanya mempertimbangkan operasi pencarian indeks untuk mengakses tabel atau melihat melalui indeks yang relevan.
Dikombinasikan dengan INDEX petunjuk FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex)) Pengoptimal kueri hanya mempertimbangkan operasi pencarian indeks untuk mengakses tabel atau melihat 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 melihat melalui indeks yang ditentukan menggunakan setidaknya kolom indeks yang ditentukan.

Saat menggunakan FORCESEEK petunjuk (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, NOEXPAND petunjuk 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 menyebabkan tidak ada rencana yang ditemukan, kesalahan 8622 dikembalikan.

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

  • Petunjuk tidak dapat ditentukan untuk tabel yang merupakan target pernyataan INSERT, UPDATE, atau DELETE.
  • Petunjuk tidak dapat ditentukan dalam kombinasi dengan INDEX petunjuk atau petunjuk lain FORCESEEK .
  • Setidaknya satu kolom harus ditentukan dan harus menjadi kolom kunci di depannya.
  • 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 FORCESCAN petunjuk.
  • Untuk indeks yang dipartisi, kolom partisi yang secara implisit ditambahkan oleh SQL Server tidak dapat ditentukan dalam FORCESEEK petunjuk.

Perhatian

Menentukan FORCESEEK dengan parameter membatasi jumlah paket yang dapat dipertimbangkan oleh pengoptimal lebih dari saat menentukan FORCESEEK tanpa parameter. Ini dapat menyebabkan kesalahan Plan cannot be generated 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 (10.50.x) Paket Layanan 1 dan versi yang lebih baru

Menentukan bahwa pengoptimal kueri hanya menggunakan operasi pemindaian indeks sebagai jalur akses ke tabel atau tampilan yang dirujuk. FORCESCAN Petunjuk 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 INDEX petunjuk. 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 INDEX(0) indeks 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.

FORCESCAN Petunjuk 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 pada tabel.
  • Petunjuk tidak dapat ditentukan untuk sumber data jarak jauh.
  • Petunjuk tidak dapat ditentukan dalam kombinasi dengan FORCESEEK petunjuk.

HOLDLOCK

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

IGNORE_CONSTRAINTS

Hanya berlaku dalam pernyataan INSERT ketika opsi MASSAL 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. 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 bertahap.

IGNORE_TRIGGERS

Hanya berlaku dalam pernyataan INSERT ketika opsi MASSAL 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 penting.

NOLOCK

Setara dengan READUNCOMMITTED. Untuk informasi selengkapnya, lihat READUNCOMMITTED nanti di artikel ini.

Catatan

Untuk pernyataan UPDATE atau DELETE: Fitur ini akan dihapus di versi SQL Server yang akan datang. 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 pada tabel. NOWAIT setara dengan menentukan SET LOCK_TIMEOUT 0 untuk tabel tertentu. NOWAIT Petunjuk tidak berfungsi ketika TABLOCK petunjuk juga disertakan. Untuk mengakhiri kueri tanpa menunggu saat menggunakan TABLOCK petunjuk, 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 SNAPSHOT tingkat isolasi, 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 READ_COMMITTED_SNAPSHOT database NONAKTIF, Mesin Database memperoleh kunci bersama saat data dibaca dan melepaskan kunci tersebut saat operasi baca selesai. Jika opsi READ_COMMITTED_SNAPSHOT database 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 di versi SQL Server yang akan datang. 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 saat operasi baca selesai, terlepas dari READ_COMMITTED_SNAPSHOT pengaturan opsi database. 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 melewati baris alih-alih memblokir transaksi saat ini sampai kunci dilepaskan. Misalnya, asumsikan tabel T1 berisi kolom bilangan bulat tunggal 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 sebelumnya 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 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 SNAPSHOT tingkat isolasi, READPAST harus dikombinasikan dengan petunjuk tabel lain yang memerlukan kunci, seperti UPDLOCK dan HOLDLOCK.

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

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

Untuk menentukan READPAST petunjuk dalam kasus ini, hapus READCOMMITTED petunjuk tabel jika ada, dan sertakan READCOMMITTEDLOCK petunjuk tabel 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 agar tidak membaca data yang terkunci. Mengizinkan bacaan 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 pengguna dengan data yang tidak pernah dilakukan, atau menyebabkan pengguna melihat rekaman dua kali (atau tidak sama sekali).

READUNCOMMITTED dan NOLOCK petunjuk hanya berlaku untuk kunci data. Semua kueri, termasuk yang memiliki READUNCOMMITTED petunjuk 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 READUNCOMMITTED atau NOLOCK petunjuk, 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 READUNCOMMITTED petunjuk dan NOLOCK dalam klausa FROM yang berlaku untuk tabel target pernyataan UPDATE atau DELETE.

Catatan

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

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

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

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

Catatan

Jika Anda menerima pesan kesalahan 601 ketika READUNCOMMITTED ditentukan, atasi karena Anda akan mengalami 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 SNAPSHOT tingkat isolasi, 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 di SERIALIZABLE tingkat isolasi. Untuk informasi selengkapnya tentang tingkat isolasi, lihat MENGATUR TINGKAT ISOLASI TRANSAKSI (Transact-SQL).

SNAPSHOT

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

Tabel yang dioptimalkan memori diakses di bawah SNAPSHOT isolasi. 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 yang Dioptimalkan Memori.

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 versi 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, jumlah 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 yang lebih jarang, angka yang lebih rendah mengurangi waktu eksekusi filter utama.

Opsi ini berfungsi untuk tesselulasi 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 alih-alih di tingkat baris atau halaman. Jika HOLDLOCK juga ditentukan, kunci tabel ditahan hingga akhir transaksi.

Saat mengimpor data ke dalam timbunan dengan menggunakan INSERT INTO <target_table> SELECT <columns> FROM <source_table> pernyataan , Anda dapat mengaktifkan pengelogan minimal dan penguncian optimal untuk pernyataan dengan menentukan TABLOCK petunjuk untuk tabel target. Selain itu, model pemulihan database harus diatur ke sederhana atau dicatat secara massal. Petunjuk ini 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 harus 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) diambil sebagai gantinya.

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

XLOCK

Menentukan bahwa kunci eksklusif harus diambil dan ditahan hingga transaksi selesai. Jika ditentukan dengan ROWLOCK, , PAGLOCKatau 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 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 nonclustered, 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, NOLOCK petunjuk tabel ditentukan pada tabel dalam kueri. Tabel ini memiliki kolom komputasi yang dihitung oleh kombinasi ekspresi dan fungsi yang mengakses kolom di tabel lain. Tabel yang direferensikan oleh ekspresi dan fungsi tidak menggunakan NOLOCK petunjuk tabel 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, READCOMMITTEDLOCKROWLOCK, TABLOCK, atau TABLOCKX.
  • Petunjuk tingkat isolasi: HOLDLOCK, , NOLOCK, READCOMMITTEDREPEATABLEREAD, SERIALIZABLE.

Petunjuk indeks yang difilter

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 ComponentIDs 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 Terfilter.

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 atasnya. 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 menjalankan kueri, pengoptimal kueri menggunakan indeks pada tampilan. Fungsionalitas ini disebut pencocokan tampilan terindeks. Sebelum SQL Server 2016 (13.x) Paket Layanan 1, penggunaan otomatis tampilan terindeks oleh pengoptimal kueri hanya didukung dalam edisi SQL Server tertentu. Karena, semua edisi mendukung penggunaan otomatis tampilan terindeks. Azure SQL Database dan Azure SQL Managed Instance juga mendukung penggunaan otomatis tampilan terindeks tanpa menentukan NOEXPAND petunjuk.

Untuk informasi selengkapnya, lihat Panduan arsitektur pemrosesan kueri.

Untuk daftar fitur yang didukung oleh edisi SQL Server di Windows, lihat:

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

  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • ARITHABORT 1
  • 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 NOEXPAND opsi . Petunjuk ini hanya dapat digunakan jika tampilan juga dinamai dalam kueri. SQL Server tidak memberikan petunjuk untuk memaksa tampilan terindeks tertentu digunakan dalam kueri yang tidak memberi nama tampilan 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 NOEXPAND petunjuk tabel 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 menggunakan statistik tampilan yang dibuat secara otomatis atau manual saat kueri mereferensikan tampilan secara langsung dan NOEXPAND petunjuk 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

J. Gunakan petunjuk TABLOCK untuk menentukan metode penguncian

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

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

B. Gunakan petunjuk FORCESEEK untuk menentukan operasi pencarian indeks

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

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 FORCESEEK petunjuk 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. Gunakan petunjuk FORCESCAN untuk menentukan operasi pemindaian indeks

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

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

Konten terkait