Saturday, August 1, 2015

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

image

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

image

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:

image