Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cshah1
Constraint Violating Yak Guru
347 Posts |
 Posted - 2010-09-12 : 15:15:04
|
| Many years ago (almost ten) I thought Index View is more like an enterprise edition (SQL 2000) only feature but I was wrong and Index view were indtroducted in sQL 2000 to satifsy competivive product's support for materialize view.However, You can still create index view and physically materialize that view in all the edition of SQL 2000/2005 and query will use that index on a view if you specify NOEXPAND query hint (which is not needed in enterprise/developer editon)Here is the white paper on Index View (that confirm what I said earlier)[url]http://msdn.microsoft.com/en-us/library/dd171921.aspx[/url]However, it appears to me that starting with SQL 2008/R2 index view indeed is an enterprise edition feature. I did compare feature by different edition [url]http://msdn.microsoft.com/en-us/library/cc645993.aspx[/url]so in SQL 2008 R2 Standard edition you can create index view but looks like NOEXPAND hint will not work so it is almost useless...Is this expected behavior? |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2010-09-13 : 00:30:32
|
| noexpand | expand can still be used in SQL 2008 R2. no doubt about that |
 |
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2010-09-13 : 09:47:08
|
| Is it possible to create index view and use that index (instead of index on base table) in SQL Server 2008 R2 (standard or express edition) using noexpand hint? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-13 : 10:06:42
|
From Books Online 2008 R2:quote:
Indexed views can be created in any edition of SQL Server. In SQL Server Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in all other editions, the NOEXPAND table hint must be used.
I'd recommend creating the indexed view in your test environment and testing queries against it both with and without the NOEXPAND hint. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-13 : 10:26:30
|
| Pretty sure that Index View had restrictions in SQL 2000 only available in Enterprise Edition.It might just have been that Indexes on a View would be considered by the query optimiser for queries on the underlying table - too long ago, I can;t remember, but it may also have been that "indexes" (as such) on the view were only actually any use in the Enterprise Edition and just permitted, i.e. the Create statements for them, in the non-Enterprise versions.My recollection is that they came with other baggage (trouble trying to insert through Triggers on a View that had Indexes, or if there was a calculated column in the underlying table, or the need for some non-default ANSI setting to be in place, or one of those gotchas that you only find out downstream ... either way, we stopped using them because of one issue or another that I have now long since forgotten and has probably been fixed |
|
|
|
|
|
|
|
|
Index View Support - Ever changing saga..?