Share via

Indexed Views in SQL Server 2005

  • Article

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.

https://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

  • Anonymous
    June 16, 2006
    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





  • Anonymous
    September 25, 2006
    Since we’re starting a new blog for the whole query processing team, we think it’s appropriate to start...
  • Anonymous
    September 25, 2006
    Since we’re starting a new blog for the whole query processing team, we think it’s appropriate to start...
  • Anonymous
    May 29, 2009
    The comment has been removed