Sample on optimizing SQL Server queries using indexed view

 

Sample DW query:

SELECT d1.EnglishEducation AS [Customer Education Level], CASE    WHEN d1.HouseOwnerFlag = 0 THEN 'No'    ELSE 'Yes' END AS [House Owner], COUNT(*) AS [Internet Order Quantity], ROUND(SUM(f1.SalesAmount),2) AS [Internet Sales Amount] FROM [AdventureWorksDW2012].[dbo].[FactInternetSales] f1 INNER JOIN [AdventureWorksDW2012].[dbo].[DimCustomer] d1    on f1.CustomerKey=d1.CustomerKey INNER JOIN [AdventureWorksDW2012].[dbo].[DimGeography] d2    on d1.GeographyKey=d2.GeographyKey WHERE year(f1.ShipDate)>=2005 and year(f1.ShipDate)<=2007 AND  d2.EnglishCountryRegionName<>'United States' GROUP BY d1.EnglishEducation, d1.HouseOwnerFlag ORDER BY d1.EnglishEducation, d1.HouseOwnerFlag GO

Create indexed View:

if object_id('VW_EnglishEducation') is not null     drop view VW_EnglishEducation GO create view VW_EnglishEducation with schemabinding as SELECT     d1.EnglishEducation,     d1.HouseOwnerFlag,     f1.SalesAmount,     f1.SalesOrderNumber,     f1.SalesOrderLineNumber FROM [dbo].[FactInternetSales] f1 INNER JOIN [dbo].[DimCustomer] d1    on f1.CustomerKey=d1.CustomerKey INNER JOIN [dbo].[DimGeography] d2    on d1.GeographyKey=d2.GeographyKey WHERE year(f1.ShipDate)>=2005 and year(f1.ShipDate)<=2007 AND  d2.EnglishCountryRegionName<>'United States' GO create unique clustered index VW_EnglishEducationon_Idx on VW_EnglishEducation (SalesOrderNumber,SalesOrderLineNumber) GO

Limitations and Restrictions

• The definition of an indexed view must be deterministic.

• The user that executes CREATE INDEX must be the owner of the view.

• When you create the index, the IGNORE_DUP_KEY option must be set to OFF (the default setting).

• Tables must be referenced by two-part names, schema.tablename in the view definition.

• User-defined functions referenced in the view must be created by using the WITH SCHEMABINDING option.

• Any user-defined functions referenced in the view must be referenced by two-part names, schema.function.

• The data access property of a user-defined function must be NO SQL, and external access property must be NO.

• Common language runtime (CLR) functions can appear in the select list of the view, but cannot be part of the definition of the clustered index key. CLR functions cannot appear in the WHERE clause of the view or the ON clause of a JOIN operation in the view.

• The view must be created by using the WITH SCHEMABINDING option.

• The view must reference only base tables that are in the same database as the view. The view cannot reference other views.

• If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. These GROUP BY restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.

• If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.

• The SELECT statement in the view definition must not contain the following Transact-SQL elements:

  COUNT	ROWSET functions (OPENDATASOURCE, OPENQUERY, OPENROWSET, AND OPENXML)	OUTER joins (LEFT, RIGHT, or FULL)
  Derived table (defined by specifying a SELECT statement in the FROM clause)	Self-joins	Specifying columns by using SELECT * or SELECT table_name.*
  DISTINCT	STDEV, STDEVP, VAR, VARP, or AVG	Common table expression (CTE)
  float*, text, ntext, image, XML, or filestream columns	Subquery	OVER clause, which includes ranking or aggregate window functions
  Full-text predicates (CONTAIN, FREETEXT)	SUM function that references a nullable expression	ORDER BY
  CLR user-defined aggregate function	TOP	CUBE, ROLLUP, or GROUPING SETS operators
  MIN, MAX	UNION, EXCEPT, or INTERSECT operators	TABLESAMPLE
  Table variables	OUTER APPLY or CROSS APPLY	PIVOT, UNPIVOT
  Sparse column sets	Inline or multi-statement table-valued functions	OFFSET
  CHECKSUM_AGG

  *The indexed view can contain float columns; however, such columns cannot be included in the clustered index key.

What is about the warning icon: