Does Azure SQL DB support automatic indexed views?
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.