Databases Reference
In-Depth Information
So, before making a decision to create an indexed view, it is better to identify the ratio of the
approximate number of
SELECT
statements going to be executed on view to the approximate
number of DML statements going to be run on base table of view. If a small number of
SELECT
statements is being executed and a higher number of DMLs are performed, or base
tables are very volatile and have a very high update ratio, it is not a good idea to create an
indexed view.
Before making any decision, it is advisable to gauge the workload of the database and apply
your expertise to find the selectivity of the query. Generally, a query with aggregation and
many joins, whose base tables are large (maybe millions of rows), and a query taking
time to execute, are good candidates to be in indexed view.
Based on my personal experience, I have observed that Online Analytical
Processing (OLAP), Data Warehouse, Date Mart, and Data Mining get more
benefit from indexed view over Online Transaction Processing (OLTP), because
in most OLTP, there would be a chance of having more DML statements than
SELECT
statements in base tables, and hence each DML statement needs to
update the index on the view along with the indexes on the base table.
Some
SET
options should be set while creating an indexed view. The following is a list of
the same:
F
ARITHABORT
F
CONCAT_NULL_YIELDS_NULL
F
QUOTED_IDENTIFIER
F
ANSI_WARNINGS
F
ANSI_NULLS
F
ANSI_PADDING
F
NUMERIC_ROUNDABORT
NUMERIC_ROUNDABORT
should be set to
OFF
, and all the other options should be set to
ON
.
How to do it...
Follow the steps provided here to perform this recipe:
1.
First, let us create a view with a table from the
AdventureWorks2012
database, by
using the following query:
--Using AdventureWorks2008R2, renamed as AdventureWorks2012,
--database which is compatible with SQL Server Denali and
--freely downloadable, there is no AdventureWorks database
--available for SQL Server 2012 at the moment.
--creating view
Search WWH ::
Custom Search