create indexs in Views Based on Views

create indexs in Views Based on Views

Post by TKT » Tue, 10 Jun 2003 23:47:43



I have a view based on another View and this completes
extraction information of two tables....

CREATE UNIQUE NONCLUSTERED INDEX P_Rirsa
ON Tops (Customerid, OrderId)

at the time of creating index in the last View marks the
following error to me:

Server: Msg 1939, Level 16, State 1, Line 1
Cannot create index on view 'TopS' because the view is not
schema bound.

Somebody has the idea of like solving it

Thanks

 
 
 

create indexs in Views Based on Views

Post by David Porta » Wed, 11 Jun 2003 00:13:50


You can't create an index on this view. Indexed views must only reference
base tables not other views.

See here for details of other requirements for indexed views:
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_06_9jnb.asp

--
David Portas
------------
Please reply only to the newsgroup
--


Quote:> I have a view based on another View and this completes
> extraction information of two tables....

> CREATE UNIQUE NONCLUSTERED INDEX P_Rirsa
> ON Tops (Customerid, OrderId)

> at the time of creating index in the last View marks the
> following error to me:

> Server: Msg 1939, Level 16, State 1, Line 1
> Cannot create index on view 'TopS' because the view is not
> schema bound.

> Somebody has the idea of like solving it

> Thanks


 
 
 

create indexs in Views Based on Views

Post by Steve Kas » Wed, 11 Jun 2003 00:16:43


This is from the Books Online documentation that comes
with SQL Server 2000:

 From the article "create index"
  The view must be defined with SCHEMABINDING in order to create an
index on it.

There are other requirements for a view to be indexed in the same article.
Also, you cannot create a nonclustered index only on a view.  A requirement
for creating a nonclustered index on the view is that a clustered index
exists.

Steve Kass
Drew University


>I have a view based on another View and this completes
>extraction information of two tables....

>CREATE UNIQUE NONCLUSTERED INDEX P_Rirsa
>ON Tops (Customerid, OrderId)

>at the time of creating index in the last View marks the
>following error to me:

>Server: Msg 1939, Level 16, State 1, Line 1
>Cannot create index on view 'TopS' because the view is not
>schema bound.

>Somebody has the idea of like solving it

>Thanks

 
 
 

create indexs in Views Based on Views

Post by JZHU » Wed, 11 Jun 2003 00:18:31


FROM BOL:
To create indexed view, the view must not reference any
other views, only base tables.

Quote:>-----Original Message-----
>I have a view based on another View and this completes
>extraction information of two tables....

>CREATE UNIQUE NONCLUSTERED INDEX P_Rirsa
>ON Tops (Customerid, OrderId)

>at the time of creating index in the last View marks the
>following error to me:

>Server: Msg 1939, Level 16, State 1, Line 1
>Cannot create index on view 'TopS' because the view is
not
>schema bound.

>Somebody has the idea of like solving it

>Thanks

>.

 
 
 

create indexs in Views Based on Views

Post by Vinod » Wed, 11 Jun 2003 05:08:34


You seem to have a requirement which is a limitation in this version of SQL Server.

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
SCT Software Solutions


> I have a view based on another View and this completes
> extraction information of two tables....

> CREATE UNIQUE NONCLUSTERED INDEX P_Rirsa
> ON Tops (Customerid, OrderId)

> at the time of creating index in the last View marks the
> following error to me:

> Server: Msg 1939, Level 16, State 1, Line 1
> Cannot create index on view 'TopS' because the view is not
> schema bound.

> Somebody has the idea of like solving it

> Thanks

 
 
 

1. Create views based on DBA_ views...

Hi,

I am new to database administration. I was trying to create some views
based on DBA_ views, but I ran into some error. Here is what I did:

* I started sqlplus as SYSTEM.
* In sqlplus, I tried to create a view:

SQL> CREATE VIEW my_dba_tablespace_sizes AS
  2    SELECT tablespace_name, sum(bytes) bytes
  3      FROM dba_data_files
  4      GROUP BY tablespace_name;
    FROM dba_data_files
             *
ERROR at line 3:
ORA-00942: table or view does not exist

Can anyone tell me what the problem was? Any suggestions for fixing this
problem?

Thanks,

-- Chris

2. Software License

3. Creating a local view using CREATE SQL VIEW

4. Microsoft Knowledge Base

5. View based on view?

6. DB Design question--Separate Data for Diff. Users

7. view based on view

8. Refresh View based on another View

9. MSSQL 6.5 View created with other Views

10. Create view based on Oracle tables error

11. how to create a view based on procedure