Membuat Tampilan Terindeks

  • Artikel
  • 9 menit untuk membaca

Berlaku untuk:yes SQL Server (semua versi yang didukung) YesAzure SQL Database

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 seperti 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 sebagai pengganti.

Langkah-langkah

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

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

Penting

Saat menjalankan DML1 pada tabel yang direferensikan oleh sejumlah besar tampilan terindeks, atau tampilan terindeks yang lebih sedikit tetapi sangat kompleks, tampilan terindeks yang direferensikan juga harus diperbarui. Akibatnya, performa kueri DML dapat diturunkan 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 sesuaikan/sederhanakan pernyataan DML.

1 Seperti operasi UPDATE, DELETE, atau INSERT.

Opsi SET yang Diperlukan untuk tampilan terindeks

Mengevaluasi ekspresi yang sama dapat menghasilkan hasil yang berbeda di Mesin Database saat opsi SET yang berbeda aktif saat kueri dijalankan. Misalnya, setelah opsi CONCAT_NULL_YIELDS_NULL SET diatur ke AKTIF, ekspresi 'abc' + NULL mengembalikan nilai NULL. Namun, setelah CONCAT_NULL_YIELDS_NULL diatur ke NONAKTIF, 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 opsi SET. Opsi SET dalam tabel berikut ini harus diatur ke nilai yang diperlihatkan di kolom Nilai yang Diperlukan setiap kali kondisi berikut ini terjadi:

  • Tampilan dan indeks berikutnya pada tampilan dibuat.
  • Tabel dasar yang direferensikan dalam tampilan pada saat tampilan dibuat.
  • Ada operasi sisipkan, perbarui, atau hapus yang dilakukan pada tabel apa pun yang berpartisipasi dalam tampilan terindeks. Persyaratan ini mencakup operasi seperti penyalinan massal, replikasi, dan kueri terdistribusi.
  • Tampilan terindeks digunakan oleh pengoptimal kueri untuk menghasilkan rencana kueri.
Opsi SET Nilai yang diperlukan Nilai server default Default

Nilai OLE DB dan ODBC		 Default

nilai DB-Library
ANSI_NULLS AKTIF AKTIF AKTIF TIDAK AKTIF
ANSI_PADDING AKTIF AKTIF AKTIF TIDAK AKTIF
ANSI_WARNINGS 1 AKTIF AKTIF AKTIF TIDAK AKTIF
ARITHABORT AKTIF AKTIF TIDAK AKTIF TIDAK AKTIF
CONCAT_NULL_YIELDS_NULL AKTIF AKTIF AKTIF TIDAK AKTIF
NUMERIC_ROUNDABORT TIDAK AKTIF TIDAK AKTIF TIDAK AKTIF TIDAK AKTIF
QUOTED_IDENTIFIER AKTIF AKTIF AKTIF TIDAK AKTIF
         

1 Pengaturan ANSI_WARNINGS ke AKTIF secara implisit ARITHABORT diatur ke AKTIF.

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

Penting

Kami sangat menyarankan Agar Anda mengatur ARITHABORT opsi pengguna ke ON di seluruh server segera setelah tampilan atau indeks terindeks pertama 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 pemilihan, serta WHERE klausa dan GROUP BY , bersifat deterministik. Ekspresi deterministik selalu mengembalikan hasil yang sama setiap kali dievaluasi dengan serangkaian 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 properti IsDeterministic dari fungsi COLUMNPROPERTY . Untuk menentukan apakah kolom deterministik dalam tampilan dengan pengikatan skema tepat, gunakan properti IsPrecise dari COLUMNPROPERTY fungsi. COLUMNPROPERTY mengembalikan 1 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 dapat bergantung pada arsitektur prosesor atau versi mikrokode. Untuk memastikan integritas data, ekspresi tersebut hanya dapat berpartisipasi sebagai kolom non-kunci tampilan terindeks. Ekspresi deterministik yang tidak berisi ekspresi float disebut presisi. 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 opsi SET 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 NONAKTIF (pengaturan default).

  • Tabel harus dirujuk oleh nama dua bagian, skema.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, <skema>.<fungsi>.

  • 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 klausa WHERE dari tampilan atau klausa ON dari operasi JOIN 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 DataAccess ke atribut DataAccessKind.None dan SystemDataAccess ke SystemDataAccessKind.None.
    AKSES EKSTERNAL = TIDAK Properti ini default ke NO untuk rutinitas CLR.
       

  • Tampilan harus dibuat dengan menggunakan WITH SCHEMABINDING opsi .

  • Tampilan hanya harus mereferensikan 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 klausul.

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

    Elemen SQL bertransaksi (lanjutan) (lanjutan)
    COUNT Fungsi ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET, AND OPENXML) OUTER gabungan (LEFT, RIGHT, atau FULL)
    Tabel turunan (ditentukan dengan menentukan SELECT pernyataan dalam FROM klausa) Gabungan mandiri Menentukan kolom dengan menggunakan SELECT * atau SELECT <table_name>.*
    DISTINCT STDEV, STDEVP, VAR, VARP, atau AVG Ekspresi tabel umum (CTE)
    float1, teks, ntext, gambar, XML, atau kolom aliran file Kueri bertumpuk OVER klausa, yang mencakup fungsi jendela peringkat atau agregat
    Predikat teks lengkap (CONTAINS, FREETEXT) SUM fungsi yang mereferensikan ekspresi nullable ORDER BY
    Fungsi agregat yang ditentukan pengguna CLR TOP CUBEoperator , ROLLUP, atau GROUPING SETS
    MIN, MAX UNIONoperator , EXCEPT, atau INTERSECT TABLESAMPLE
    Variabel tabel OUTER APPLY atau CROSS APPLY PIVOT, UNPIVOT
    Kumpulan kolom jarang Fungsi bernilai tabel sebaris (TVF) atau multi-pernyataan (MSTVF) OFFSET
    CHECKSUM_AGG STRING_AGG

    1 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 datetime/smalldatetime

Ketika Anda merujuk ke literal string datetime dan smalldatetime dalam tampilan terindeks, kami sarankan Anda secara eksplisit mengonversi harfiah ke jenis tanggal yang Anda inginkan dengan menggunakan gaya format tanggal deterministik. Untuk daftar gaya format tanggal yang deterministik, lihat CAST dan CONVERT (Transact-SQL). 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

Ketika Anda menjalankan DML (seperti UPDATE, DELETE atau INSERT) pada tabel yang direferensikan oleh sejumlah besar tampilan terindeks, atau tampilan terindeks yang lebih sedikit tetapi sangat kompleks, tampilan terindeks tersebut juga harus diperbarui selama eksekusi DML. Akibatnya, performa kueri DML dapat mengalami penurunan 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 sesuaikan/sederhanakan pernyataan DML.

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

Berbagai pertimbangan tambahan

  • Pengaturan opsi large_value_types_out_of_row kolom dalam tampilan terindeks diwarisi dari pengaturan kolom terkait dalam tabel dasar. Nilai ini diatur dengan menggunakan sp_tableoption. Pengaturan default untuk kolom yang dibentuk dari ekspresi adalah 0. Ini berarti bahwa jenis nilai besar disimpan secara berturut-turut.

  • 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 nonkluster 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 izin CREATE VIEW di database dan mengubah izin pada skema tempat tampilan sedang dibuat. Jika tabel dasar berada dalam skema yang berbeda, izin REFERENSI pada tabel diperlukan minimal. Jika Pengguna yang membuat Indeks berbeda dari Pengguna yang membuat Tampilan, untuk pembuatan Indeks saja izin ALTER pada Tampilan diperlukan (dicakup oleh ALTER pada skema).

Catatan

Indeks hanya dapat dibuat pada tampilan yang memiliki pemilik yang sama dengan tabel atau tabel yang direferensikan. 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 juga dimungkinkan bahwa objek individu dalam skema memiliki pemilik eksplisit yang berbeda. Kolom yang principal_id dalam sys.tables berisi nilai jika pemiliknya berbeda dari pemilik skema.

Membuat tampilan terindeks: contoh T-SQL

Contoh berikut membuat tampilan dan indeks pada tampilan tersebut. Dua kueri disertakan yang menggunakan tampilan terindeks dalam database AdventureWorks.

--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
--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
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 can use the above indexed view.
SELECT OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
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;

Untuk informasi selengkapnya, lihat CREATE VIEW (Transact-SQL).

Lihat juga