Indexed Views

Indexed Views are like magic in terms of performance. The data is fully contained in an index over the view and is available instantly, saving a huge amount of time when accessing this data. The trade-off is the extra space used to store the index and the extra incremental processing time required to keep the index updated as the underlying data changes.

Indexed Views have the following limitations:

• They cannot be based on other views (only base tables)
• They cannot include the same table twice.
• No LEFT OUTER JOIN statements are permitted.

When joining with Indexed Views in queries, you must specify """WITH (NOEXPAND)""" otherwise the index will not be used. This extra statement stops the query optimiser from attempting to include the source of the view rather than use the index.

A template for indexed views follows:

if exists(select * from sys.indexes where name = 'UNDX_my_indexed_view')
begin
	exec('
		DROP INDEX [UNDX_my_indexed_view] ON [dbo].[my_indexed_view] WITH ( ONLINE = OFF )
	')
end
GO
--------
if exists(select * from sys.views where name = 'my_indexed_view')
begin
	exec('DROP VIEW [dbo].[my_indexed_view]')
end
GO
--------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--------
CREATE VIEW [dbo].[my_indexed_view] WITH SCHEMABINDING 
AS	
	SELECT	 a.GRP
		,Sum(isnull(b.VAL,0)) AS Total_Amount
		,count_big(*) as counter
	FROM
		dbo.table_a a
	inner join
		dbo.table_b b
			on	b.FK = a.ID 	
	GROUP BY a.GRP
GO
--------
CREATE UNIQUE CLUSTERED INDEX [UNDX_my_indexed_view] ON [dbo].[my_indexed_view]
(
	[GRP] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO