Some tips for using indexed views in SQL Server 2016
Consider creating two or more smaller views instead of creating a single large
indexed view.
Sometimes it can greatly improve performance especially if the underlying tables
are located in different databases.
Add an index to a view very carefully.
When the underlying tables of the view are modified, the clustered index, and any
non-clustered indexes of the view are also modified to be always up-to-date when
it is accessed. So, indexes on views, just like indexes on tables, experience
modification overhead. So, you should add an index to a view only if the benefit of
its speed increase when running exceeds the time it takes to update the view’s index.
Avoid creating indexes on a view when the queries that use the view don’t contain
JOINs or aggregations.
In this case, the queries will not take advantages of the view’s indexes and the
queries performance will be the same.
Consider using partition-aligned indexed views.
SQL Server 2016 supports defining indexed views on partitioned data. These views are
called partition-aligned indexed views. By using these views, you can increase the
speed and efficiency of your queries.
Keep the indexes on the views as narrow as possible.
Because each index takes up disk space try to minimize the index key’s size to avoid
using superfluous disk space. This reduces the number of reads required to read the
index and boost overall index performance.
Avoid creating indexes on a view that based on a table with a high percentage
of INSERT, UPDATE and DELETE statements.
Because the index of the view will be updated automatically when the base table will be
updated, you may experience modification overhead and the total modification performance
may be very poor.
Consider using compressed indexed views.
SQL Server 2016 supports on-disk storage compression for indexed views. By using
indexed views compression, you can reduce the total disk storage space.
Don’t create index on column(s) which values has low selectivity.
For example, don’t create an index for columns with many duplicate values, such as
“Sex” column (which has only “Male” and “Female” values) because in this case the
disadvantages of additional space used and slowly rows modification outweigh the
speed advantages of creating a new index.
Convert the literal to the date type you want by using a deterministic date format
style when you refer to datetime and smalldatetime string literals in indexed views.
Keep in mind, that expressions that involve implicit conversion of character strings to
datetime or smalldatetime are considered nondeterministic because the results depend on
the LANGUAGE and DATEFORMAT settings of the server session. You can use CAST or
CONVERT Transact-SQL commands in this case.
Try to create indexes on columns that have integer values rather than character values.
Because the integer values usually have less size than the characters values size (the
size of the int data type is 4 bytes, the size of the bigint data type is 8 bytes), you
can reduce the number of index pages, which are used to store the index keys. This
reduces the number of reads required to read the index and boost overall index performance.
Consider creating index on a view that joins two or more large tables.
In this case, creating appropriate clustered index on a view can greatly improve
performance of the queries based on this view.
If you create a composite (multi-column) index, try to order the columns in the key
as to enhance selectivity, with the most selective columns to the leftmost of the key.
The order of the columns in a composite (multi-column) index is very important. This can
increase the chance the index will be used.
Consider using NOEXPAND hint with the indexed views.
The NOEXPAND hint tells the optimizer to just use the index defined and to not look
at the underlying tables that define the view that has been indexed. This may be
necessary in some circumstances where the indexed view would work faster than letting
the optimizer access the underlying tables.
Use Database Engine Tuning Advisor to help select the indexes to be used in a view.
The Database Engine Tuning Advisor was first introduced in SQL Server 2005 and replaces
the Index Tuning Wizard in previous releases of SQL Server. SQL Server 2016 Database Engine
Tuning Advisor helps to select and create an optimal set of indexes, indexed views, and
partitions. So, you can find tables that are scanned by queries instead of using an index.
You can use Database Engine Tuning Advisor as a graphical user interface (GUI) based tool,
and as a command-line utility (dta.exe). You can use the dta.exe utility when you need
to use Database Engine Tuning Advisor functionality in applications and scripts.