I was having a discussion the other day and was quite surprised to hear that there still appears to be a slight misconception regarding being able to use indexed views on web or standard editions of SQL server. As it stands the documentation clearly states that you can create and use indexed views on web, standard, BI and enterprise editions of SQL server (I'm ignoring developer + evaluation as they are the same as enterprise). You can create a view, materialise that view in the form of a unique clustered index and then write queries that utilises that index in web editions and above. However there is a feature in enterprise edition that adds value to indexed views by having the optimiser automatically consider them during plan compilation without any changes to the t-sql. For non-enterprise editions of SQL, to force the optimiser to use the indexed view then you must specify the NOEXPAND table hint to stop the view from 'expanding' to access the tables referenced by the view. MSDN states 'The query optimizer treats the view like a table with clustered index'

But, you can create and use indexed views on all editions from express upwards....

So first of all, I'm going to show how indexed views work on enterprise edition and show that the optimiser will automatically consider an indexed view if it deems to be the best choice without any changes in the logic:

A simple example script that creates two tables, adds some data to both and then creates a view referencing both tables:

CREATE TABLE MyOrders

(

   OrderId INT,

   CustomerId INT,

   OrderDate DATE,

   CONSTRAINT PK_MyOrders PRIMARY KEY CLUSTERED (OrderId)

)
GO

CREATE TABLE MyOrderDetails

(

   OrderId INT,

   LineId INT,

   ProductId INT,

   SalePrice DECIMAL(10,2),

   CONSTRAINT PK_MyOrderDetails PRIMARY KEY CLUSTERED (OrderId, LineId)

)
GO

ALTER TABLE dbo.MyOrderDetails
ADD CONSTRAINT FK_OrderId_Orders FOREIGN KEY (OrderId) REFERENCES dbo.MyOrders (OrderId)
GO
INSERT INTO dbo.MyOrders (OrderId, CustomerId, OrderDate) VALUES (1, 1, GETDATE())
INSERT INTO dbo.MyOrders (OrderId, CustomerId, OrderDate) VALUES (2, 2, GETDATE()-1)
INSERT INTO dbo.MyOrders (OrderId, CustomerId, OrderDate) VALUES (3, 3, GETDATE()-2)
INSERT INTO dbo.MyOrderDetails (OrderId, LineId, ProductId, SalePrice) VALUES (1, 1, 1, 10.00)
INSERT INTO dbo.MyOrderDetails (OrderId, LineId, ProductId, SalePrice) VALUES (1, 2, 2, 500.00)
INSERT INTO dbo.MyOrderDetails (OrderId, LineId, ProductId, SalePrice) VALUES (1, 3, 3, 7500.00)
INSERT INTO dbo.MyOrderDetails (OrderId, LineId, ProductId, SalePrice) VALUES (2, 1, 1, 20.00)
INSERT INTO dbo.MyOrderDetails (OrderId, LineId, ProductId, SalePrice) VALUES (2, 2, 2, 600.00)
INSERT INTO dbo.MyOrderDetails (OrderId, LineId, ProductId, SalePrice) VALUES (2, 3, 3, 8500.00)
INSERT INTO dbo.MyOrderDetails (OrderId, LineId, ProductId, SalePrice) VALUES (3, 1, 1, 30.00)
INSERT INTO dbo.MyOrderDetails (OrderId, LineId, ProductId, SalePrice) VALUES (3, 2, 2, 700.00)
INSERT INTO dbo.MyOrderDetails (OrderId, LineId, ProductId, SalePrice) VALUES (3, 3, 3, 9500.00)
GO

CREATE VIEW MyOrdersAndDetails_View
WITH SCHEMABINDING
AS

SELECT O.OrderId, CustomerId, OrderDate, LineId, ProductId, SalePrice
FROM dbo.MyOrders O

INNER JOIN dbo.MyOrderDetails D ON D.OrderId = O.OrderId
GO

Now if we were to simply select all records from the view, we would end up with a no surprise execution plan like:

So next lets materialise the view by creating a unique clustered index against it:

CREATE UNIQUE CLUSTERED INDEX UCIX_OrderId_LineId ON MyOrdersAndDetails_View (OrderId, LineId)

and run the exact same identical select * query against the view, we now get this execution plan:

You can see that the optimiser has chosen not to 'expand' the view to the base tables, but to use the data from the unique clustered index on the view and you can see that the query is identical in both plans.

To compare, lets do exactly the same steps on a 2012 Express edition:

After creating the tables, data and view as per the same script above (not the index yet though) and selecting all records from the view we are given the expected following plan which is identical to the enterprise version before the index was created:

Now create the same unique clustered index as before against the view, and run the same select * query:

CREATE UNIQUE CLUSTERED INDEX UCIX_OrderId_LineId ON MyOrdersAndDetails_View (OrderId, LineId)

Still the same plan as the one without the index, so the optimiser has not considered the use of the index. But this is as per the documentation i.e. considering indexes on views being an enterprise only feature.

But we can force the optimiser to use the index by using the NOEXPAND hint:

SELECT * FROM dbo.MyOrdersAndDetails_View WITH (NOEXPAND)

So running the revised query on my 2012 express instance produces this new plan:

The optimiser has used the index to satisfy the query.

It is hardly surprising that this misconception exists as the official documentation is a bit misleading even suggesting that the feature doesn't exist in Express edition when it clearly is. There is also a lot of wrong advice out there and persistent misinformation.

Hopefully you can see that you can create and use indexed views on express, web, standard, BI and enterprise editions. The difference is that for non-enterprise editions, you have to 'force' the optimiser with a hint to use the index whereas enterprise editions will automatically consider the index and use it.

Indexed views do come with a lot of restrictions and in many situations you won't be able to use them and because you are materialising the data, any DML to the base tables of the views will take that bit longer due to the additional work that it has to perform keeping the index up to date. I've used them with quite a bit of success in the past even developing specially so I could use an indexed view. But on other occasions I have also ripped them out in favour of refactoring elsewhere to get performance improvements. You need to bear in mind that you are creating a clustered index so you are materialising the entire view and not just the key columns. For complicated views with large columns this can amount to quite a substantial overhead. So just because you can use them in all editions it seems, it doesn't necessarily mean you should be looking to put an index on all your views! 🙂

Enjoy!

Follow me on twitter @sqlserverrocks

Subscribe to my blog RSS feed

Comment on this or any of my posts or contact me directly from http://www.olcot.co.uk