Creating index on a view
hi there,
i try to create a index on view becasue it takes to long when i try to execute a stored procedure that pulls data from it. i keep getting this error:
"Cannot create index on view ... because the view is not schema bound."
I know it needs something like CREATE VIEW .... WITH SCHEMABINDING can you experts let me know how to achieved this?
SELECT dbo.NEWBAL.TD_BALANCE, dbo.CUSTNEW.ACCT_P, dbo.CUSTNEW.[CUST-DIGIT], dbo.CUSTNEW.[CUST-TYPE], dbo.BROKER2.[Last name],
dbo.CUSTNEW.[CUST-NAME], dbo.CUSTNEW.PHONE#, dbo.CUSTNEW.STATE, dbo.NEWBAL.REP_P,
dbo.uw_TotalMvByAcct.SumOf
CASE WHEN [SumOfTOTAL_MKT_VALUE] = 0 THEN 0 ELSE ([SumOfNET_LIQ_EQUITY] / [SumOfTOTAL_MKT_VALUE]) END AS Percentage,
dbo.POSITION.CUSIP, dbo.POSITION.TRD_DATE_POS,
dbo.NEWBAL.CUST_TYPE, dbo.uw_TotalMvByAcct.NET
FROM dbo.NEWBAL LEFT OUTER JOIN
dbo.POSITION ON dbo.NEWBAL.ACCT_P = dbo.POSITION.ACCT_P LEFT OUTER JOIN
dbo.CUSTNEW ON dbo.NEWBAL.ACCT_P = dbo.CUSTNEW.ACCT_P LEFT OUTER JOIN
dbo.BROKER2 ON dbo.NEWBAL.REP_P = dbo.BROKER2.REP_P LEFT OUTER JOIN
dbo.uw_TotalMvByAcct ON dbo.NEWBAL.ACCT_P = dbo.uw_TotalMvByAcct.ACCT_
WHERE (dbo.NEWBAL.TD_BALANCE > '0') AND (dbo.uw_TotalMvByAcct.SumO
i need the index on dbo.CUSTNEW.ACCT_P
thanks,
i try to create a index on view becasue it takes to long when i try to execute a stored procedure that pulls data from it. i keep getting this error:
"Cannot create index on view ... because the view is not schema bound."
I know it needs something like CREATE VIEW .... WITH SCHEMABINDING can you experts let me know how to achieved this?
SELECT dbo.NEWBAL.TD_BALANCE, dbo.CUSTNEW.ACCT_P, dbo.CUSTNEW.[CUST-DIGIT], dbo.CUSTNEW.[CUST-TYPE], dbo.BROKER2.[Last name],
dbo.CUSTNEW.[CUST-NAME], dbo.CUSTNEW.PHONE#, dbo.CUSTNEW.STATE, dbo.NEWBAL.REP_P,
dbo.uw_TotalMvByAcct.SumOf
CASE WHEN [SumOfTOTAL_MKT_VALUE] = 0 THEN 0 ELSE ([SumOfNET_LIQ_EQUITY] / [SumOfTOTAL_MKT_VALUE]) END AS Percentage,
dbo.POSITION.CUSIP, dbo.POSITION.TRD_DATE_POS,
dbo.NEWBAL.CUST_TYPE, dbo.uw_TotalMvByAcct.NET
FROM dbo.NEWBAL LEFT OUTER JOIN
dbo.POSITION ON dbo.NEWBAL.ACCT_P = dbo.POSITION.ACCT_P LEFT OUTER JOIN
dbo.CUSTNEW ON dbo.NEWBAL.ACCT_P = dbo.CUSTNEW.ACCT_P LEFT OUTER JOIN
dbo.BROKER2 ON dbo.NEWBAL.REP_P = dbo.BROKER2.REP_P LEFT OUTER JOIN
dbo.uw_TotalMvByAcct ON dbo.NEWBAL.ACCT_P = dbo.uw_TotalMvByAcct.ACCT_
WHERE (dbo.NEWBAL.TD_BALANCE > '0') AND (dbo.uw_TotalMvByAcct.SumO
i need the index on dbo.CUSTNEW.ACCT_P
thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This is from books online
Requirements for the View
A view must meet these requirements before you can create a clustered index on it:
The ANSI_NULLS and QUOTED_IDENTIFIER 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. SCHEMABINDING 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. 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 if a user-defined function is deterministic. For more information, see Deterministic and Nondeterministic Functions.
The SELECT statement in the view cannot contain these Transact-SQL syntax elements:
The select list cannot use the * or table_name.* syntax to specify columns. Column names must be explicitly stated.
A table column name used as a simple expression cannot be specified in more than one view column. A column can be referenced multiple times provided all, or all but one, reference to the column is part of a complex expression or a parameter to a function. For example, this select list is invalid:
SELECT ColumnA, ColumnB, ColumnA
These select lists are valid:
SELECT ColumnA, AVG(ColumnA), ColumnA + Column B AS AddColAColB
SELECT SUM(ColumnA), ColumnA % ColumnB AS ModuloColAColB
A derived table.
Rowset functions.
UNION operator.
Subqueries.
Outer or self joins.
TOP clause.
ORDER BY clause.
DISTINCT keyword.
COUNT(*) (COUNT_BIG(*) is allowed.)
The AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions. If AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP are specified in queries referencing the indexed view, the optimizer can often calculate the needed result if the view select list contains these substitute functions. Complex aggregate function Substitute simple aggregate functions
AVG(X)
SUM(X), COUNT_BIG(X)
STDEV(X)
SUM(X), COUNT_BIG(X), SUM(X**2)
STDEVP(X)
SUM(X), COUNT_BIG(X), SUM(X**2)
VAR(X)
SUM(X), COUNT_BIG(X), SUM(X**2)
VARP(X)
SUM(X), COUNT_BIG(X), SUM(X**2)
For example, an indexed view select list cannot contain the expression AVG(SomeColumn). If the view select list contains the expressions SUM(SomeColumn) and COUNT_BIG(SomeColumn), SQL Server can calculate the average for a query that references the view and specifies AVG(SomeColumn).
A SUM function that references a nullable expression.
The full-text predicates CONTAINS or FREETEXT.
COMPUTE or COMPUTE BY clause.
If GROUP BY is not specified, the view select list cannot contain aggregate expressions.
If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, CUBE, or ROLLUP.
A column resulting from an expression that either evaluates to a float value or uses float expressions for its evaluation cannot be a key of an index in an indexed view or a table.
Requirements for the CREATE INDEX Statement
The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create additional nonclustered indexes. The naming conventions for indexes on views are the same as for indexes on tables. The only difference is that the table name is replaced with a view name. For more information, see CREATE INDEX.
The CREATE INDEX statement must meet these requirements in addition to the normal CREATE INDEX requirements:
The user executing the CREATE INDEX statement must be the view owner.
These SET options must be set to ON when the CREATE INDEX statement is executed:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIERS
The NUMERIC_ROUNDABORT option must be set to OFF.
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.
Requirements for the View
A view must meet these requirements before you can create a clustered index on it:
The ANSI_NULLS and QUOTED_IDENTIFIER 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. SCHEMABINDING 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. 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 if a user-defined function is deterministic. For more information, see Deterministic and Nondeterministic Functions.
The SELECT statement in the view cannot contain these Transact-SQL syntax elements:
The select list cannot use the * or table_name.* syntax to specify columns. Column names must be explicitly stated.
A table column name used as a simple expression cannot be specified in more than one view column. A column can be referenced multiple times provided all, or all but one, reference to the column is part of a complex expression or a parameter to a function. For example, this select list is invalid:
SELECT ColumnA, ColumnB, ColumnA
These select lists are valid:
SELECT ColumnA, AVG(ColumnA), ColumnA + Column B AS AddColAColB
SELECT SUM(ColumnA), ColumnA % ColumnB AS ModuloColAColB
A derived table.
Rowset functions.
UNION operator.
Subqueries.
Outer or self joins.
TOP clause.
ORDER BY clause.
DISTINCT keyword.
COUNT(*) (COUNT_BIG(*) is allowed.)
The AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions. If AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP are specified in queries referencing the indexed view, the optimizer can often calculate the needed result if the view select list contains these substitute functions. Complex aggregate function Substitute simple aggregate functions
AVG(X)
SUM(X), COUNT_BIG(X)
STDEV(X)
SUM(X), COUNT_BIG(X), SUM(X**2)
STDEVP(X)
SUM(X), COUNT_BIG(X), SUM(X**2)
VAR(X)
SUM(X), COUNT_BIG(X), SUM(X**2)
VARP(X)
SUM(X), COUNT_BIG(X), SUM(X**2)
For example, an indexed view select list cannot contain the expression AVG(SomeColumn). If the view select list contains the expressions SUM(SomeColumn) and COUNT_BIG(SomeColumn), SQL Server can calculate the average for a query that references the view and specifies AVG(SomeColumn).
A SUM function that references a nullable expression.
The full-text predicates CONTAINS or FREETEXT.
COMPUTE or COMPUTE BY clause.
If GROUP BY is not specified, the view select list cannot contain aggregate expressions.
If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, CUBE, or ROLLUP.
A column resulting from an expression that either evaluates to a float value or uses float expressions for its evaluation cannot be a key of an index in an indexed view or a table.
Requirements for the CREATE INDEX Statement
The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create additional nonclustered indexes. The naming conventions for indexes on views are the same as for indexes on tables. The only difference is that the table name is replaced with a view name. For more information, see CREATE INDEX.
The CREATE INDEX statement must meet these requirements in addition to the normal CREATE INDEX requirements:
The user executing the CREATE INDEX statement must be the view owner.
These SET options must be set to ON when the CREATE INDEX statement is executed:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIERS
The NUMERIC_ROUNDABORT option must be set to OFF.
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.
ASKER
when i try to recreate the view it gives an error on dbo.Newbal??
how this happend i can still execute but why does it give me an error?
how this happend i can still execute but why does it give me an error?
can you post the code you are using
ASKER
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_Report2930CUSTM
(@GSP VARCHAR (50))
AS
SELECT uw_CUSTMV_Pre.REP_P, BROKER2.[Over], BROKER2.[Last name],
uw_CUSTMV_Pre.ACCT_P,
CASE WHEN TRADE1RR.GSP IS NULL THEN 'HOUSE' ELSE TRADE1RR.GSP END AS GSP,
BROKER2.DEPT_P, tblDlyCom.[Month],
CASE WHEN [TOTAL_BALANCE]IS NULL THEN 0 ELSE [TOTAL_BALANCE] END AS TD_BAL,
CASE WHEN [TOTAL_SD_BALANCE]IS NULL THEN 0 ELSE [TOTAL_SD_BALANCE]END AS SD_BAL,
CASE WHEN [uw_TotalMMKT].[TOTAL_MMKT
CASE WHEN [SumOfTOTAL_MKT_VALUE]IS NULL THEN 0 ELSE [SumOfTOTAL_MKT_VALUE]END AS TOTAL_MKT_VALUE,
uw_COMM_TOTALS.SumOfSumOfG
FROM ((((((uw_CUSTMV_Pre LEFT JOIN uw_TotalBalance ON uw_CUSTMV_Pre.ACCT_P = uw_TotalBalance.ACCT_P) LEFT JOIN uw_TotalMMKT ON uw_CUSTMV_Pre.ACCT_P = uw_TotalMMKT.ACCT_P) LEFT JOIN tblDlyCom ON uw_CUSTMV_Pre.REP_P = tblDlyCom.REP_P) LEFT JOIN BROKER2 ON uw_CUSTMV_Pre.REP_P = BROKER2.REP_P) LEFT JOIN TRADE1RR ON uw_CUSTMV_Pre.REP_P = TRADE1RR.REP_P) LEFT JOIN uw_TotalMvByAcct ON uw_CUSTMV_Pre.ACCT_P = uw_TotalMvByAcct.ACCT_P) LEFT JOIN uw_COMM_TOTALS ON uw_CUSTMV_Pre.REP_P = uw_COMM_TOTALS.REP_P
WHERE (((uw_CUSTMV_Pre.REP_P)<>'
AND
isnull(TRADE1RR.GSP, '') = CASE @GSP
WHEN '*' THEN isnull(TRADE1RR.GSP, '')
ELSE @GSP
END;
--usp_Report2930CUSTMV_REP
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_Report2930CUSTM
(@GSP VARCHAR (50))
AS
SELECT uw_CUSTMV_Pre.REP_P, BROKER2.[Over], BROKER2.[Last name],
uw_CUSTMV_Pre.ACCT_P,
CASE WHEN TRADE1RR.GSP IS NULL THEN 'HOUSE' ELSE TRADE1RR.GSP END AS GSP,
BROKER2.DEPT_P, tblDlyCom.[Month],
CASE WHEN [TOTAL_BALANCE]IS NULL THEN 0 ELSE [TOTAL_BALANCE] END AS TD_BAL,
CASE WHEN [TOTAL_SD_BALANCE]IS NULL THEN 0 ELSE [TOTAL_SD_BALANCE]END AS SD_BAL,
CASE WHEN [uw_TotalMMKT].[TOTAL_MMKT
CASE WHEN [SumOfTOTAL_MKT_VALUE]IS NULL THEN 0 ELSE [SumOfTOTAL_MKT_VALUE]END AS TOTAL_MKT_VALUE,
uw_COMM_TOTALS.SumOfSumOfG
FROM ((((((uw_CUSTMV_Pre LEFT JOIN uw_TotalBalance ON uw_CUSTMV_Pre.ACCT_P = uw_TotalBalance.ACCT_P) LEFT JOIN uw_TotalMMKT ON uw_CUSTMV_Pre.ACCT_P = uw_TotalMMKT.ACCT_P) LEFT JOIN tblDlyCom ON uw_CUSTMV_Pre.REP_P = tblDlyCom.REP_P) LEFT JOIN BROKER2 ON uw_CUSTMV_Pre.REP_P = BROKER2.REP_P) LEFT JOIN TRADE1RR ON uw_CUSTMV_Pre.REP_P = TRADE1RR.REP_P) LEFT JOIN uw_TotalMvByAcct ON uw_CUSTMV_Pre.ACCT_P = uw_TotalMvByAcct.ACCT_P) LEFT JOIN uw_COMM_TOTALS ON uw_CUSTMV_Pre.REP_P = uw_COMM_TOTALS.REP_P
WHERE (((uw_CUSTMV_Pre.REP_P)<>'
AND
isnull(TRADE1RR.GSP, '') = CASE @GSP
WHEN '*' THEN isnull(TRADE1RR.GSP, '')
ELSE @GSP
END;
--usp_Report2930CUSTMV_REP
based on the above query you cannot index it because it contains a left outer join converting the left outer join to inne rjoin
create view
ABC
with schemabinding
as
SELECT dbo.NEWBAL.TD_BALANCE, dbo.CUSTNEW.ACCT_P, dbo.CUSTNEW.[CUST-DIGIT], dbo.CUSTNEW.[CUST-TYPE], dbo.BROKER2.[Last name],
dbo.CUSTNEW.[CUST-NAME], dbo.CUSTNEW.PHONE#, dbo.CUSTNEW.STATE, dbo.NEWBAL.REP_P,
dbo.uw_TotalMvByAcct.SumOf
CASE WHEN [SumOfTOTAL_MKT_VALUE] = 0 THEN 0 ELSE ([SumOfNET_LIQ_EQUITY] / [SumOfTOTAL_MKT_VALUE]) END AS Percentage,
dbo.POSITION.CUSIP, dbo.POSITION.TRD_DATE_POS,
dbo.NEWBAL.CUST_TYPE, dbo.uw_TotalMvByAcct.NET
FROM dbo.NEWBAL inner JOIN
dbo.POSITION ON dbo.NEWBAL.ACCT_P = dbo.POSITION.ACCT_P inner JOIN
dbo.CUSTNEW ON dbo.NEWBAL.ACCT_P = dbo.CUSTNEW.ACCT_P inner JOIN
dbo.BROKER2 ON dbo.NEWBAL.REP_P = dbo.BROKER2.REP_P inner JOIN
dbo.uw_TotalMvByAcct ON dbo.NEWBAL.ACCT_P = dbo.uw_TotalMvByAcct.ACCT_
WHERE (dbo.NEWBAL.TD_BALANCE > '0') AND (dbo.uw_TotalMvByAcct.SumO
go
create index ix_001 on abc(ACCT_P)
go