Re: create inex in a view
From: Graeme Malcolm (graemem_cm_at_hotmail.com)
Date: 08/12/04
- Next message: MANDLA MKHWANAZI: "Period To Date Function"
- Previous message: Hugo Kornelis: "Re: Scalar UDF returns subquery error... ddl included."
- In reply to: oj: "Re: create inex in a view"
- Next in thread: oj: "Re: create inex in a view"
- Reply: oj: "Re: create inex in a view"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 12 Aug 2004 08:47:51 +0100
That's interesting - I was going on this note from BOL:
Note You can create indexed views only if you install Microsoft SQL Server
2000 Enterprise Edition or Microsoft SQL Server 2000 Developer Edition.
I use Dev edition, so I couldn't check it against a Standard installation.
Looks like OJ is right; alter your view(s) to include a SCHEMABINDING clause
and see what happens!
-- ---- Graeme Malcolm Principal Technologist Content Master Ltd. www.contentmaster.com "oj" <nospam_ojngo@home.com> wrote in message news:%23MPHZlDgEHA.2984@tk2msftngp13.phx.gbl... #2 is simply wrong. You can create an indexed view on any editions. However, the index is automatically considered by the query optimizer if you're on Enterprise. Note: you can make the optimizer to consider the view's index by introducing the Noexpand hint. Here is a quick demo script that successfully runs on a standard edition. /* Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3) */ create table dbo.t1(pkid int primary key,j int) go create view _v with schemabinding as select pkid,j from dbo.t1 go create unique clustered index _ix on _v(pkid) create index _ix2 on _v(j) go drop view _v go drop table t1 go aoxpsql: the error means you do not have 'schemabinding' defined for your view's definition. "Graeme Malcolm" <graemem_cm@hotmail.com> wrote in message news:eEgd1QDgEHA.3048@TK2MSFTNGP09.phx.gbl... > A couple of things to check: > 1. The first index on a view must be a UNIQUE CLUSTERED index - yours is > simply CLUSTERED. > 2. You can only create indexed view on Enterprise and Developer editions of > SQL Server. > Hope that helps! > Graeme > > -- > ---- > Graeme Malcolm > Principal Technologist > Content Master Ltd. > www.contentmaster.com > > > "aoxpsql" <anonymous@discussion.com> wrote in message > news:OgMyZ%23CgEHA.3428@TK2MSFTNGP11.phx.gbl... > Hi, > I am trying to create an index in a view, using: > > USE Axapta > go > SET NUMERIC_ROUNDABORT OFF > go > SET > ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIF > IER,ANSI_NULLS ON > go > CREATE CLUSTERED INDEX I_10_Vw1 > ON > dbo.Vw_Summary_Reports(projid,Date,dimension3_,dimension5_,CUSTACCOUNT) > WITH FILLFACTOR = 10 > ON [PRIMARY] > go > > and get the warning: > 15:43:00.196 DBMS MSHFSQL2 -- [Microsoft][ODBC SQL Server Driver][SQL > Server]Cannot create index on view 'Vw_Summary_Reports' because the view is > not schema bound.(42000,1939) > > any help? > > >
- Next message: MANDLA MKHWANAZI: "Period To Date Function"
- Previous message: Hugo Kornelis: "Re: Scalar UDF returns subquery error... ddl included."
- In reply to: oj: "Re: create inex in a view"
- Next in thread: oj: "Re: create inex in a view"
- Reply: oj: "Re: create inex in a view"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
