2

I asked here about counting the number of times a value from table a occurs in table b, but after it was solved, I realized I couldn't use a left join (or right or outer), since I'm going to use the results in an indexed view, which doesn't allow for those joins to be used.

So, if those restrictions apply, is there any way to do the following?

I have two tables: products and orders. Orders references products via ProductID as a foreign key. I want to know how many times each product has been sold, including the product never having been sold.

Is there a way to solve this that will have you ending up with something like this?

Product | Times sold
Milk    | 5
Bread   | 18
Cheese  | 0
Community
  • 1
  • 1
Petter Brodin
  • 2,069
  • 4
  • 21
  • 27
  • Doesn't look like it: http://blog.sqlauthority.com/2009/09/25/sql-server-outer-join-in-indexed-view-question-to-readers/ Lots of other results from searching *LEFT JOIN indexed view*. – Yuck Jan 30 '12 at 17:34
  • 1
    I would highly suggest that you move this over to dba.stackexchange.com – NotMe Jan 30 '12 at 17:38
  • 1
    Just about *every* restriction on indexed views stems from the following: You have to be able to recalculate the stored values in the view based *solely* on the (information contained in the) rows that have been affected by an operation on the underlying table(s). Once you realize this, you'll realize that there aren't likely to be any workarounds – Damien_The_Unbeliever Jan 30 '12 at 17:41
  • @ChrisLively Couldn't agree more. This really belongs on DBA.SE since the reason you can't create an index on a view of this nature involves the internal workings of maintaining such an index. A DBA is much more qualified to answer that question. – Yuck Jan 30 '12 at 17:41
  • you need a left join if you explicitly want to see the products that were never sold on your query. And why does your view needs to be indexed? – Diego Jan 30 '12 at 17:34
  • I'm going to run a full-text query on the view. (My example is simplified when it comes to what columns I'm getting from the tables, but the general problem remains.) – Petter Brodin Jan 30 '12 at 17:40

1 Answers1

2

There's no direct workaround - but you could construct an indexed view on Orders (so that the aggregates are being computed on it, and stored in an index), and have a non-indexed view based on a join between Products and Orders. You should still benefit from the index.

(Insert usual caveats re: using NOEXPAND, or working on Enterprise/Developer Edition)

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • What are your thoughts on creating a table on the results of the query with an index so that it can be used in a full-text search? The table could be kept in-sync with `Orders` and `Products` through use of triggers. Too much infrastructure? – Yuck Jan 30 '12 at 17:46
  • @Yuck - writing the triggers, and ensuring they maintain correct totals in all cases (including edge cases), and keeping everything performing well could be a challenge. As I say, I'd construct the index purely on Orders, then rely on that index as being sufficient, but I'm not sure on the FT indexing implications. I *generally* wouldn't be looking to create a single data structure that supports cheap aggregation and FT indexing (they seem too different) – Damien_The_Unbeliever Jan 30 '12 at 17:53