Indexed views in SQL Azure

Indexed views can be used to improve performance of complex queries or aggregations, but there are some differences between SQL server standard edition and SQL server Enterprise edition. In standard edition you need to specify the Query hint WITH(NOEXPAND), otherwise the query will use the code of the view to query the underlying tables instead of the indexed view, but thats not the case with Enterprise edition, the indexed view will be used without the hint. But what about SQL Azure?

Can I create indexed views? How does SQL Azure handle the indexed view? Well lets find out. Execute the script below and look at the result(don’t bother to worry about the lack of ORDER BY, its intentionally to demonstrate the use of indexed view, but in real life you shouldn’t depend on the order of the data unless you specify ORDER BY) –create a table– CREATE TABLE dbo.AzureTable1 ( c1 int NOT NULL, c2 char(10) NULL, c3 datetime NULL, CONSTRAINT PK_sample_table PRIMARY KEY (c1) ) GO –insert some data– INSERT INTO dbo.AzureTable1 VALUES (1,’C’,GETDATE()), (2,’B’,GETDATE()), (3,’A’,GETDATE()) –test the query and look at the order of the data- SELECT * FROM dbo.AzureTable1 –create the view– CREATE VIEW dbo.vAzureTable1 WITH SCHEMABINDING AS SELECT c1,c2,c3 FROM dbo.AzureTable1 GO –create the index on the view on another column– CREATE UNIQUE CLUSTERED INDEX ix1 ON dbo.vAzureTable1 (c2) –PRESS CTRL+M and execute the code below– SELECT * FROM vAzureTable1 –look at the order of the data SELECT * FROM vAzureTable1 WITH(NOEXPAND) –look at the order of the data –look at the execution plans–

Wait a minute! The code proves that the indexed view is not used unless you specify the NOEXPAND hint. Does this mean that SQL Azure works like Standard edition? Well thats what it looks like, at least in SQL Azure Web edition.

If you like to hear more about SQL Azure or SQL server, feel free to contact any of our SQL server consultants and we will tell you more. And don’t miss the SQLUG event in Kista the 6th October where I will speak about Azure.