Does Azure SQL DB support automatic indexed views?

First published on MSDN on Sep 04, 2016
Are indexed views used automatically in SQL Azure without " WITH (NOEXPAND) "?How does it work?and How the query optimizer decides to use it or not ?

The query optimizer uses indexed views to speed up the query execution. Which means SQL Server's query optimizer realizes the precomputed sums of all the Quantity values for each value are already available in the index for the view. The optimizer will evaluate the cost of using that indexed views in processing the query. But just because you have an indexed views doesn't mean the query optimizer will always choose it for the query's execution plan.

In fact, even if you reference the indexed views directly in the FROM clause, the optimizer might decide to directly access the base table instead,But why?

The optimizer decides whether the view's query tree matches the submitted query's query tree, then determines which is more cost-effective: using the indexed view or the base table. Optimizer might decide that using the base table is cheap enough and won't even bother determining a cost for using the indexed views.

So,we can’t always predict what the query optimizer will do!

Query optimizer will automatically consider the unique clustered index as an option for a query – but if it finds a “better” index, that will be used.

if you are  testing a query ,and your data is pretty small and the cost for selecting directly from the base tables maybe is cheaper than the estimated cost from the view,then indexed views won't be used . Taking in consideration that you can force the optimizer to use the indexed views by using the hint (NOEXPAND) .

-------------------------------------------------------------------

Below is an example,you can use to test the indexed views in SQL azure

--- Create the Table Test

CREATE TABLE Test(KeyUser [int] NOT NULL,Name varchar(200) NOT NULL)

--- Create the Table Test_

CREATE TABLE Test_(KeyUser [int] NOT NULL,Name varchar(200) NOT NULL)

--- Insert the 500000 rows in the Logins table (Keep in mind not  a small amount of data)

begin transaction

declare @k as int

set @k = 1

while @k <= 500000

begin

set @k = @k+1

INSERT INTO Test(KeyUser,Name) Values (@k, 'Example ' + convert(varchar(20),@k) )

end

commit transaction

--- Create the indexed views

CREATE VIEW dbo.vKey1 with SCHEMABINDING AS SELECT KeyUser, Name FROM dbo.Test

CREATE UNIQUE CLUSTERED INDEX ix4 ON dbo.vKey1(Name)

---Enable the actual execution plan and run the following T-SQL Text (Ctrl +M)

select Test.KeyUser, Test.Name from dbo.Test

inner join

Test_on Test_.KeyUser = Test.Keyuser

where

Test.Name like 'Example 1%'

-------------------------------------------------

Wait a moment!

YES ,The code proves that the  Azure SQL supports automatic use of indexed views without specifying the NOEXPAND hint.