views:

475

answers:

1

I want to create an indexed view that integrates data from several tables, however SQL Server complains with the error message:

Cannot create index on view "MyView". The view contains a self join on "dbo.Companies".

The view definition is something like this (simplified version):

SELECT  T.Field1
      , T.Field2
      , P.CompanyName AS ProviderName
      , C.CompanyName AS CustomerName
      , T3.Field1
FROM dbo.Table1 T 
                  INNER JOIN dbo.Companies P ON T.ProviderId = T2.Id
                  INNER JOIN dbo.Companies C ON T.CustomerId = T2.Id
                  INNER JOIN dbo.Table3 ON T.Id = T3.Id

Is there any workaround for this case? I don't want to split the Companies table in two tables.

Thanks in advance.

+2  A: 

You won't be able to work around this, the indexed views must conform to certain restrictions enumerated in Creating Indexed Views. Among other things, outer and self joins are not supported (10th restriction from top to bottom). It boils down to the engine ability to be able to update the view index when the base table is updated.

Not knowing exact all the details of your data model, are you sure that an indexed view is necessary and won't the base table indexes suffice?

Remus Rusanu
Thanks for your answer. I'm using the base tables now, but I wanted to simplify my queries and get a performance boost as a bonus. One of the lists my app shows to users, needs to join multiple tables (4 or 5), do some column concatenations (surname + name) and it also has a couple of 'case' statements. I think it makes the perfect case for a view (indexed if possible), but this restriction is on my way.
JAG
Well those arguments are not very strong for an *indexed* view. Surname+name can be a persisted computed indexed column in the *base* table if you need to seek on it, and most likely the CASEs too. The joins could make a good case for an indexed view, depending on several factors, but then you can always separate the non-self join joined tables into a view and index that, then reference the already indexed view into your final view to benefit the index seeks on it. Again, is more of a guess from me w/o all the info.
Remus Rusanu
Even if I use computed persisted columns to avoid the column concatenations I still need to join the main table with those that contains the computed columns. Also note that I have not a self-referencing table, I just reference the same table twice in a query. The SQL Server error is confusing and not exact.I'm going to take your advice and go with two views. If I understand you correctly, you propose to create an indexed view with out one of the tables and the create another view joining the remaining table, right?Thanks for your feedback, I really appreciate it.
JAG
Creating an indexed view to avoid joins is a bit unusual in my book. Well indexed tables and proper join conditions produce very good results most of the time. If your joins are so complex as to need an indexed table (which really means you build a de-normalized view of the data model and ask SQL to maintain it for you) they'll probably bee too complex to meet the indexed view criteria anyway. And you dig yourself into Enterprise Edition only with indexed views.
Remus Rusanu
What is your read vs. write ratio in the data you plan to create the indexed view on?
Remus Rusanu
I didn't know about the Enterprise Edition bit, which automatically discards this option as most of my customers are using the Express Edition. Just for the record, this query is executed a lot to present a list of records to the user, so I was trying to find ways to improve its performance. I'm going to mark your answer as accepted; together with your comments it's been very helpful to me. Thanks again.
JAG
The problem with EE is this: the Query Optimizer will *ignore* the indexed view as an option, *even when selecting from the view* on any edition under EE. The only way to use the indexed view is to select from the view using the NOEXPAND hint. See http://msdn.microsoft.com/en-us/library/ms181151.aspx
Remus Rusanu
The only way on non-EE I mean
Remus Rusanu
Ok, so NOEXPAND will force the Query Optimizer to use the view. I'll explore and test my options. Thanks for your detailed answers.
JAG