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

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

Path	csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail
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	Mon, 18 Jun 2012 12:09:48 +0200
Organization	A noiseless patient Spider
Lines	25
Message-ID	<jrmupd$6nm$1@dont-email.me> (permalink)
References	<jrmpr8$amv$1@dont-email.me> <XnsA0767390D5D4Yazorman@127.0.0.1>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	7bit
Injection-Date	Mon, 18 Jun 2012 10:09:49 +0000 (UTC)
Injection-Info	mx04.eternal-september.org; posting-host="bflSdaxROoSQaSvhiBWUVA"; logging-data="6902"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19la/b/qnmzp4e/HQp9+I21"
User-Agent	Mozilla/5.0 (X11; Linux i686; rv:12.0) Gecko/20120430 Thunderbird/12.0.1
In-Reply-To	<XnsA0767390D5D4Yazorman@127.0.0.1>
Cancel-Lock	sha1:vCdTGwKTY+y0S/ta/5u4L9u2+TY=
Xref	csiph.com comp.databases.ms-sqlserver:1129

Show key headers only | 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