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

Question

To improve query performance, I created a denormalized indexed view containing some of the information I need to report. When I didn’t get the performance gain that I was hoping for, I created a tabular 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 SELECT. I know that this can hurt performance if these columns were merged in a regular view, but I had the impression that it would be normal if the view were indexed. Can problems with ISNULL?

Answer 1

Did you specify the columns that you actually selected? If you do not have a coverage index in the indexed view for your query, you are sure to find a table faster. However, if you do this, 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 will try to choose the following from this view:

SELECT id, col3 FROM denormalized

The only data stored for this view is the index in the ID column - the rest should be a warm up on the fly. Thus, ISNULL is computed again for each row. However, if we add this index:

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

- , .

Answer 2

SQL Server SKU? Enterprise Edition . , NOEXPAND.

, , , MSDN :

SQL Server. SQL Server , . , NOEXPAND .