sp_indexoption (T-SQL)
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.
Sintaks
sp_indexoption
[ @IndexNamePattern = ] N'IndexNamePattern'
, [ @OptionName = ] 'OptionName'
, [ @OptionValue = ] 'OptionValue'
[ ; ]
Argumen
[ @IndexNamePattern = ] N'IndexNamePattern'
Nama tabel atau indeks yang memenuhi syarat atau tidak memenuhi syarat. @IndexNamePattern 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 = ] 'OptionName'
Nama opsi indeks. @OptionName adalah varchar(35), dan bisa menjadi salah satu nilai berikut.
Nilai | Deskripsi |
---|---|
AllowRowLocks |
Ketika TRUE , kunci baris diizinkan saat mengakses indeks. Mesin Database menentukan kapan kunci baris digunakan. Saat FALSE , kunci baris tidak digunakan. Default adalah TRUE . |
AllowPageLocks |
Ketika TRUE , kunci halaman diizinkan saat mengakses indeks. Mesin Database menentukan kapan kunci halaman digunakan. Saat FALSE , kunci halaman tidak digunakan. Default adalah TRUE . |
DisAllowRowLocks |
Saat TRUE , kunci baris tidak digunakan. Ketika FALSE , kunci baris diizinkan saat mengakses indeks. Mesin Database menentukan kapan kunci baris digunakan. |
DisAllowPageLocks |
Saat TRUE , kunci halaman tidak digunakan. Ketika FALSE , kunci halaman diizinkan saat mengakses indeks. Mesin Database menentukan kapan kunci halaman digunakan. |
[ @OptionValue = ] 'OptionValue'
Menentukan apakah pengaturan @OptionName diaktifkan (TRUE
, , ON
, yes
atau 1
) atau dinonaktifkan (FALSE
, , OFF
, no
atau 0
). @OptionValue adalah varchar(12), tanpa default.
Mengembalikan nilai kode
0
(berhasil) atau > 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, tingkat halaman, dan tingkat tabel diizinkan saat mengakses indeks saat
AllowRowLocks = TRUE
atauDisAllowRowLocks = FALSE
, danAllowPageLocks = TRUE
atauDisAllowPageLocks = 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 tumpukan) pengaturan diterapkan sebagai berikut:
Ketika
AllowRowLocks
atauDisAllowRowLocks
diatur keTRUE
atauFALSE
, pengaturan diterapkan ke heap dan indeks nonclustered terkait.Ketika
AllowPageLocks
opsi diatur ke atauDisAllowPageLocks
diatur keTRUE
FALSE
, pengaturan diterapkan ke heap dan indeks nonclustered terkait.Ketika
AllowPageLocks
opsi diaturFALSE
atauDisAllowPageLocks
diatur keTRUE
, pengaturan sepenuhnya diterapkan ke indeks yang tidak terkluster. 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
ALTER
Memerlukan izin 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