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.

Konvensi sintaks transact-SQL

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, yesatau 1) atau dinonaktifkan (FALSE, , OFF, noatau 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 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 tumpukan) pengaturan diterapkan sebagai berikut:

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

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

  • Ketika AllowPageLocks opsi diatur FALSE atau DisAllowPageLocks diatur ke TRUE, 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

Konten terkait