Membuat tampilan terindeks

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure 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:

  1. Verifikasi 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 UPDATEoperasi , DELETE atau INSERT (Bahasa Manipulasi Data, atau DML) 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 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 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 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 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 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 pemilihan, serta WHERE klausa dan GROUP BY , bersifat deterministik. Ekspresi deterministik selalu mengembalikan hasil yang sama kapan saja 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 dapat bergantung 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, 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 ke DataAccessDataAccessKind.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 Gunakan COUNT_BIG
    Fungsi ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET, dan OPENXML)
    Rata-rata aritmatika AVG Gunakan COUNT_BIG dan SUM sebagai kolom terpisah
    Fungsi agregat statistik (STDEV, , STDEVP, VARdan 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 Gunakan 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 ALL, EXCEPT, INTERSECT Gunakan OR, AND NOT, dan AND dalam WHERE klausul masing-masing
    ORDER BY ORDER BY
    ORDER BY OFFSET
    Jenis kolom sumber Kemungkinan alternatif
    Teks tipe kolom nilai besar, ntext, dan gambar yang tidak digunakan lagi Migrasikan kolom ke varchar(max), nvarchar(max), dan varbinary(max) masing-masing.
    kolom xml atau FILESTREAM
    float1 kolom dalam kunci indeks
    Kumpulan kolom jarang

    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 tanggalwaktu 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 (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

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 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 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 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 UBAH 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. 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 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
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
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 SQL Server 2016 (13.x) Paket Layanan 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 (Transact-SQL).

Langkah berikutnya