Friday, January 31, 2014

NOEXPAND Required in 2012 Enterprise

SQL Server 2012 Enterprise, build 11.0.2332.0. I've created a view that joins two tables, and has a computed column that includes components from both underlying tables. I've created a unique clustered index on the view on two columns, the first of which is the computed column. When I query the view like this:


SELECT * FROM schema.View WHERE ComputedColumn = '<some value>';


the optimizer does not use the view index, but instead goes directly against the underlying tables (slow). If I add WITH (NOEXPAND) to the query, it uses the view index and is very fast. I thought that NOEXPAND should never be required when using Enterprise edition for the view index to be considered. Interestingly, if I simply query for the count(*), the view index is used without specifying NOEXPAND.


What am I missing?


Thanks




Vern Rabe


No comments:

Post a Comment