Indexed View - MSSQL Server

Indexed View

Keywords : Pre Join, High Performance, MSSQL , Views, Indexed Views

Reference / Short note Copied from :

http://www.databasejournal.com/features/mssql/article.php/2119721/Indexed-Views-Basics-in-SQL-Server-2000.htm

http://www.databasejournal.com

 What is a VIEW ?

In SQL Server a view represents a virtual table. Just like a real table, a view consists of rows with columns, and you can retrieve data from a view (sometimes even update data in a view). The fields in the view’s virtual table are the fields of one or more real tables in the database.

Benefits of Views

• Restrict access to specific rows in a table
• Restrict access to specific columns in a table
• Join columns from multiple tables and present them as though they are part of a single table
• Present aggregate information (such as the results of the COUNT function)

 Limitations Of Views

http://blog.sqlauthority.com/2010/10/03/sql-server-the-limitations-of-the-views-eleven-and-more/

 

What is an Indexed View?

An indexed view is a view that has a unique clustered index created on it. Normally views do not exist on disk as rows. That changes for indexed views, which exist in the database as rows that realize the view. There can also be non-clustered indexes on the view so long as it has the unique clustered index.

Since the indexed view exists on disk there are two types of overhead added to the database:

• The disk space taken up by the view
• The cost of maintaining the view on disk as the base tables are modified.

Both of these costs can be substantial and limit the usefulness of indexed views to situations where there is a high ratio of data retrieval operations to data modifications. They are best for decision support and analytic applications where the frequency of data modification is low.

Why use them?

When the circumstances are right, there is one powerful reason for using them: performance of data retrieval operations.

The performance gain during the query comes from one of two factors:

• Storing a join.
• Performing grouping and aggregations in advance of running the query.

It is even possible to combine aggregation and join operations in a single indexed view.

Restrictions on Indexing Views

There are restrictions on which base tables may be included in the view, which views are eligible for indexing, on the columns that can be in the view, and on which columns in those the view can be part of the index. It is also important to manage the database options that are in effect any time the view or its base tables are referenced by the database.

Indexed views require that a consistent set of session options exists during three time periods:

• When the indexed view is created
• When any INSERT, UPDATE, or DELETE operation is performed on a base table of the view.
• When the indexed view is used by the optimizer to produce a query plan

SET ANSI_NULLS ON

GO

 

SET ANSI_PADDING ON

GO

SET ARITHABORT ON

GO

SET NUMERIC_ROUNDABORT OFF

GO

SET QUOTED_IDENTIFIER ON

GO

SET CONCAT_NULL_YEILDS_NULL ON

GO

SET ANSI_WARNING ON

GO

For me ANSI_WARNING and CONTACT_NULL_YEILDS_NULL didn't work

Restrictions on Base Tables

For starters, base tables of the view must be in the same database as the view. In addition, if there are computed fields in the base table that are reference by the view, a special condition applies: the value of ANSI_NULLS and QUOTED_IDENTIFIER must have the correct option when the base table is created. The reason they must be set when the base table is created, as opposed to at runtime, is that they are both parse time options. That is, the table retains the value that was set when the table was created. If the wrong options are in effect when the base tables referenced by an indexed view are created, the view cannot be created with SCHEMABINDING, which is a requirement for the view. Ordinary columns that are not computed are not affected by this requirement.

Restrictions on the View to be Indexed

When the views to be indexed is created it:

• Must be created the WITH SCHEMABINDING view option
• May only refer to base tables in the same database.
• If there is a GROUP BY clause, the view may not have a HAVING, CUBE, or ROLLUP.
• May not have an OUTER JOIN clause.
• May not have a UNION.
• May not have DISTINCT or TOP clauses
• May not have full-text predicates such as CONATINSTABLE
• May not have a ROWSET function such as OPENROWSET
• May not use derived tables or subqueries.
• Must be created with ANSI_NULLS ON and QUOTED_IDENTIFIER ON

The SCHEMABINDING requirement adds additional requirements, such as the use of a two-part name for all base tables.

-- Check index eligibility for 'Order Details%' tables and views
SELECT TABLE_TYPE, TABLE_NAME
    , OBJECTPROPERTY (OBJECT_ID(TABLE_NAME), 'IsIndexable')
                         AS IsIndexable
  FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME LIKE ('[yourView_Name]')
        OR TABLE_NAME = '[yourView_Name]'
GO

This gives the output as the table is indexable or Not..

Restrictions on Columns in the View

The columns that can be in the SELECT list or WHERE clause of an indexed view are restricted. There are two possibilities based on the query: GROUP BY or no GROUP BY. If there is no group by, the columns may not contain expressions. However, they can contain computed fields in base tables and user-defined functions. On the other hand, if there is a GROUP BY clause, the following restrictions apply:

• These aggregate functions may not be used: COUNT(*), STDEV, VARIANCE, AVG, MIN, MAX
• No SUM aggregate function on a NULLable expression.
• The view must contain a COUNT_BIG(*) expression

If these conditions are satisfied, it is possible to create an index on the view. However, not all columns in the view are eligible for indexing.

To give you a quick rundown about which columns of a view can be included in an index I have put together the user-defined function udf_View_ColumnIndexableTAB. Here is the CREATE FUNCTION script for it.

/* function udf_View_ColumnIndexableTAB  starts here*/

SET QUOTED_IDENTIFERS ON
SET ANSI_NULLS ON
GO

CREATE  FUNCTION udf_View_ColumnIndexableTAB (

   @view_name_pattern sysname = NULL -- View name or pattern to
                  -- search for. NULL for all
 , @col_name_pattern sysname = NULL -- Column name or pattern to
                  -- search for. NULL for all
) RETURNS TABLE
/*
* Returns a table of the columns in views whose name match the
* patterns in the parameters and the status of the columns as
* indexable, deterministic, and precise.
*
* Example:
SELECT * FROM udf_View_ColumnIndexableTAB(NULL, NULL)
*
* ) Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published in the UDF of the Week Newsletter Vol 1 Number 19
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS RETURN
SELECT TOP 100 PERCENT WITH TIES
      C.TABLE_SCHEMA AS [Owner]
    , C.TABLE_NAME AS [VIEW_NAME]
    , COLUMN_NAME
    , ORDINAL_POSITION
    , dbo.udf_SQL_DataTypeString (C.DATA_TYPE
                                , C.CHARACTER_MAXIMUM_LENGTH
                                , C.NUMERIC_PRECISION
                                , C.NUMERIC_SCALE) AS DATA_TYPE
    , CASE WHEN 1=COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME)
                         , COLUMN_NAME, 'IsIndexable')
           THEN 'YES' ELSE 'NO' END as IsIndexable
    , CASE WHEN 1=COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME)
                    , COLUMN_NAME, 'IsDeterministic')
           THEN 'YES' ELSE 'NO' END as IsDeterministic
    , CASE WHEN 1=COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME)
                         , COLUMN_NAME, 'IsPrecise')
           THEN 'YES' ELSE 'NO' END as IsPrecise
   , IS_NULLABLE
   FROM INFORMATION_SCHEMA.[COLUMNS] C
      INNER JOIN INFORMATION_SCHEMA.TABLES T
          ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
           AND C.TABLE_NAME = T.TABLE_NAME
   WHERE T.TABLE_TYPE='VIEW'
     AND (@view_name_pattern is NULL -- all tables
          OR C.Table_Name LIKE @view_name_pattern)
     AND (@col_name_pattern is NULL -- all columns
          OR [Column_Name] Like @col_name_pattern)
   ORDER BY C.TABLE_NAME
          , C.ORDINAL_POSITION

It uses a function defined in the following link:

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-18-udf_SQL_DatatypeString.htm

Function Definition :

/* function udf_SQL_DataTypeString  starts here*/

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

GO

CREATE    FUNCTION dbo.udf_SQL_DataTypeString (

  @BaseDataType nvarchar(128) -- base name like int, numeric

, @Character_Maximum_Length int

, @Numeric_Precision int

, @Numeric_Scale int

)  RETURNS nvarchar(24) -- Data type name like 'numeric (15, 3)'

 WITH SCHEMABINDING

AS BEGIN

RETURN LTRIM(RTRIM(@BaseDataType))

    + CASE WHEN @BaseDataType in ('char', 'varchar', 'nvarchar', 'nchar')

            THEN '('

                + CONVERT (varchar(4), @Character_Maximum_Length)

                + ')'

          WHEN @BaseDataType in ('numeric', 'decimal')

          THEN '('

              + Convert(varchar(4), @Numeric_Precision)

              + ' ' + convert(varchar(4), @Numeric_scale)

              + ')'

          ELSE '' -- empty string

          END

END

GRANT EXEC, REFERENCES ON dbo.udf_SQL_DataTypeString to

[PUBLIC] GO

SELECT DISTINCT TOP 15

     COLUMN_NAME as [Column]

   , dbo.udf_SQL_DataTypeString (Data_Type

         , Character_Maximum_Length, Numeric_Precision

         , Numeric_Scale) AS [Data Type]

  FROM INFORMATION_SCHEMA.COLUMNS

The function's two parameters are patterns that work with the LIKE operator. The first parameter is on the view name. The second parameter is on the column name. LIKE operator, patterns are used instead of the names to give more flexibility to the UDF. The UDF can be queried to check the index eligibility of columns in our indexable view, OrderDetailsXSB:

-- Are columns in "OrderDetailsXSB" eligible for indexing?
SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
    , ISIndexable, IsDeterministic, IsPrecise
   FROM udf_View_ColumnIndexableTAB('[yourView_Name]XSB', NULL)
GO

Creating the Index

Indexes on views are created in the same way that indexes on tables are with the CREATE INDEX statement. Here is one to create a unique clustered index on OrderDetailsXSB that makes it an indexed view:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE UNIQUE CLUSTERED INDEX [IDX_Order_Details_X]
      ON OrderDetailsXSB (OrderID, ProductID
                        , ProductName, Quantity)
GO

Finally RUN

SELECT [yourView_Name], Column_Name as [Column]

   , Ordinal_Position as Pos, DATA_TYPE

   , IsIndexable as Indexable, IsDeterministic as Deterministic

   , IsPrecise as IsPrecise, Is_Nullable as [Nullable]

  FROM udf_View_ColumnIndexableTAB('[yourView_Name]', null)

GO

This will not be a good note for u, please refer

http://www.databasejournal.com/features/mssql/article.php/2119721/Indexed-Views-Basics-in-SQL-Server-2000.htm

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-19-udf_View_ColumnsIndexableTAB.htm

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-18-udf_SQL_DatatypeString.htm