Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - A new trainee was confused when he discovered that organization is discouraging the Indexed View

  • A new trainee was confused when he discovered that organization is discouraging the Indexed View (or materialized views). After talking to senior team members he realized that Index Views which are good at certain tasks it comes with lots of restrictions. What are the restrictions of using INDEXED VIEWS inside SQL Server?

    Posted on 01-07-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

10  Answers  

Subscribe to Notifications
  • Score
    8

    Views allow you to create a virtual table by defining a query against one or more tables. With a standard view, the result is not stored in the database. Instead, the result set is determined at the time a query utilizing that view is executed. Creating a unique clustered index on a view changes it to an indexed view.

    In the developer and enterprise editions of SQL Server, the optimizer can use the indexes of views to optimize queries that do not specify the indexed view. In the other editions of SQL Server, however, the query must include the indexed view and specify the hint NOEXPAND to get the benefit of the index on the view.If your queries could benefit from having more than one index on the view, non-clustered indexes can also be created on the view.

    Restrictions/Limitations

    An index cannot be created on just any view. Several constraints exist that a view must meet in order for the index creation to be successful. We discussed WITH SCHEMABINDING and two-part tablenames above. Here are some other constraints.

    The view must have been created with certain SET options, such as QUOTEDIDENTIFIER and CONCATNULLYIELDSNULL set to ON.

    The ANSI_NULLS option also must be turned on when the view is created. Following SP will turn those options on.

    The session creating the index must also have the correct SET options.

    Any user-defined functions referenced by the view must have been created using WITH SCHEMABINDING.

    The view must be deterministic (consistently providing the same result given the same input).

    The base tables must have been created with the proper ANSI_NULLS setting.

    The result set of the view is physically stored in the database, thus storage space for the clustered index is also a constraint to consider.

    All the tables referenced by the view must be in the same database as the view.

    All the tables referenced by the view must have the same owner as the view.

    Indexed view must be created with the SCHEMABINDING option. This option prohibits the schema of the base tables from being changed (adding or dropping a column, for instance). To change the tables, Indexed view must be dropped.

    A column can not be referenced twice in the SELECT statement unless all references, or all but one reference, to the column are made in a complex expression.

    You can’t use ROWSET, UNION, TOP, ORDER BY, DISTINCT, COUNT(*), COMPUTE, or COMPUTE BY.

    The AVG, MAX, MIN, STDEV, STDEVP, VAR, and VARP aggregate functions aren’t allowed in the SELECT statement.

    A SUM() that references a nullable expression isn’t allowed.

    CONTAINS and FREETEXT aren’t allowed in the SELECT statement.

    If you use GROUP BY, you can’t use HAVING, ROLLUP, or CUBE, and you must use COUNT_BIG() in the select list.

    You can’t modify more than one table at a time through a view.

    If your view is based on aggregate functions, you can’t use it to modify data.

    If your view is based on a table that contains fields that don’t allow null values yet your view doesn’t display those fields, then you won’t be able to insert new data.

    Apart, Indexed view design should consider the cost of the indexed view. If the query is not being used the indexes created on view it will pay the cost for its maintenance and space. This might even end up with deterimental effect on the performance of the system.

    Replied on Jan 7 2012 12:16AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    7

    Indexed View Background:

    Views are also known as virtual tables because the result set returned by the view has the same general form as a table with columns and rows, and views can be referenced just like tables in SQL statements. The result set of a standard view is not stored permanently in the database. Every time a query references a standard view, SQL Server substitutes the definition of the view into the query internally until a modified query is formed that only references base tables

    From DBMS perspective, a view is a description of the data, a form of metadata. It is possible to create a unique clustered index on a view, as well as non-clustered indexes, to improve data access performance on the most complex queries. In SQL Server 2000 and 2005, a view that has a unique clustered index is referred to as an indexed view. The main benefit of using index view is that the query optimizer searches though a set of possible execution plans for a query, and chooses the lowest-cost plan it can find, based on estimates of the actual time it will take to execute each query plan.

    In the case of a non-indexed view, the portions of the view necessary to solve the query are materialized at run time. Any computations such as joins or aggregations are done during query execution for each query referencing the view. After a unique clustered index is created on the view, the view's result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.

    The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or the entire query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.

    Disadvantages of Indexed View:

    To use an indexed view in all other editions, the NOEXPAND table hint must be used. Indexed views work best when the underlying data is infrequently updated. The maintenance of an indexed view can be greater than the cost of maintaining a table index. If the underlying data is updated frequently, the cost of maintaining the indexed view data may outweigh the performance benefits of using the indexed view.

    Another cost of indexed views is that the data is actually stored. By applying the clustered index we are creating a copy of the data. So if we have several indexed views on a single table, we will have several copies of the data, along with the statistics of the data and the indexes and all of the overhead that goes with it.

    Following are the disadvantages using index views that do not improve the performance:

    * OLTP systems that have many writes to the disk.
    * Databases that have many updates.
    * Queries that do not involve aggregations or joins.
    * High numbers of updates will most likely kill the benefit. If it is mainly reads then it will work fine.
    * The overuse of indexed views can be like winding a watch with a wrench.
    * Online index rebuilds are not supported for indexed views.
    

    Restrictions of using INDEXED VIEWS inside SQL Server

    1) The ANSINULLS and QUOTEDIDENTIFIER options must be turned on when the view is created. Following SP will turn those options on.

    spdboption 'ANSINULLS', TRUE spdboption 'QUOTEDIDENTIFIER', TRUE

    2) The ANSI_NULLS option must have been turned on during the creation of all the tables that are referenced by the view.

    3) All the tables referenced by the view must be in the same database as the view.

    4) All the tables referenced by the view must have the same owner as the view.

    5) Indexed view must be created with the SCHEMABINDING option. This option prohibits the schema of the base tables from being changed (adding or dropping a column, for instance). To change the tables, Indexed view must be dropped.

    6) Any user-defined functions referenced in the view must have been created with the SCHEMABINDING option as well.

    7) Any functions referenced in an indexed view must be deterministic; deterministic functions return the same value each time they’re invoked with the same arguments.

    8) A column can not be referenced twice in the SELECT statement unless all references, or all but one reference, to the column are made in a complex expression.

    Illegal: SELECT qty, orderid, qty Legal: SELECT qty, orderid, SUM(qty)

    9)You can’t use ROWSET, UNION, TOP, ORDER BY, DISTINCT, COUNT(*), COMPUTE, or COMPUTE BY.

    The AVG, MAX, MIN, STDEV, STDEVP, VAR, and VARP aggregate functions aren’t allowed in the SELECT statement.

    A SUM() that references a nullable expression isn’t allowed.

    10) CONTAINS and FREETEXT aren’t allowed in the SELECT statement.

    11) If you use GROUP BY, you can’t use HAVING, ROLLUP, or CUBE, and you must use COUNT_BIG() in the select list.

    12) You can’t modify more than one table at a time through a view.

    13) If your view is based on aggregate functions, you can’t use it to modify data.

    14) If your view is based on a table that contains fields that don’t allow null values yet your view doesn’t display those fields, then you won’t be able to insert new data.

    Following are few links which are studied by me:

    *Study Ref. Click 1 ::

    Study Ref. Click 2 ::

    SQL 2008 Microsoft Click 3::

    Limitation of indexed Views Click 4 ::*

    Thanks

    Yogesh K

    Replied on Jan 7 2012 12:56AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    7

    An indexed view is the same thing as a normal view, however the critical difference is that an indexed view actually allows you to create a clustered index on it.Creating a clustered index on a view stores the data as it exists at the time the index is created. An indexed view also automatically reflects modifications made to the data in the base tables after the index is created, the same way an index created on a base table does. As modifications are made to the data in the base tables, the data modifications are also reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification.

    CREATE TABLE MyTable(
    ItemID            INT PRIMARY KEY, 
    ItemDsc           VARCHAR(20),
    QTY               INT)
    GO
    
    CREATE VIEW MyView WITH SCHEMABINDING AS
    SELECT ItemID, QTY
      FROM dbo.MyTable
     WHERE QTY > 20
    GO
    CREATE UNIQUE CLUSTERED INDEX idx_MyView ON MyView(QTY)
    

    Following are the disadvantages using index views that do not improve the performance:

    OLTP systems that have many writes to the disk.
    Databases that have many updates.
    Queries that do not involve aggregations or joins.
    High numbers of updates will most likely kill the benefit. If it is mainly reads then it will work fine.
    The overuse of indexed views can be like winding a watch with a wrench.
    Online index rebuilds are not supported for indexed views.
    

    Limitation of view

    1. The view must have been created with certain SET options, such as QUOTEDIDENTIFIER and CONCATNULLYIELDSNULL set to ON.
    2. The session creating the index must also have the correct SET options.
    3. The view must not reference any other views, only base tables.
    4. All base tables referenced by the view must be in the same database as the view and have the same owner as the view.
    5. Any user-defined functions referenced by the view must have been created using WITH SCHEMABINDING.
    6. The view must be deterministic (consistently providing the same result given the same input).
    7. The base tables must have been created with the proper ANSI_NULLS setting.
    8. The result set of the view is physically stored in the database, thus storage space for the clustered index is also a constraint to consider. So if we have several indexed views on a single table, we will have several copies of the data, along with the statistics of the data and the indexes and all of the overhead that goes with it.
    9. To use an indexed view in all other editions, the NOEXPAND table hint must be used.
    10. The maintenance of an indexed view can be greater than the cost of maintaining a table index. If the underlying data is updated frequently, the cost of maintaining the indexed view data may outweigh the performance benefits of using the indexed view.
    11. Cross Database Query Not Allowed
    12. Schema name must be mentioned before creating view
    13. All Views must be Schema bound if used in your Indexed View
    14. Indexed View cannot be Altered. You cannot drop a View if it is using in any other Indexed View
    15. Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.
    16. 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.
    17. The first index on a View must be a clustered, UNIQUE index.
    18. SELF JOIN Not Allowed in Indexed View
    19. View Over the View Not Possible with Index View
    20. Order by does not work
    21. Adding Column is Expensive by Joining Table Outside View

    keywords which are not allowed in the indexed view

    ANY, NOT ANY
    Arithmetic on imprecise (float, real) values
    COMPUTE, COMPUTE BY
    Contradictions SQL Server can detect that mean the view would be empty (for example, where 0=1 and …)
    CONVERT producing an imprecise result
    COUNT(*)
    Derived tables (subquery in FROM list)
    DISTINCT
    EXISTS, NOT EXISTS
    Expressions on aggregate results (for example, SUM(x)+SUM(x))
    Full-text predicates (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
    GROUP BY ALL
    Imprecise constants (for example, 2.34e5)
    Inline or table-valued functions
    MIN, MAX
    Nondeterministic expressions
    Non-Unicode collations
    OPENROWSET, OPENQUERY, OPENDATASOURCE
    OPENXML
    ORDER BY
    OUTER join
    References to a base table with a disabled clustered index
    References to a table or function in a different database
    References to another view
    ROWSET functions
    Self-joins
    STDEV, STDEVP, VAR, VARP, AVG
    Subqueries
    SUM on nullable expressions
    Table hints (for example, NOLOCK)
    text, ntext, image, filestream, or xml columns
    TOP
    UNION
    

    http://msdn.microsoft.com/en-us/library/ms191432.aspx

    http://www.databasejournal.com/features/mssql/article.php/3867651/SQL-Server-Indexed-Views.htm

    http://blog.sqlauthority.com/2010/09/27/sql-server-keywords-view-definition-must-not-contain-for-indexed-view-limitation-of-the-view-10

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

    Replied on Jan 7 2012 3:44AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    3

    1.You cannot create a SCHEMABINDING view calling a query from other database. 2.* Cannot be Used in Query of an Indexed View. 3.While creating Indexed view you have to mention schema name before table name. 4.If you are creating a view using another view as your From clause then you have to re-create your already created view as SCHEMABINDING view.

    Replied on Jan 7 2012 8:27PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    5

    An indexed view is a view that has been materialized. This means it has been computed and stored. You index a view by creating a unique clustered index on it. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated.

    A view must meet the following requirements before you can create a clustered index on it:

    • The ANSINULLS and QUOTEDIDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.

    • The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.

    • The view must not reference any other views, only base tables.

    • All base tables referenced by the view must be in the same database as the view and have the same owner as the view.

    • The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.

    • User-defined functions referenced in the view must have been created with the SCHEMABINDING option.

    • Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.

    • All functions referenced by expressions in the view must be deterministic. The IsDeterministic property of the OBJECTPROPERTY function reports whether a user-defined function is deterministic.

    • If the view definition uses an aggregate function, the SELECT list must also include COUNT_BIG (*).

    • 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.

    • If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, ROLLUP, CUBE, or GROUPING SETS.

    • The SELECT statement in the view cannot contain the following Transact-SQL syntax elements:

      • The * or table_name.* syntax to specify columns. Column names must be explicitly stated.
      • An expression on a column used in the GROUP BY clause, or an expression on the results of an aggregate.
      • A derived table.
      • A common table expression (CTE).
      • Rowset functions.
      • UNION, EXCEPT or INTERSECT operators.
      • Subqueries.
      • Outer or self joins.
      • TOP clause.
      • ORDER BY clause.
      • DISTINCT keyword.
      • A SUM function that references a nullable expression.
      • The OVER clause, which includes ranking or aggregate window functions.
      • A CLR user-defined aggregate function.
      • The full-text predicates CONTAINS or FREETEXT.
      • COMPUTE or COMPUTE BY clause.
      • The CROSS APPLY or OUTER APPLY operators.
      • The PIVOT or UNPIVOT operators
      • Table hints (applies to compatibility level of 90 or higher only).
      • Join hints.
      • Direct references to Xquery expressions. Indirect references, such as Xquery expressions inside a schema-bound user-defined function, are acceptable.
    Replied on Jan 8 2012 10:24PM  . 
    Hardik Doshi
    20 · 9% · 2863
  • Score
    6

    To create an index on a view in SQL Server 2005, the view definition must not contain:

    • ANY, NOT ANY
    • OPENROWSET, OPENQUERY, OPENDATASOURCE
    • arithmetic on imprecise (float, real) values
    • OPENXML
    • COMPUTE, COMPUTE BY
    • ORDER BY
    • CONVERT producing an imprecise result
    • OUTER join
    • COUNT(*)
    • reference to a base table with a disabled clustered index
    • GROUP BY ALL
    • reference to a table or function in a different database
    • The indexed view may contain float and real columns; however, such columns cannot be included in the clustered index key if they are non-persisted
    • computed columns.
    • Derived table (subquery in FROM list) reference to another view
    • DISTINCT
    • ROWSET function
    • EXISTS, NOT EXISTS
    • self-join
    • expressions on aggregate results (e.g. SUM(x)+SUM(x))
    • STDEV, STDEVP, VAR, VARP, AVG
    • full-text predicates (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
    • Subquery
    • imprecise constants (e.g. 2.34e5)
    • SUM on nullable expressions
    • inline or table-valued functions
    • table hints (e.g. NOLOCK)
    • MIN, MAX
    • text, ntext, image, filestream, or XML columns
    • non-deterministic expressions
    • TOP
    • non-unicode collations
    • UNION

    The indexed view may contain float and real columns; however, such columns cannot be included in the clustered index key if they are non-persisted computed columns.

    GROUP BY Restrictions

    If GROUP BY is present, the VIEW definition:

    • Must contain COUNT_BIG(*).
    • Must not contain HAVING, CUBE, ROLLUP, or GROUPING()

    These 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

    Index Requirements

    The user executing the CREATE INDEX statement must be the view owner. 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 index must not be created with the IGNOREDUPKEY option enabled.

    Index Views will not help in the following cases;

    • OLTP systems that have many writes to the disk.
    • Databases that have many updates.
    • Queries that do not involve aggregations or joins.
    • High numbers of updates will most likely kill the benefit. If it is mainly reads then it will work fine.
    • The overuse of indexed views can be like winding a watch with a wrench.
    • Online index rebuilds are not supported for indexed views.
    Replied on Jan 11 2012 6:44AM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
  • Score
    7

    Indexed view is a virtual table which represents an output of a select statement. In general when we create a view the view does not store any data. So when we query a view it queries the underlying base table. But when we create indexed views, result set is persisted on the hard disk.

    Limitations Of Indexed View

    • Outer joins are NOT allowed in the indexed views. Rows can logically disappear from an Indexed View based on OUTER JOIN when you insert data into a base table. This makes the OUTER JOIN view to be increasingly updated, which is relatively difficult to implement. In addition, the performance of the implementation would be slower than for views based on INNER JOIN.
    • The view must use the SCHEMABINDING option, which prevents the underlying tables from changing in a database. If any tables are changed, the view must be dropped before the change and re-added after the change. This must happen, for example, if a field is changed from an 'int' to a 'bigint'.
    • Self Join is not allowed in the Index View. it is very expensive to manage the view for SQL Server when SELF JOIN is implemented in the query.
    • The view must not reference any other views, only base tables.
    • All base tables referenced by the view must be in the same database as the view and have the same owner as the view.
    • User-defined functions referenced in the view must have been created with the SCHEMABINDING option.
    • Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.
    • All functions referenced by expressions in the view must be deterministic. The IsDeterministic property of the OBJECTPROPERTY function reports whether a user-defined function is deterministic.
    • The ANSINULLS and QUOTEDIDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.
    • The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.
    • An expression on a column used in the GROUP BY clause, or an expression on the results of an aggregate.
    • The following SET options must be set to ON when the CREATE INDEX statement is executed: ANSINULLS,ANSIPADDING,ANSIWARNINGS,CONCATNULLYIELDSNULL,QUOTED_I DENTIFIER
    • The NUMERIC_ROUNDABORT option must be set to OFF. This is the default setting.
    • If the database is running in 80 compatibility mode or earlier, the ARITHABORT option must be set to ON.
    • When you create a clustered or nonclustered index, the IGNOREDUPKEY option must be set to OFF (the default setting).
    • The view cannot include text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement.
    • If the SELECT statement in the view definition specifies a GROUP BY clause, the key of the unique clustered index can reference only columns specified in the GROUP BY clause.
    • An imprecise expression that forms the value of an index key column must reference a stored column in a base table underlying the view. This column may be a regular stored column or a persisted computed column. No other imprecise expressions can be part of the key column of an indexed view.

    Other things which must not contain in the Index view definition are,

    • ANY, NOT ANY
    • Arithmetic on imprecise (float, real) values
    • COMPUTE, COMPUTE BY
    • ORDER BY
    • Contradictions SQL Server can detect that mean the view would be empty.
    • CONVERT producing an imprecise result
    • COUNT(*)
    • Derived tables (subquery in FROM list)
    • DISTINCT
    • EXISTS, NOT EXISTS
    • A common table expression (CTE).
    • UNION, EXCEPT or INTERSECT operators.
    • The OVER clause, which includes ranking or aggregate window functions.
    • The full-text predicates CONTAINS or FREETEXT.
    • The CROSS APPLY or OUTER APPLY operators.
    • The PIVOT or UNPIVOT operators
    • Table hints (applies to compatibility level of 90 or higher only).
    • Join hints.
    • Direct references to Xquery expressions. Indirect references, such as Xquery expressions inside a schema-bound user-defined function, are acceptable.
    • Expressions on aggregate results (for example, SUM(x)+SUM(x))
    • GROUP BY ALL
    • Imprecise constants (for example, 2.34e5)
    • Inline or table-valued functions
    • Nondeterministic expressions
    • Non-Unicode collations
    • OPENROWSET, OPENQUERY, OPENDATASOURCE
    • OPENXML
    • References to a base table with a disabled clustered index
    • ROWSET functions
    • STDEV, STDEVP, VAR, VARP, AVG
    • SUM on nullable expressions
    • text, ntext, image, filestream, or xml columns
    • TOP

    Reference

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

    http://msdn.microsoft.com/en-us/library/ms191432.aspx

    http://dba-basics.blogspot.com/2007/07/introduction-to-indexed-views.html

    Replied on Jan 12 2012 4:46AM  . 
    Sineetha
    106 · 2% · 492
  • Score
    5

    An indexed view is the same thing as a normal view, however the critical difference is that an indexed view actually allows you to create a clustered index on it.Creating a clustered index on a view stores the data as it exists at the time the index is created. An indexed view also automatically reflects modifications made to the data in the base tables after the index is created, the same way an index created on a base table does. As modifications are made to the data in the base tables, the data modifications are also reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification.

    CREATE TABLE MyTable(
    ItemID            INT PRIMARY KEY, 
    ItemDsc           VARCHAR(20),
    QTY               INT)
    GO
    
    CREATE VIEW MyView WITH SCHEMABINDING AS
    SELECT ItemID, QTY
      FROM dbo.MyTable
     WHERE QTY > 20
    GO
    CREATE UNIQUE CLUSTERED INDEX idx_MyView ON MyView(QTY)
    

    Following are the disadvantages using index views that do not improve the performance:

    OLTP systems that have many writes to the disk.
    Databases that have many updates.
    Queries that do not involve aggregations or joins.
    High numbers of updates will most likely kill the benefit. If it is mainly reads then it will work fine.
    The overuse of indexed views can be like winding a watch with a wrench.
    Online index rebuilds are not supported for indexed views.
    
    1. The view must have been created with certain SET options, such as QUOTEDIDENTIFIER and CONCATNULLYIELDSNULL set to ON.
    2. The session creating the index must also have the correct SET options.
    3. The view must not reference any other views, only base tables.
    4. All base tables referenced by the view must be in the same database as the view and have the same owner as the view.
    5. Any user-defined functions referenced by the view must have been created using WITH SCHEMABINDING.
    6. The view must be deterministic (consistently providing the same result given the same input).
    7. The base tables must have been created with the proper ANSI_NULLS setting.
    8. The result set of the view is physically stored in the database, thus storage space for the clustered index is also a constraint to consider. So if we have several indexed views on a single table, we will have several copies of the data, along with the statistics of the data and the indexes and all of the overhead that goes with it.
    9. To use an indexed view in all other editions, the NOEXPAND table hint must be used.
    10. The maintenance of an indexed view can be greater than the cost of maintaining a table index. If the underlying data is updated frequently, the cost of maintaining the indexed view data may outweigh the performance benefits of using the indexed view.
    11. Cross Database Query Not Allowed
    12. Schema name must be mentioned before creating view
    13. All Views must be Schema bound if used in your Indexed View
    14. Indexed View cannot be Altered. You cannot drop a View if it is using in any other Indexed View
    15. Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.
    16. 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.
    17. The first index on a View must be a clustered, UNIQUE index.
    18. SELF JOIN Not Allowed in Indexed View
    19. View Over the View Not Possible with Index View
    20. Order by does not work
    21. Adding Column is Expensive by Joining Table Outside View

    keywords which are not allowed in the indexed view

    ANY, NOT ANY
    Arithmetic on imprecise (float, real) values
    COMPUTE, COMPUTE BY
    Contradictions SQL Server can detect that mean the view would be empty (for example, where 0=1 and …)
    CONVERT producing an imprecise result
    COUNT(*)
    Derived tables (subquery in FROM list)
    DISTINCT
    EXISTS, NOT EXISTS
    Expressions on aggregate results (for example, SUM(x)+SUM(x))
    Full-text predicates (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
    GROUP BY ALL
    Imprecise constants (for example, 2.34e5)
    Inline or table-valued functions
    MIN, MAX
    Nondeterministic expressions
    Non-Unicode collations
    OPENROWSET, OPENQUERY, OPENDATASOURCE
    OPENXML
    ORDER BY
    OUTER join
    References to a base table with a disabled clustered index
    References to a table or function in a different database
    References to another view
    ROWSET functions
    Self-joins
    STDEV, STDEVP, VAR, VARP, AVG
    Subqueries
    SUM on nullable expressions
    Table hints (for example, NOLOCK)
    text, ntext, image, filestream, or xml columns
    TOP
    UNION
    The OVER clause, which includes ranking or aggregate window functions.
    A CLR user-defined aggregate function.
    The full-text predicates CONTAINS or FREETEXT.
    COMPUTE or COMPUTE BY clause.
    The CROSS APPLY or OUTER APPLY operators.
    The PIVOT or UNPIVOT operators
    Table hints (applies to compatibility level of 90 or higher only).
    Join hints.
    Direct references to Xquery expressions. Indirect references, such as Xquery expressions inside a schema-bound user-defined function, are acceptable.
    

    http://msdn.microsoft.com/en-us/library/ms191432.aspx

    http://www.databasejournal.com/features/mssql/article.php/3867651/SQL-Server-Indexed-Views.htm

    http://blog.sqlauthority.com/2010/09/27/sql-server-keywords-view-definition-must-not-contain-for-indexed-view-limitation-of-the-view-10

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

    Replied on Jan 12 2012 5:37AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    4

    Indexed views in sql server are similar to what other product called as materrialized view. When we create an index on view the aggregated data stored in the leaf level of the table. So when we perform any operation on view, instead of scanning the whole table it only scan the leaf node. For example if a table contain all the customer details. But we want customer details of each country. So we can create a view using GROUP BY on countries and count the no of orders.

    Normal view are not stored, its every time scan the whole table to get the result, but when we create index it stored the data in leaf node of the table. The first index build on view should be must clustered index, because of clustered index contains all the data at its leaf level, this index actually does the materilazation of view.

    Restriction

    The view defination can not contain any of the following:

    1. TOP
    2. LOB columns
    3. DISTINCT
    4. MIN, MAX, COUNT(*), COUNT(), STDEV, VARIANCE, AVG
    5. SUM on a nullable expression
    6. A derived table
    7. The ROWSET function
    8. Another view (you can reference only base tables)
    9. UNION
    10. Subqueries, OUTER joins, or self-joins
    11. Full-text predicates (CONTAINS, FREETEXT)
    12. COMPUTE, COMPUTE BY
    13. ORDER BY ( we can use this by using TOP keyword in SELECT statement)

    Example to creat Indexed view:

    CREATE VIEW CountryCustomer

    WITH SCHEMABINDING

    AS SELECT CustomerID,CustomerName,SUM (OrderQty) AS TotalOrder

    FROM Customer.CustomerOrderDetail

    GROUP BY CustomerID,CustomerName;

    CREATE UNIQUE CLUSTERED INDEX VCustomerOrder_Idx ON Vdiscount1 (CustomerID,CustomerName);

    Replied on Jan 12 2012 9:47PM  . 
    Rajesh Patel
    2376 · 0% · 5
  • Score
    5

    Why Indexed Views?

    Views have been available throughout the history of Microsoft SQL Server. However, using views that return very large result sets can lead to poor performance, as the result set is not indexed and the entire result must be table scanned if the view is used in a join or a subquery of a T-SQL command. Additionally, products like Oracle have come out with the concept of a Materialized View that give an additional performance boost by being able to have indexes built on a view. So in the continuing evolution of the SQL Server product line and in response to Oracle’s Materialized View, After Microsoft SQL Server 2000 there is a new feature called the View Index. View Indexes give the product the capability to define an index on a view. Additionally, SQL Server View Indexes are dynamic in that changes to the data in the base tables are automatically reflected in the indexed view. Also the SQL Server query optimizer will try to use an indexed view even if the view is not referenced in the from clause of a T-SQL command. These features are not available in Oracle’s Materialized Views.

    What are the requirements for Indexed views?

    There are several requirements that you must take into consideration when using Indexed views.

    1.View definition must always return the same results from the same underlying data.

    2.Views cannot use non-deterministic functions.

    3.The first index on a View must be a clustered, UNIQUE index.

    4.If you use Group By, you must include the new COUNT_BIG(*) in the select list.

    5.View definition cannot contain the following

    a,TOP

    b.Text, ntext or image columns

    c.DISTINCT

    d.MIN, MAX, COUNT, STDEV, VARIANCE, AVG

    e.SUM on a nullable expression

    f.A derived table

    g.Rowset function

    h.Another view

    i.UNION

    j.Subqueries, outer joins, self joins

    k.Full-text predicates like CONTAIN or FREETEXT

    l.COMPUTE or COMPUTE BY

    m.Cannot include order by in view definition

    Notice that Indexed Views change the very essence of what a view was before this version of Sql Server. First, the data represented by the view is actually stored in the database. Secondly, the view definition must always return the same results for the same underlying data and all functions and expressions must be deterministic no matter what the current session settings.

    To make sure that you can meet this requirement, the following session options must be set when you create an index view, when you modify any of the tables included in an indexed view or when the optimizer decides to use the indexed view as part of a query plan. Session Options that must be on

    ANSI_NULLS

    ANSI_PADDING

    ANSI_WARNINGS

    ARITHABORT

    CONCATNULLYEILDS_NULL

    QUOTED_IDENTIFIERS

    Session options that must be off

    NUMERIC_ROUNDABORT

    Functions like GetDate(), rand() are non-deterministic because of different session settings can return different values and the settings for one user may not be the same as for another.

    Besides these restrictions, the underlying tables that make up the view must be protected from schema changes. Part of the syntax of the create view command is the “with SCHEMABINDING” phrase. This is required to create a View Index and this will prevent the dropping or altering of tables participating in an Indexed View. Note that dropping the clustered index of an Indexed View will return it to the standard SQL view as it was as a normal view

    Replied on Jan 24 2012 12:24AM  . 
    indika saminda kannangara
    188 · 1% · 251

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.