November 6, 2002 at 9:43 am
1 Question and 1 Statement:
Question) When I create an Indexed view as Clustered and Unique on the Base table's Serial ID - the Index is created, BUT I get the following message from SQL Server:
< Warning: The optimizer cannot use the index because the select list of the view contains a non-aggregate expression. >
WTH does that mean! And is it stupid do Index the ID column of a view for which there is an already indexed base table???
I Indexed this column so if I need to join the View to other tables - the SerialID column is indexed - seems logical to me...
Statement) When updating a table which is a source for and Indexed View, the Indexed View is immediately "Checked" or "Tested" against the table with the new data.
My Indexed view calls a Funciton called "GetSafePercent" which evaluates the denominator of a percent calculation and ensures it is not Zero. If I update the base tables data column, which happens to be the denominator, I get an error from the function which is called ONLY in the View. It took me a LONG time to find this problem - I thought my SQL Server was completely wacked. So - be careful with Indexed views !!!!!!
November 6, 2002 at 12:05 pm
Index views have been intoduced (I believe) to aid the performance of aggregate operations. If your index does not use aggregates then the view is generally broken down with the rest of the query when optimised.
So bottom line is index views are there so you don't have to perform the same aggregation every time the veiw is accessed.
Does'nt seem I have made it very clear?
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 7, 2002 at 9:25 am
Thanks Simon.
What if I have a SerialID column contain in the Indexed view and plan to join that view to other tables using said SerialID column.
Does it make sense to index that column in the view regardless of aggregates? Or am I missing something (I usually am...)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply