Membuat tampilan terindeks

Artikel
07/23/2024

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance

Artikel ini menjelaskan cara membuat indeks pada tampilan. Indeks pertama yang dibuat pada tampilan harus merupakan indeks berkluster yang unik. Setelah indeks berkluster unik dibuat, Anda dapat membuat lebih banyak indeks non-kluster. Membuat indeks berkluster unik pada tampilan meningkatkan performa kueri, karena tampilan disimpan dalam database dengan cara yang sama tabel dengan indeks berkluster disimpan. Pengoptimal kueri dapat menggunakan tampilan terindeks untuk mempercepat eksekusi kueri. Tampilan tidak harus dirujuk dalam kueri agar pengoptimal mempertimbangkan tampilan tersebut untuk penggantian.

Langkah-langkah

Langkah-langkah berikut diperlukan untuk membuat tampilan terindeks dan sangat penting untuk keberhasilan implementasi tampilan terindeks:

Verifikasi opsi SET sudah benar untuk semua tabel yang sudah ada yang akan direferensikan dalam tampilan.
Verifikasi bahwa SET opsi untuk sesi diatur dengan benar sebelum Anda membuat tabel dan tampilan apa pun.
Verifikasi bahwa definisi tampilan deterministik.
Verifikasi bahwa tabel dasar memiliki pemilik yang sama dengan tampilan.
Buat tampilan dengan menggunakan WITH SCHEMABINDING opsi .
Buat indeks berkluster unik pada tampilan.

Saat Anda menjalankan UPDATE, DELETE atau INSERT operasi (Bahasa Manipulasi Data, atau DML) pada tabel yang direferensikan oleh sejumlah besar tampilan terindeks, atau tampilan terindeks yang lebih sedikit tetapi kompleks, tampilan terindeks yang direferensikan juga harus diperbarui. Akibatnya, performa kueri DML dapat turun secara signifikan, atau dalam beberapa kasus, rencana kueri bahkan tidak dapat diproduksi.

Dalam skenario seperti itu, uji kueri DML Anda sebelum penggunaan produksi, analisis rencana kueri dan setel/sederhanakan pernyataan DML.

Opsi SET yang diperlukan untuk tampilan terindeks

Mengevaluasi ekspresi yang sama dapat menghasilkan hasil yang berbeda di Mesin Database saat opsi yang berbeda SET aktif saat kueri dijalankan. Misalnya, setelah SET opsi diatur ke ON, ekspresi 'abc' + NULL mengembalikan nilai NULLCONCAT_NULL_YIELDS_NULL . Namun, setelah CONCAT_NULL_YIELDS_NULL diatur ke OFF, ekspresi yang sama menghasilkan abc.

Untuk memastikan bahwa tampilan dapat dipertahankan dengan benar dan mengembalikan hasil yang konsisten, tampilan terindeks memerlukan nilai tetap untuk beberapa SET opsi. Opsi SET dalam tabel berikut ini harus diatur ke nilai yang diperlihatkan di Required value kolom setiap kali kondisi berikut ini terjadi:

Tampilan dan indeks berikutnya pada tampilan dibuat.
Tabel dasar yang direferensikan dalam tampilan pada saat tampilan dibuat.
Saat operasi sisipkan, perbarui, atau hapus dilakukan pada tabel apa pun yang berpartisipasi dalam tampilan terindeks. Persyaratan ini mencakup operasi seperti salinan massal, replikasi, dan kueri terdistribusi.
Tampilan terindeks digunakan oleh pengoptimal kueri untuk menghasilkan rencana kueri.

ATUR opsi	Nilai yang diperlukan	Nilai server default	Default Nilai OLE DB dan ODBC	Default Nilai Pustaka DB
`ANSI_NULLS`	`ON`	`ON`	`ON`	`OFF`
`ANSI_PADDING`	`ON`	`ON`	`ON`	`OFF`
`ANSI_WARNINGS` ¹	`ON`	`ON`	`ON`	`OFF`
`ARITHABORT`	`ON`	`ON`	`OFF`	`OFF`
`CONCAT_NULL_YIELDS_NULL`	`ON`	`ON`	`ON`	`OFF`
`NUMERIC_ROUNDABORT`	`OFF`	`OFF`	`OFF`	`OFF`
`QUOTED_IDENTIFIER`	`ON`	`ON`	`ON`	`OFF`

¹ Pengaturan ANSI_WARNINGS untuk ON mengatur secara implisit ARITHABORT ke ON.

Jika Anda menggunakan koneksi server OLE DB atau ODBC, satu-satunya nilai yang harus dimodifikasi adalah ARITHABORT pengaturan. Semua nilai Pustaka DB harus diatur dengan benar baik di tingkat server dengan menggunakan sp_configure atau dari aplikasi dengan menggunakan SET perintah .

Penting

Kami sangat menyarankan Agar Anda mengatur ARITHABORT opsi pengguna ke ON seluruh server segera setelah tampilan atau indeks pertama yang diindeks pada kolom komputasi dibuat di database apa pun di server.

Persyaratan tampilan deterministik

Definisi tampilan terindeks harus deterministik. Tampilan bersifat deterministik jika semua ekspresi dalam daftar pilih, dan WHERE klausa dan GROUP BY , bersifat deterministik. Ekspresi deterministik selalu mengembalikan hasil yang sama setiap kali dievaluasi dengan sekumpulan nilai input tertentu. Hanya fungsi deterministik yang dapat berpartisipasi dalam ekspresi deterministik. Misalnya, DATEADD fungsi ini deterministik karena selalu mengembalikan hasil yang sama untuk sekumpulan nilai argumen yang diberikan untuk tiga parameternya. GETDATE tidak deterministik karena selalu dipanggil dengan argumen yang sama, tetapi nilai yang dikembalikannya berubah setiap kali dijalankan.

Untuk menentukan apakah kolom tampilan deterministik, gunakan IsDeterministic properti fungsi COLUMNPROPERTY . Untuk menentukan apakah kolom deterministik dalam tampilan dengan pengikatan skema tepat, gunakan IsPrecise properti COLUMNPROPERTY fungsi. COLUMNPROPERTY1 mengembalikan jika TRUE, 0 jika FALSE, dan NULL untuk input yang tidak valid. Ini berarti kolom tidak deterministik atau tidak tepat.

Bahkan jika ekspresi deterministik, jika berisi ekspresi float, hasil yang tepat tergantung pada arsitektur prosesor atau versi kode mikro. Untuk memastikan integritas data, ekspresi tersebut hanya dapat berpartisipasi sebagai kolom non-kunci tampilan terindeks. Ekspresi deterministik yang tidak berisi ekspresi float disebut tepat. Hanya ekspresi deterministik yang tepat yang dapat berpartisipasi dalam kolom kunci dan dalam WHERE atau GROUP BY klausa tampilan terindeks.

Persyaratan tambahan

Persyaratan berikut juga harus dipenuhi, selain SET opsi dan persyaratan fungsi deterministik

Pengguna yang menjalankan CREATE INDEX harus menjadi pemilik tampilan.
Saat Anda membuat indeks, IGNORE_DUP_KEY opsi indeks harus diatur ke OFF (pengaturan default).
Tabel harus dirujuk oleh nama dua bagian, <schema>.<tablename>, dalam definisi tampilan.
Fungsi yang ditentukan pengguna yang direferensikan dalam tampilan harus dibuat dengan menggunakan WITH SCHEMABINDING opsi .
Setiap fungsi yang ditentukan pengguna yang dirujuk dalam tampilan harus dirujuk oleh nama dua bagian, <schema>.<function>.
Properti akses data dari fungsi yang ditentukan pengguna harus NO SQL, dan properti akses eksternal harus NO.
Fungsi runtime bahasa umum (CLR) dapat muncul dalam daftar tampilan yang dipilih, tetapi tidak dapat menjadi bagian dari definisi kunci indeks berkluster. Fungsi CLR tidak dapat muncul dalam WHERE klausa tampilan atau ON klausa JOIN operasi dalam tampilan.

Fungsi CLR dan metode jenis yang ditentukan pengguna CLR yang digunakan dalam definisi tampilan harus memiliki properti yang diatur seperti yang ditunjukkan dalam tabel berikut.

Properti	Catatan
DETERMINISTIK = TRUE	Harus dinyatakan secara eksplisit sebagai atribut metode Microsoft .NET Framework.
PRECISE = TRUE	Harus dinyatakan secara eksplisit sebagai atribut dari metode .NET Framework.
AKSES DATA = TIDAK ADA SQL	Ditentukan dengan mengatur atribut ke `DataAccess` `DataAccessKind.None` dan `SystemDataAccess` atribut ke `SystemDataAccessKind.None`.
AKSES EKSTERNAL = TIDAK	Properti ini default ke NO untuk rutinitas CLR.

Tampilan harus dibuat dengan menggunakan WITH SCHEMABINDING opsi .
Tampilan harus mereferensikan hanya tabel dasar yang berada dalam database yang sama dengan tampilan. Tampilan tidak dapat mereferensikan tampilan lain.
Jika GROUP BY ada, definisi VIEW harus berisi COUNT_BIG(*) dan tidak boleh berisi HAVING. Pembatasan ini GROUP BY hanya berlaku untuk definisi tampilan terindeks. Kueri dapat menggunakan tampilan terindeks dalam rencana eksekusinya meskipun tidak memenuhi batasan ini GROUP BY .
Jika definisi tampilan berisi GROUP BY klausa, kunci indeks berkluster unik hanya dapat mereferensikan kolom yang ditentukan dalam GROUP BY klausa.

Pernyataan SELECT dalam definisi tampilan tidak boleh berisi sintaks Transact-SQL berikut:

Fungsi Transact-SQL	Kemungkinan alternatif
`COUNT`	Menggunakan `COUNT_BIG`
`ROWSET` fungsi (`OPENDATASOURCE`, `OPENQUERY`, `OPENROWSET`, dan `OPENXML`)
Rata-rata aritmatika (`AVG`)	Gunakan `COUNT_BIG` dan `SUM` sebagai kolom terpisah
Fungsi agregat statistik (`STDEV`,,`STDEVPVAR`, dan`VARP`)
`SUM` fungsi yang mereferensikan ekspresi nullable	Gunakan `ISNULL` di dalam `SUM()` untuk membuat ekspresi tidak dapat diubah ke null
Fungsi agregat lainnya (`MIN`,,`MAXCHECKSUM_AGG`, dan`STRING_AGG`)
Fungsi agregat yang ditentukan pengguna (SQL CLR)

Klausul SELECT	Elemen T-SQL	Kemungkinan alternatif
`WITH cte AS`	Ekspresi tabel umum (CTE) `WITH`
`SELECT`	Subkueri
`SELECT`	`SELECT [ <table>. ] *`	Kolom nama eksplisit
`SELECT`	`SELECT DISTINCT`	Menggunakan `GROUP BY`
`SELECT`	`SELECT TOP`
`SELECT`	`OVER` klausa, yang mencakup fungsi jendela peringkat atau agregat
`FROM`	`LEFT OUTER JOIN`
`FROM`	`RIGHT OUTER JOIN`
`FROM`	`FULL OUTER JOIN`
`FROM`	`OUTER APPLY`
`FROM`	`CROSS APPLY`
`FROM`	Ekspresi tabel turunan (yaitu, menggunakan `SELECT` dalam `FROM` klausa)
`FROM`	Gabungan mandiri
`FROM`	Variabel tabel
`FROM`	Fungsi bernilai tabel sebaris
`FROM`	Fungsi bernilai tabel multi-pernyataan
`FROM`	`PIVOT`, `UNPIVOT`
`FROM`	`TABLESAMPLE`
`FROM`	`FOR SYSTEM_TIME`	Mengkueri tabel riwayat temporal secara langsung
`WHERE`	Predikat teks lengkap (`CONTAINS`, , `FREETEXTCONTAINSTABLE`, `FREETEXTTABLE`)
`GROUP BY`	`CUBE`, `ROLLUP`, atau `GROUPING SETS` operator	Menentukan tampilan terindeks terpisah untuk setiap kombinasi `GROUP BY` kolom
`GROUP BY`	`HAVING`
Operator set	`UNION`, , `UNION ALLEXCEPT`,`INTERSECT`	Gunakan `OR`, `AND NOT`, dan `AND` dalam `WHERE` klausul masing-masing
`ORDER BY`	`ORDER BY`
`ORDER BY`	`OFFSET`

Jenis kolom sumber	Kemungkinan alternatif
Tipe kolom nilai besar yang tidak digunakan lagi (teks, ntext, dan gambar)	Migrasikan kolom ke varchar(max), nvarchar(max), dan varbinary(max) masing-masing.
kolom xml atau FILESTREAM
float ¹ kolom dalam kunci indeks
Kumpulan kolom jarang

¹ Tampilan terindeks dapat berisi kolom float ; namun, kolom tersebut tidak dapat disertakan dalam kunci indeks berkluster.

Penting

Tampilan terindeks tidak didukung di atas kueri temporal (kueri yang menggunakan FOR SYSTEM_TIME klausa).

Rekomendasi untuk datetime dan smalldatetime

Saat Anda merujuk ke literal string datetime dan smalldatetime dalam tampilan terindeks, kami sarankan Anda mengonversi literal secara eksplisit ke jenis tanggal yang Anda inginkan dengan menggunakan gaya format tanggal deterministik. Untuk daftar gaya format tanggal yang deterministik, lihat CAST dan CONVERT. Untuk informasi selengkapnya tentang ekspresi deterministik dan nondeterministik, lihat bagian Pertimbangan di halaman ini.

Ekspresi yang melibatkan konversi implisit string karakter ke datetime atau smalldatetime dianggap nondeterministik. Untuk informasi selengkapnya, lihat Konversi nondeterministik string tanggal harfiah menjadi nilai DATE.

Pertimbangan performa dengan tampilan terindeks

Saat Anda menjalankan DML (seperti UPDATE, DELETE atau INSERT) pada tabel yang direferensikan oleh sejumlah besar tampilan terindeks, atau tampilan terindeks yang lebih sedikit tetapi kompleks, tampilan terindeks tersebut juga harus diperbarui selama eksekusi DML. Akibatnya, performa kueri DML dapat turun secara signifikan, atau dalam beberapa kasus, rencana kueri bahkan tidak dapat diproduksi. Dalam skenario seperti itu, uji kueri DML Anda sebelum penggunaan produksi, analisis rencana kueri dan setel/sederhanakan pernyataan DML.

Untuk mencegah Mesin Database menggunakan tampilan terindeks, sertakan petunjuk OPTION (EXPAND VIEWS) pada kueri. Selain itu, jika salah satu opsi yang tercantum salah diatur, opsi ini mencegah pengoptimal menggunakan indeks pada tampilan. Untuk informasi selengkapnya tentang petunjuk, OPTION (EXPAND VIEWS) lihat SELECT.

Pertimbangan tambahan

Pengaturan large_value_types_out_of_row opsi kolom dalam tampilan terindeks diwarisi dari pengaturan kolom terkait dalam tabel dasar. Nilai ini diatur dengan menggunakan sp_tableoption. Pengaturan default untuk kolom yang terbentuk dari ekspresi adalah 0. Ini berarti bahwa jenis nilai besar disimpan secara berurut.
Tampilan terindeks dapat dibuat pada tabel yang dipartisi, dan dapat dipartisi sendiri.
Semua indeks pada tampilan dihilangkan saat tampilan dihilangkan. Semua indeks non-kluster dan statistik yang dibuat secara otomatis pada tampilan dihilangkan saat indeks berkluster dihilangkan. Statistik yang dibuat pengguna pada tampilan dipertahankan. Indeks yang tidak terkluster dapat dihilangkan secara individual. Menghilangkan indeks berkluster pada tampilan akan menghapus tataan hasil yang disimpan, dan pengoptimal kembali memproses tampilan seperti tampilan standar.
Indeks pada tabel dan tampilan dapat dinonaktifkan. Saat indeks berkluster pada tabel dinonaktifkan, indeks pada tampilan yang terkait dengan tabel juga dinonaktifkan.

Izin

Untuk membuat tampilan, pengguna perlu menahan CREATE VIEW izin dalam database dan ALTER izin pada skema tempat tampilan sedang dibuat. Jika tabel dasar berada dalam skema yang berbeda, REFERENCES izin pada tabel diperlukan minimal. Jika pengguna yang membuat indeks berbeda dari pengguna yang membuat tampilan, untuk pembuatan indeks saja ALTER izin pada tampilan diperlukan (dicakup oleh ALTER pada skema).

Indeks hanya dapat dibuat pada tampilan yang memiliki pemilik yang sama dengan tabel atau tabel yang direferensikan. Konsep ini juga disebut rantai kepemilikan yang utuh antara tampilan dan tabel. Biasanya, ketika tabel dan tampilan berada dalam skema yang sama, pemilik skema yang sama berlaku untuk semua objek dalam skema. Oleh karena itu dimungkinkan untuk membuat tampilan dan bukan pemilik tampilan. Di sisi lain, ada kemungkinan juga bahwa masing-masing objek dalam skema memiliki pemilik eksplisit yang berbeda. Kolom principal_id di sys.tables berisi nilai jika pemilik berbeda dari pemilik skema.

Membuat tampilan terindeks: contoh T-SQL

Contoh berikut membuat tampilan dan indeks pada tampilan tersebut AdventureWorks , dalam database.

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING,
    ANSI_WARNINGS,
    CONCAT_NULL_YIELDS_NULL,
    ARITHABORT,
    QUOTED_IDENTIFIER,
    ANSI_NULLS ON;

--Create view with SCHEMABINDING.
IF OBJECT_ID('Sales.vOrders', 'view') IS NOT NULL
    DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
    WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
    OrderDate,
    ProductID,
    COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od,
    Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate,
    ProductID;
GO

--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON Sales.vOrders (
    OrderDate,
    ProductID
);
GO

Dua kueri berikutnya menunjukkan bagaimana tampilan terindeks dapat digunakan, meskipun tampilan tidak ditentukan dalam FROM klausa.

--This query can use the indexed view even though the view is
--not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
    OrderDate,
    ProductID
FROM Sales.SalesOrderDetail AS od
INNER JOIN Sales.SalesOrderHeader AS o
    ON od.SalesOrderID = o.SalesOrderID
        AND o.OrderDate >= CONVERT(DATETIME, '05/01/2012', 101)
WHERE od.ProductID BETWEEN 700
        AND 800
GROUP BY OrderDate,
    ProductID
ORDER BY Rev DESC;
GO

--This query will also use the above indexed view.
SELECT OrderDate,
    SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
INNER JOIN Sales.SalesOrderHeader AS o
    ON od.SalesOrderID = o.SalesOrderID
        AND o.OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
        AND o.OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
GROUP BY OrderDate
ORDER BY OrderDate ASC;

Terakhir, contoh ini memperlihatkan kueri langsung dari tampilan terindeks. Sebelum Paket Layanan SQL Server 2016 (13.x) 1, penggunaan otomatis tampilan terindeks oleh pengoptimal kueri hanya didukung dalam edisi SQL Server tertentu. Pada edisi Standar SQL Server, Anda harus menggunakan NOEXPAND petunjuk kueri untuk mengkueri tampilan terindeks secara langsung. Karena SQL Server 2016 (13.x) Paket Layanan 1, 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 Petunjuk Tabel (Transact-SQL).

--This query uses the indexed view directly, on Enterprise edition.
SELECT OrderDate, Revenue
FROM Sales.vOrders
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
    AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;

--This query uses the indexed view directly, with the NOEXPAND hint.
SELECT OrderDate, Revenue
FROM Sales.vOrders WITH (NOEXPAND)
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
    AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;

Untuk informasi selengkapnya, lihat CREATE VIEW.

Bagikan melalui

Membuat tampilan terindeks

Langkah-langkah

Opsi SET yang diperlukan untuk tampilan terindeks

Persyaratan tampilan deterministik

Persyaratan tambahan

Rekomendasi untuk datetime dan smalldatetime

Pertimbangan performa dengan tampilan terindeks

Pertimbangan tambahan

Izin

Membuat tampilan terindeks: contoh T-SQL

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

Membuat tampilan terindeks

Langkah-langkah

Opsi SET yang diperlukan untuk tampilan terindeks

Persyaratan tampilan deterministik

Persyaratan tambahan

Rekomendasi untuk datetime dan smalldatetime

Pertimbangan performa dengan tampilan terindeks

Pertimbangan tambahan

Izin

Membuat tampilan terindeks: contoh T-SQL

Konten terkait

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan: