Indexed Views in SQL Server 2005

re: Indexed Views in SQL Server 2005

FranckL — Sat, 17 Jun 2006 14:32:32 GMT

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

Query Processing -- introduction to the blog!

Tips, Tricks, and Advice from the SQL Server Query Processing Team — Tue, 26 Sep 2006 03:50:44 GMT

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

Query Processing -- introduction to the blog!

Tips, Tricks, and Advice from the SQL Server Query Processing Team — Tue, 26 Sep 2006 03:52:17 GMT

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

Tips Tricks and Advice from the SQL Server Query Optimization Team | Paid Surveys

Fri, 29 May 2009 22:30:30 GMT

PingBack from http://paidsurveyshub.info/story.php?title=tips-tricks-and-advice-from-the-sql-server-query-optimization-team