November 7, 2002 at 1:18 pm
Hello...
This message is for Antares (Simon, thinaks for your help so far in the other areas)
I've finally got my Indexed view to work properly since it calls several UDF's and I had a NIGHTMARE getting all the SET statements correct before creating the Index(ANS_NULLS etc..).
But - I've created several indexes now on the View.
Questions:
1) If a column in the Indexed view is a SerialID and in it's base table the column is in fact a Foreign key - should I index that ID column in the View ??? I cannot see the view index being used when I run the execution plan. All I see are the indexes on the base table(s) being used.
2) In fact I cannot ever see the view indexes being used as I have created several indexes purely for testing purposes. Will I ever see the view name appear in the execution plan or only base table names??
Essentially, creating these Indexed views with schemabinding and SET statements is a real pain in the @ss and I do not see any benefits from it as yet. But it sounds sooo useful 🙂
Any talkback is appreciated.
- B
November 7, 2002 at 1:56 pm
Even more stuff on Indexed Views.
A) It seems that the ONLY way I can specify the correct ANSI_NULLS and (I think) ARITH_ABORT options when creating a View or UDF is by using the Query Analyzer. Enterprise Manager, no matter what settings I choose, seems to create UDFs and Views with Incorrect SET settings and as such - I cannot index Views properly - Grrrrr.
B) When I finally do get a View to Index - I get this message: "The optimizer cannot use the index because the select list of the view contains a non-aggregate expression." when I try to do a Re-Indexing using:
DBCC DBREINDEX('MyIndexedView').
Simon had a brief comment regarding this message - but is it true that I MUST have at least one aggregate in a view in order for it to be indexed ???? DOn't see it in the documentation along with the other 5000 things you have to do to get a View to Index.
Yes I'm a bit testy at this point - sorry.
Oh - and if you attempt to reply to this message - be sure to include the word "ANSI_PADDING" or this board will not accept the message since it contains a Non-Padded expression ;|
L8r - B
November 7, 2002 at 2:42 pm
Um - duh -
Indexed Views only work with the Enterprise, Developer, and some other version of SQL Server - but not Standard.
So - I could set them up in Standard but they wouldn't work !!!!!!
I need to read my Robert Viera book again 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply