Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1127 > unrolled thread

Indexed views availible in all versions of mssql 2012?

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1127 — Indexed views availible in all versions of mssql 2012?

I'm trying to determine in what versions of mssql-2012 indexed views are
available, but so far I have not seen it mentioned anywhere. Oracle
supports materialised views and and DB2 supports materialised query
tables in there enterprise versions. Is mssql more generous in this
respect, or am I just a lousy investigator?


Cheers
/Lennart

[toc] | [next] | [standalone]

#1128

Lennart Jonsson (erik.lennart.jonsson@gmail.com) writes:
> I'm trying to determine in what versions of mssql-2012 indexed views are
> available, but so far I have not seen it mentioned anywhere. Oracle
> supports materialised views and and DB2 supports materialised query
> tables in there enterprise versions. Is mssql more generous in this
> respect, or am I just a lousy investigator?
 
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.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1129

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web