I have recently read the text below in 70-762 Developing SQL Databases (p. 67)
:
The first benefit of using an indexed view is that when you use it Enterprise Edition of SQL Server, it uses the stored data in the index structure. For Standard Edition, it uses the code of the query unless you use a NOEXPAND table hint, in which case it uses the clustered index representation.
and since we have migrated from Enterprise to Standard (using SQL Server 2016 SP1) and have indexed view, I decide to check how this is affecting the performance. So, I executed the queries below:
DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;
SELECT *
FROM [dbo].[vw_my_view]
WITH (NOEXPAND);
and:
DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;
SELECT *
FROM [dbo].[vw_my_view];
and the second query was slower and the view definition was expanded, while in the first case, the clustered index scan was used. In this article is said, that NOEXPAND
can lead to better performance even on Enterprise Edition.
I am wondering, having the quote above, how indexed view is handled in Enterprise Edition, can we conclude that we should always used WITH (NOEXAPND)
in Standard Edition to get the similar behavior?
Why the behavior is not the same in all editions as we have free workaround?