Creating an Indexed View

I am trying to create an indexed view, on a date from a date dimension table...I am new to SQL, and I am at a loss of ideas on this one. Any help would be greatly appreciated!

Here is the Error I am given

"Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line 3

Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'. 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.

Msg 1939, Level 16, State 1, Line 1

Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is not schema bound."

Here is my code..

CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING

AS

SELECT TEW_D_DT.DT_KEY AS DATE_KEY,

VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,

TEW_D_LOC.LOC_DESC AS LOC_DESC ,

TEW_D_LOC.RGN_DESC AS REGION_DESC,

TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,

ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,

COUNT_BIG(*) AS COUNT

FROM

JJWHSE.VEW_F_INVC_SHIP_TO VEW_F_INVC_SHIP_TO

INNER JOIN

JJWHSE.VEW_F_INVC_PAY_EXT VEW_F_INVC_PAY_EXT

ON

VEW_F_INVC_SHIP_TO.DATE_KEY = VEW_F_INVC_PAY_EXT.DATE_KEY

AND VEW_F_INVC_SHIP_TO.ORDER_NUMBER = VEW_F_INVC_PAY_EXT.ORDER_NUMBER

AND VEW_F_INVC_SHIP_TO.INVOICE_NUMBER = VEW_F_INVC_PAY_EXT.INVOICE_NUMBER

AND VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER = VEW_F_INVC_PAY_EXT.SHIP_TO_NUMBER

INNER JOIN

JJWHSE.VEW_F_INVC_DTL VEW_F_INVC_DTL

ON

VEW_F_INVC_DTL.DATE_KEY = VEW_F_INVC_SHIP_TO.DATE_KEY

AND VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_INVC_SHIP_TO.ORDER_NUMBER

AND VEW_F_INVC_DTL.INVOICE_NUMBER = VEW_F_INVC_SHIP_TO.INVOICE_NUMBER

AND VEW_F_INVC_DTL.SHIP_TO_NUMBER = VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER

AND VEW_F_INVC_DTL.LINE_NUMBER = VEW_F_INVC_PAY_EXT.LINE_NUMBER

AND VEW_F_INVC_DTL.SEQUENCE_NUMBER = VEW_F_INVC_PAY_EXT.SEQUENCE_NUMBER

AND VEW_F_INVC_DTL.NON_INVENTORY = 'N'

AND VEW_F_INVC_DTL.GIFT_CARD = 'N'

INNER JOIN

JJWHSE.VEW_F_MZT_ORDER_HEADER VEW_F_MZT_ORDER_HEADER

ON

VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_MZT_ORDER_HEADER.ORDER_NUMBER

AND VEW_F_MZT_ORDER_HEADER.ACTIVE_FLAG = 1

INNER JOIN

JJWHSE.TEW_D_DT TEW_D_DT

ON

VEW_F_INVC_DTL.DATE_KEY = TEW_D_DT.DT_KEY

INNER JOIN

JJWHSE.TEW_D_LOC TEW_D_LOC

ON

VEW_F_MZT_ORDER_HEADER.LOCATION_KEY = TEW_D_LOC.LOC_KEY

WHERE VEW_F_INVC_SHIP_TO.CHANNEL = 'I'

GROUP BY TEW_D_DT.DT_KEY , VEW_F_MZT_ORDER_HEADER.LOCATION_KEY , TEW_D_LOC.LOC_DESC ,

TEW_D_LOC.RGN_DESC , TEW_D_LOC.DISTRICT_DESC

GO

CREATE UNIQUE CLUSTERED INDEX IX_VEW_F_MZT_ORDER_HEADE_DAY ON JJWHSE.VEW_F_MZT_ORDER_HEADER ( DATE_KEY )

The first error message is as direct as it can be. You cannot

create a view WITH SCHEMABINDING when its definition mentions

another view that was not itself created WITH SCHEMABINDING.

In your case, you can't create 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'

with schemabinding because it refers to another view,

'JJWHSE.VEW_F_INVC_SHIP_TO', which was not created with

schemabinding.

There does seem to be a bit of confusion. The code you pasted

here tries to do two things:

1. Create a view named 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'

2. Create an index on 'VEW_F_MZT_ORDER_HEADER',

which is a *different* view.

The second error you got (Cannot create index...)

has nothing at all to do with the first error or with

the CREATE VIEW code that generates the first error. It's

also as clear as it can be. The 'VEW_F_MZT_ORDER_HEADER'

exists, but it was not created with schemabinding, which

is a requirement for creating an index on a view.

If you want to index a view, that view and all the views

(and functions) on which it depends must be created with

schemabinding and meet the requirements for that option.

Steve Kass

Drew University

http://www.stevekass.com
topcoder_cc@.discussions.microsoft.com wrote:

> I am trying to create an indexed view, on a date from a date dimension

> table...I am new to SQL, and I am at a loss of ideas on this one. Any

> help would be greatly appreciated!

>

> Here is the Error I am given

>

> "Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line

> 3

>

> Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'.

> 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.

>

> Msg 1939, Level 16, State 1, Line 1

>

> Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is

> not schema bound."

>

> Here is my code..

>

> CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING

>

> AS

>

> SELECT TEW_D_DT.DT_KEY AS DATE_KEY,

>

> VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,

>

> TEW_D_LOC.LOC_DESC AS LOC_DESC ,

>

> TEW_D_LOC.RGN_DESC AS REGION_DESC,

>

> TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,

>

> ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,

>

> COUNT_BIG(*) AS COUNT

>

> FROM

>

> JJWHSE.VEW_F_INVC_SHIP_TO VEW_F_INVC_SHIP_TO

>

> INNER JOIN

>

> JJWHSE.VEW_F_INVC_PAY_EXT VEW_F_INVC_PAY_EXT

>

> ON

>

> VEW_F_INVC_SHIP_TO.DATE_KEY = VEW_F_INVC_PAY_EXT.DATE_KEY

>

> AND VEW_F_INVC_SHIP_TO.ORDER_NUMBER = VEW_F_INVC_PAY_EXT.ORDER_NUMBER

>

> AND VEW_F_INVC_SHIP_TO.INVOICE_NUMBER =

> VEW_F_INVC_PAY_EXT.INVOICE_NUMBER

>

> AND VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER =

> VEW_F_INVC_PAY_EXT.SHIP_TO_NUMBER

>

> INNER JOIN

>

> JJWHSE.VEW_F_INVC_DTL VEW_F_INVC_DTL

>

> ON

>

> VEW_F_INVC_DTL.DATE_KEY = VEW_F_INVC_SHIP_TO.DATE_KEY

>

> AND VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_INVC_SHIP_TO.ORDER_NUMBER

>

> AND VEW_F_INVC_DTL.INVOICE_NUMBER = VEW_F_INVC_SHIP_TO.INVOICE_NUMBER

>

> AND VEW_F_INVC_DTL.SHIP_TO_NUMBER = VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER

>

> AND VEW_F_INVC_DTL.LINE_NUMBER = VEW_F_INVC_PAY_EXT.LINE_NUMBER

>

> AND VEW_F_INVC_DTL.SEQUENCE_NUMBER = VEW_F_INVC_PAY_EXT.SEQUENCE_NUMBER

>

> AND VEW_F_INVC_DTL.NON_INVENTORY = 'N'

>

> AND VEW_F_INVC_DTL.GIFT_CARD = 'N'

>

> INNER JOIN

>

> JJWHSE.VEW_F_MZT_ORDER_HEADER VEW_F_MZT_ORDER_HEADER

>

> ON

>

> VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_MZT_ORDER_HEADER.ORDER_NUMBER

>

> AND VEW_F_MZT_ORDER_HEADER.ACTIVE_FLAG = 1

>

> INNER JOIN

>

> JJWHSE.TEW_D_DT TEW_D_DT

>

> ON

>

> VEW_F_INVC_DTL.DATE_KEY = TEW_D_DT.DT_KEY

>

> INNER JOIN

>

> JJWHSE.TEW_D_LOC TEW_D_LOC

>

> ON

>

> VEW_F_MZT_ORDER_HEADER.LOCATION_KEY = TEW_D_LOC.LOC_KEY

>

> WHERE VEW_F_INVC_SHIP_TO.CHANNEL = 'I'

>

> GROUP BY TEW_D_DT.DT_KEY , VEW_F_MZT_ORDER_HEADER.LOCATION_KEY ,

> TEW_D_LOC.LOC_DESC ,

>

> TEW_D_LOC.RGN_DESC , TEW_D_LOC.DISTRICT_DESC

>

> GO

>

> CREATE UNIQUE CLUSTERED INDEX IX_VEW_F_MZT_ORDER_HEADE_DAY ON

> JJWHSE.VEW_F_MZT_ORDER_HEADER ( DATE_KEY )

>

>