Creating Indexes on Views  
Author Message
Jorabi





PostPosted: Thu Feb 16 17:19:27 CST 2006 Top

SQL Server Developer >> Creating Indexes on Views

Hi,

I have a view which uses 2 other views.I need to create an index on this
view. But one of the two other views has Union statements. Is there any way
that an index can be created on this final view ( which is made of the other
2 views)?

Thanks


--
pmud

SQL Server210  
 
 
ML





PostPosted: Thu Feb 16 17:19:27 CST 2006 Top

SQL Server Developer >> Creating Indexes on Views Indexed views cannot reference other views, and a view definition cannot
include a UNION. More here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_9jnb.asp


ML

---
http://milambda.blogspot.com/
 
 
pmud





PostPosted: Thu Feb 16 17:36:26 CST 2006 Top

SQL Server Developer >> Creating Indexes on Views Yes, I read that article, but i thought maybe someone ahs faced the same
problem as me and has a workaround for this. :(
--
pmud




> Indexed views cannot reference other views, and a view definition cannot
> include a UNION. More here:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_9jnb.asp
>
>
> ML
>
> ---
> http://milambda.blogspot.com/
 
 
ML





PostPosted: Thu Feb 16 17:49:27 CST 2006 Top

SQL Server Developer >> Creating Indexes on Views I was once in a similar situation, but found out that I could avoid it by
slightly adapting the data model. Maybe there's something else that would
help you achieve your goal, but you'd have to post DDL (and maybe some sample
data).


ML

---
http://milambda.blogspot.com/
 
 
Gert-Jan





PostPosted: Fri Feb 17 15:19:50 CST 2006 Top

SQL Server Developer >> Creating Indexes on Views I don't know your real problem (because you haven't told it), or your
experience with SQL Server. It sounds like you are asking for a kludge.

The first step would be to add index(es) to the base table(s). In many
cases this will increase performance sufficiently, and other queries
(queries that do not use your view) will also benefit from these
indexes.

If this is not enough, you can consider indexing the view. But as you
have noticed there are many limitations to that, and you need Enterprise
Edition (or always use the appropriate view hints).

Gert-Jan



>
> Hi,
>
> I have a view which uses 2 other views.I need to create an index on this
> view. But one of the two other views has Union statements. Is there any way
> that an index can be created on this final view ( which is made of the other
> 2 views)?
>
> Thanks
>
> --
> pmud