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

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

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

Show all headers | View raw

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

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

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