3

Does Azure SQL Database support indexed views? Does it support automatic indexed view matching without NOEXPAND? Does the answer depend on the pricing tier?

I was unable to find definitive answers to these questions. The feature differences guide does not mention indexed views.

The indexed view documentation does not contain the word "Azure".

1

I was just searching for an answer to this and I found an MSDN blog entry from 2016 that suggests that Azure SQL does support indexed view matching and may (in at least some circumstances) use indexed views without NOEXPAND. From the linked article:

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

The blog entry also provides a code example that the author uses to support their assertion. I won't reproduce it here but check out the article at: https://blogs.msdn.microsoft.com/azuresqldbsupport/2016/09/04/does-azure-sql-db-support-automatic-indexed-views/

0

Just don't get disappointed when you try to assert your thinking onto the database - and it doesn't agree!

I have an indexed view right now where a complex query (which does not mention the view by name) was able to figure out that a nonclustered index on the view would work perfectly - I was impressed.

But a second nonclustered index on the same view couldn't be triggered without NOEXPAND even though I only queried and SELECT-ed a single column from the view AND mentioned the view by name!

So yes it can work in some very impressive ways - but can also fail in some super simple ways.

I read somewhere that sometimes it 'doesn't even bother to check if the indexed view is more efficient' if it determines that expanding can be good enough!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.