Groups > comp.databases.ms-sqlserver > #1129

Re: Indexed views availible in all versions of mssql 2012?

From	Lennart Jonsson <erik.lennart.jonsson@gmail.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Indexed views availible in all versions of mssql 2012?
Date	2012-06-18 12:09 +0200
Organization	A noiseless patient Spider
Message-ID	<jrmupd$6nm$1@dont-email.me> (permalink)
References	<jrmpr8$amv$1@dont-email.me> <XnsA0767390D5D4Yazorman@127.0.0.1>

Show all headers | View raw

On 06/18/2012 11:21 AM, Erland Sommarskog wrote:
[...]
> You can create indexed views in any edition of SQL Server. 
> 
> However, it is only in Enterprise and Developer Edition that the optimizer 
> will consider using an indexed view in a query plan on its own initiative.
> 
> If you run a query like:
> 
>    SELECT * FROM myindexedview
> 
> SQL Server first expands the view definition, before optimisation, and the 
> optimiser thus have to rematch it. Which will not happen in Standard or 
> Express Edition. However you can add a hint:
> 
>    SELECT * FROM myindexedview WITH (NOEXPAND)
> 
> that prevents expansion from occurring, and in this way an indxed can be 
> utilised also in Standard and Express.
> 

Thanks for the information Erland. Much appreciated.


/Lennart

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

Indexed views availible in all versions of mssql 2012? Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2012-06-18 10:45 +0200
  Re: Indexed views availible in all versions of mssql 2012? Erland Sommarskog <esquel@sommarskog.se> - 2012-06-18 11:21 +0200
    Re: Indexed views availible in all versions of mssql 2012? Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2012-06-18 12:09 +0200

csiph-web