Hi,
Can we define Index on view?
Thanks,
Rahul Jhayes, but there are some rules you have to follow.
http://msdn2.microsoft.com/en-US/library/ms191432.aspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx|||thnkx jezemine|||Hi,
Can we define Index on view?
Thanks,
Rahul Jha
Are you playing us?
I mean where do you come up with these questions
For example, wouldn't you be more worried about appropriate indexes on the tables first?|||Was worried about indexes on the table first Brett. And once done with that want to check with the indexed view. Who knows that might again improve the performance. But I was amazed that why are you asking this......
No, m not playing with any one Brett.|||I am here to help myself from the support of fellow members.|||Yes Rahul, but this information is easily found in Books Online or a simple google search. The forum does not exist to copy/paste the relevant documentation for you, or do your searches for you. Your first resource should always be Books Online. Your second resource should be a google search, or a search of existing threads on dbforums. Only after exhausting these resources should you post your question on the forum.|||Was worried about indexes on the table first Brett.
Not necessarily. As far as I understand the indexed views, SQL Server will use the indexes when the view is queried directly, bypassing the table. An indexed view in SQL Server seems to be very similar to a materialized view in Oracle. So I'd assume that an indexed view can be quite fast even if the underlying tables don't have any indexes defined.|||Never, ever had to create an indexed view
BOL
In SQL Server 2000, indexes also can be created on computed columns and views. Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored.
The UNIQUE or PRIMARY KEY may contain a computed column as long as it satisfies all conditions for indexing. Specifically, the computed column must be deterministic, precise, and must not contain text, ntext, or image columns. For more information about determinism, see Deterministic and Nondeterministic Functions.
Creation of an index on a computed column or view may cause the failure of an INSERT or UPDATE operation that previously worked. Such a failure may take place when the computed column results in arithmetic error. For example, although computed column c in the following table will result in an arithmetic error, the INSERT statement will work:
CREATE TABLE t1 (a int, b int, c AS a/b)
GO
INSERT INTO t1 VALUES ('1', '0')
GO
If, instead, after creating the table, you create an index on computed column c, the same INSERT statement now will fail.
CREATE TABLE t1 (a int, b int, c AS a/b)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1.c
GO
INSERT INTO t1 VALUES ('1', '0')
GO
The result of a query using an index on a view defined with numeric or float expressions may be different from a similar query that does not use the index on the view. This difference may be the result of rounding errors during INSERT, DELETE, or UPDATE actions on underlying tables.
To prevent SQL Server from using indexed views, include the OPTION (EXPAND VIEWS) hint on the query. Also, setting any of the listed options incorrectly will prevent the optimizer from using the indexes on the views. For more information about the OPTION (EXPAND VIEWS) hint, see SELECT.
Restrictions on indexed views
The SELECT statement defining an indexed view must not have the TOP, DISTINCT, COMPUTE, HAVING, and UNION keywords. It cannot have a subquery.
The SELECT list may not include asterisks (*), 'table.*' wildcard lists, DISTINCT, COUNT(*), COUNT(<expression>), computed columns from the base tables, and scalar aggregates.
Nonaggregate SELECT lists cannot have expressions. Aggregate SELECT list (queries that contain GROUP BY) may include SUM and COUNT_BIG(<expression>); it must contain COUNT_BIG(*). Other aggregate functions (MIN, MAX, STDEV,...) are not allowed.
Complex aggregation using AVG cannot participate in the SELECT list of the indexed view. However, if a query uses such aggregation, the optimizer is capable of using this indexed view to substitute AVG with a combination of simple aggregates SUM and COUNT_BIG.
A column resulting from an expression that either evaluates to a float data type or uses float expressions for its evaluation cannot be a key of an index in an indexed view or on a computed column in a table. Such columns are called nonprecise. Use the COLUMNPROPERTY function to determine if a particular computed column or a column in a view is precise.
Indexed views are subject to these additional restrictions:
The creator of the index must own the tables. All tables, the view, and the index, must be created in the same database.
The SELECT statement defining the indexed view may not contain views, rowset functions, inline functions, or derived tables. The same physical table may occur only once in the statement.
In any joined tables, no OUTER JOIN operations are allowed.
No subqueries or CONTAINS or FREETEXT predicates are allowed in the search condition.
If the view definition contains a GROUP BY clause, all grouping columns as well as the COUNT_BIG(*) expression must appear in the view's SELECT list. Also, these columns must be the only columns in the CREATE UNIQUE CLUSTERED INDEX clause.
The body of the definition of a view that can be indexed must be deterministic and precise, similar to the requirements on indexes on computed columns. See Creating Indexes on Computed Columns.
Permissions|||I have, occasionally. Its a handy way to implement unique constraints on nullable columns.|||I have, occasionally. Its a handy way to implement unique constraints on nullable columns.
ummmmmmmmm
what kind of constraints that can't be done at the table level?|||Column values must be unique or null. Allow multiple NULLs.|||it's the schema binding stuff i always found awkward and led me to shun indexed views.|||Allow multiple NULLs.
On a Unique INDEX?
You sure?
Post some sample code
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment