Indexed Views in SQL Server 2005


I’d like to point you to a white paper that Eric, one of our program managers, did on Indexed Views in SQL 2005.  This logic mostly applies to the Enterprise Edition of our product.


http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx


This white paper focuses on the functionality in our product.  If you have questions or things that we can make clearer, please let us know and we’ll roll them up into the next revision of this (that goes for any of our published works, btw).


I’ll try to put together some additional literature on indexed views/materialized views from the academic literature, as this is a topic that can take some time to digest.


Thanks,


Conor


Comments (4)

  1. FranckL says:

    Great article!

    I have a question regarding computed columns, functions and indexed views.

    In an indexed view, I’m able to create indexes on (persistent & precise) computed columns, I’m able to create indexes on functions and scalar expressions, but I don’t seem to be able to create an index on an expression that contains at least one computed column. Is that by design? How can I work around it?

    Example: CREATE VIEW MyView WITH SCHEMABINDING AS

    SELECT id, simpleCol, computedCol, FLOOR(simpleCol) as test1, FLOOR(computedCol) as test2, simpleCol +1 as test3, computedCol +1 as test4 FROM table

    GO

    CREATE UNIQUE CLUSTERED INDEX Cindex ON MyView(id) -> success

    CREATE NONCLUSTERED INDEX index1 ON MyView(simpleCol) -> success

    CREATE NONCLUSTERED INDEX index2 ON MyView(computedCol) -> success

    CREATE NONCLUSTERED INDEX index3 ON MyView(test1) -> success

    CREATE NONCLUSTERED INDEX index4 ON MyView(test2) -> error 1901

    CREATE NONCLUSTERED INDEX index5 ON MyView(test3) -> success

    CREATE NONCLUSTERED INDEX index6 ON MyView(test4) -> error 1901

  2. Since we’re starting a new blog for the whole query processing team, we think it’s appropriate to start…

  3. Since we’re starting a new blog for the whole query processing team, we think it’s appropriate to start…