Maintaining Indexes - Microsoft SQL Server 2012 Performance Tuning - page 246

There's more...
Indexed view is available in every edition of SQL Server 2012. In the Developer and Enterprise
editions of SQL Server 2012, the query processor can use an indexed view to solve queries
that structurally match the view, even if they don't refer to the view by name. In other editions,
we must reference the view by name and use the NOEXPAND hint on the view reference, to
query the contents of an indexed view.
Indexed view must be created with the WITH SCHEMABINDING option, so that any object
referenced in the view cannot be altered in a way that could make the view stop working.
If an indexed view has a GROUP BY clause in its definition, the COUNT_BIG (*) column must
be included in the SELECT list of the view definition, and the view definition cannot specify
HAVING , CUBE , and ROLLUP .
The NOEXPAND hint forces the query optimizer to use the index created on the view rather
than searching for the index created in an underlying table. The NOEXPAND hint can only be
applied if the indexed view is referenced directly in the FROM clause.
Enhancing performance with index on Com-
puted Columns
Before trying to understand what "an index on a Computed Column" is, it is good to have a
basic understanding of what a Computed Column is.
As per MSDN, a Computed Column is computed from an expression that can use other
columns in the same table. The expression can be a non-computed column name, constant,
function, or any combination of these, connected by one or more operators. The expression
cannot be a subquery.
By default, a Computed Column is a virtual column and it is recalculated every time we call it,
until we specify it as PERSISTED in the CREATE TABLE or ALTER TABLE commands.
If a Computed Column is defined as being PERSISTED , it stores the calculated value and
those stored values are updated each time you change the value of the original column.
Moreover, you can't use Computed Column names in INSERT and UPDATE statements.
As it is already proved that an index plays an important role in performance, in the previous
chapter, Chapter 9 , Implementing Index , it is good to know whether it plays any significant role
in all re-calculative values.