How to create indexed view with select distinct statement in sql 2005

Question

My view named "Ge_Entry" is running very very slow so want to create indexed view with select distinct statement but sql server not allowing it.

So i want to know is there any way to create i.view with s.dist statement.

So that i can create indexes on this view for fast results.

Thanx

Neel

Answer 1

You can't use distinct. But you could use COUNT_BIG(*) and GROUP BY, possibly:

CREATE VIEW v1
WITH SCHEMABINDING
AS
    SELECT ColumnA,ColumnB,COUNT_BIG(*) as Cnt
    FROM BaseTable
    GROUP BY ColumnA,ColumnB
GO
CREATE UNIQUE CLUSTERED INDEX IX_v1 ON v1 (ColumnA,ColumnB)
GO

Instead of DISTINCT ColumnA,ColumnB

---

Most of the rules for creating indexed views appear to relate to the efficiency of maintaining the index. Imagine you have a base table with the following content:

ID        ColumnA        ColumnB
1         abc            def
2         abc            def
3         ghi            jkl

And you were allowed to create your view based purely on distinct. Now, you delete the row with ID 2 from the table. Should SQL Server remove the row abc,def from the view's index? It can only find out by scanning the entire base table again.

Contrast this with my above view definition. Here's what the views data looks like:

ColumnA      ColumnB        Cnt
abc          def            2
abc          def            1

If you delete the row with ID 2 from the base table, SQL Server can subtract 1 from the Cnt column for the row abc,def. If that column's new value is 0, it knows it should delete that row from the index. Conversely, if the value is >0, then it knows that there are still rows in the base table for this value.