Bagikan melalui

sp_indexoption (T-SQL)

  • Artikel

Berlaku untuk:SQL Server

Mengatur nilai opsi penguncian untuk indeks atau tabel berkluster yang ditentukan pengguna dan tidak berkluster tanpa indeks berkluster.

Mesin Database SQL Server secara otomatis membuat pilihan penguncian tingkat halaman, baris, atau tabel. Anda tidak perlu mengatur opsi ini secara manual. sp_indexoption disediakan untuk pengguna ahli yang tahu dengan pasti bahwa jenis kunci tertentu selalu sesuai.

Penting

Fitur ini akan dihapus dalam 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. Sebagai gantinya, gunakan ALTER INDEX (Transact-SQL).

Konvensi sintaks transact-SQL

Sintaks

  
sp_indexoption [ @IndexNamePattern = ] 'table_or_index_name'   
    , [ @OptionName = ] 'option_name'   
    , [ @OptionValue = ] 'value'

Argumen

[ @IndexNamePattern = ] 'table_or_index_name' Adalah nama tabel atau indeks yang memenuhi syarat atau tidak memenuhi syarat dari tabel atau indeks yang ditentukan pengguna. table_or_index_name adalah nvarchar(1035), tanpa default. Tanda kutip diperlukan hanya jika indeks atau nama tabel yang memenuhi syarat ditentukan. Jika nama tabel yang sepenuhnya memenuhi syarat, termasuk nama database, disediakan, nama database harus menjadi nama database saat ini. Jika nama tabel ditentukan tanpa indeks, nilai opsi yang ditentukan diatur untuk semua indeks pada tabel tersebut dan tabel itu sendiri jika tidak ada indeks berkluster.

[ @OptionName = ] 'option_name' Adalah nama opsi indeks. option_name adalah varchar(35), tanpa default. option_name bisa memiliki salah satu nilai berikut.

Nilai Deskripsi
AllowRowLocks Saat TRUE, kunci baris diizinkan saat mengakses indeks. Mesin Database menentukan kapan kunci baris digunakan. Saat FALSE, kunci baris tidak digunakan. Defaultnya adalah TRUE.
AllowPageLocks Ketika TRUE, kunci halaman diizinkan saat mengakses indeks. Mesin Database menentukan kapan kunci halaman digunakan. Saat FALSE, kunci halaman tidak digunakan. Defaultnya adalah TRUE.
DisAllowRowLocks Ketika TRUE, kunci baris tidak digunakan. Saat FALSE, kunci baris diizinkan saat mengakses indeks. Mesin Database menentukan kapan kunci baris digunakan.
DisAllowPageLocks Ketika TRUE, kunci halaman tidak digunakan. Saat FALSE, kunci halaman diizinkan saat mengakses indeks. Mesin Database menentukan kapan kunci halaman digunakan.

[ @OptionValue = ] 'value' Menentukan apakah pengaturan option_name diaktifkan (TRUE, ON, ya, atau 1) atau dinonaktifkan (FALSE, OFF, no, atau 0). nilainya adalah varchar(12), tanpa default.

Mengembalikan Nilai Kode

0 (berhasil) atau lebih besar dari 0 (kegagalan)

Keterangan

Indeks XML tidak didukung. Jika indeks XML ditentukan, atau nama tabel ditentukan tanpa nama indeks dan tabel berisi indeks XML, pernyataan gagal. Untuk mengatur opsi ini, gunakan ALTER INDEX sebagai gantinya.

Untuk menampilkan properti penguncian baris dan halaman saat ini, gunakan TAMPILAN katalog INDEXPROPERTY atau sys.indexes .

  • Kunci tingkat baris, halaman, dan tabel diizinkan saat mengakses indeks saat AllowRowLocks = TRUE atau DisAllowRowLocks = FALSE, dan AllowPageLocks = TRUE atau DisAllowPageLocks = FALSE. Mesin Database memilih kunci yang sesuai dan dapat meningkatkan kunci dari baris atau kunci halaman ke kunci tabel.

Hanya kunci tingkat tabel yang diizinkan saat mengakses indeks saat AllowRowLocks = FALSE atau DisAllowRowLocks = TRUE dan AllowPageLocks = FALSE atau DisAllowPageLocks = TRUE.

Jika nama tabel ditentukan tanpa indeks, pengaturan diterapkan ke semua indeks pada tabel tersebut. Ketika tabel yang mendasar tidak memiliki indeks berkluster (yaitu, itu adalah timbunan) pengaturan diterapkan sebagai berikut:

  • Saat AllowRowLocks atau DisAllowRowLocks diatur ke TRUE atau FALSE, pengaturan diterapkan ke heap dan indeks noncluster terkait.

  • Ketika opsi AllowPageLocks diatur ke TRUE atau DisAllowPageLocks diatur ke FALSE, pengaturan diterapkan ke heap dan indeks noncluster terkait.

  • Ketika opsi AllowPageLocks diatur FALSE atau DisAllowPageLocks diatur ke TRUE, pengaturan sepenuhnya diterapkan ke indeks nonclustered. Artinya, semua kunci halaman tidak diizinkan pada indeks nonclustered. Pada tumpukan, hanya kunci bersama (S), pembaruan (U), dan eksklusif (X) untuk halaman yang tidak diizinkan. Mesin Database masih dapat memperoleh kunci halaman niat (IS, IU atau IX) untuk tujuan internal.

Izin

Memerlukan izin UBAH pada tabel.

Contoh

J. Mengatur opsi pada indeks tertentu

Contoh berikut melarang kunci halaman pada IX_Customer_TerritoryID indeks pada Customer tabel.

USE AdventureWorks2022;  
GO  
EXEC sp_indexoption N'Sales.Customer.IX_Customer_TerritoryID',  
    N'disallowpagelocks', TRUE;

B. Mengatur opsi pada semua indeks pada tabel

Contoh berikut melarang penguncian baris pada semua indeks yang Product terkait dengan tabel. Tampilan sys.indexes katalog dikueri sebelum dan sesudah menjalankan sp_indexoption prosedur untuk menunjukkan hasil pernyataan.

USE AdventureWorks2022;  
GO  
--Display the current row and page lock options for all indexes on the table.  
SELECT name, type_desc, allow_row_locks, allow_page_locks   
FROM sys.indexes  
WHERE object_id = OBJECT_ID(N'Production.Product');  
GO  
-- Set the disallowrowlocks option on the Product table.   
EXEC sp_indexoption N'Production.Product',  
    N'disallowrowlocks', TRUE;  
GO  
--Verify the row and page lock options for all indexes on the table.  
SELECT name, type_desc, allow_row_locks, allow_page_locks   
FROM sys.indexes  
WHERE object_id = OBJECT_ID(N'Production.Product');  
GO

C. Mengatur opsi pada tabel tanpa indeks berkluster

Contoh berikut melarang kunci halaman pada tabel tanpa indeks berkluster (timbunan). Tampilan sys.indexes katalog dikueri sebelum dan sesudah sp_indexoption prosedur dijalankan untuk menunjukkan hasil pernyataan.

USE AdventureWorks2022;  
GO  
--Display the current row and page lock options of the table.   
SELECT OBJECT_NAME (object_id) AS [Table], type_desc, allow_row_locks, allow_page_locks   
FROM sys.indexes  
WHERE OBJECT_NAME (object_id) = N'DatabaseLog';  
GO  
-- Set the disallowpagelocks option on the table.   
EXEC sp_indexoption DatabaseLog,  
    N'disallowpagelocks', TRUE;  
GO  
--Verify the row and page lock settings of the table.  
SELECT OBJECT_NAME (object_id) AS [Table], allow_row_locks, allow_page_locks   
FROM sys.indexes  
WHERE OBJECT_NAME (object_id) = N'DatabaseLog';  
GO

Lihat Juga

INDEXPROPERTY (Transact-SQL)
Prosedur Tersimpan Sistem (Transact-SQL)
sys.indexes (Transact-SQL)