Better for SQL Query performance to use an actual denormalized table with indexes rather than an indexed view?

Question

In order to improve the performance of a query I have created a denormalized indexed view that contains some of the information I need to report on. When I didn't get the performance gains that I had hoped for I created a table version of my view with indexes and got significantly better performance.

I should note that when I create my view there are a lot of ISNULLs in the SELECT. I know that these can hurt performance if these columns were joined on a regular view but I was under the impression that it would be OK if the view was indexed. Could the ISNULLs be the problem?

Answer 1

Did you index the columns you were actually selecting on? If you don't have a covering index on the indexed view for your query, then you will definitely find a table is quicker. If you do, though, there should be no real difference. Example:

CREATE VIEW dbo.denormalized
WITH SCHEMABINDING
AS
    SELECT  A.id,
            A.col1,
            A.col2,
            ISNULL(B.col3, '') col3
    FROM    dbo.A LEFT JOIN dbo.B ON A.Bid = B.id
GO

CREATE UNIQUE CLUSTERED INDEX UIX_denormlaized
ON dbo.denormalized (id)

So far so good. Now, we try to select from this view as follows:

SELECT id, col3 FROM denormalized

The only persisted data for this view is the index on the ID column - the remainder has to be workout out on the fly. So the ISNULL is calculated again for each row. However if we add this index:

CREATE INDEX IX_denormalized
ON dbo.denormalized (id, col3)

then the same query is served entirely from the persisted index - much quicker, in fact equivalent performance to selecting from a table.

Answer 2

What SQL Server SKU? Only Enterprise Edition considers indexed views in the query plan. Standard Edition will not consider the indexed view, unless the select is from the view and uses a NOEXPAND hint.

Update

Since I already got two comments indicating this is usefull to know, I'm linking the relevant MSDN page Resolving Indexes on Views:

Indexed views can be created in any edition of SQL Server. In SQL Server Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in all other editions, the NOEXPAND table hint must be used.