Microsoft SQL Indexed Views Clustered

Microsoft SQL Indexed Views Clustered.

--To create an indexed view the view must use WITH SCHEMA BINGING clause.
--After the view is created you need to add a UNIQUE CLUSTERED INDEX in order to hold data in view.
--Once unique clustered index is added then you can add non-unique clustered index.
--You must use COUNT_BIG(*) instead of COUNT(*) for indexed views

create view vw_Direction_Count
with schemabinding
as
SELECT ofd.[Direction], COUNT_BIG(*) as count_all
  FROM [dbo].[options ftse] as ofd
  group by [Direction]
-- view your report in view.
  select * from vw_Direction_Count;

-- All calculated columns like count_big must have an alias name in a view.

--create a unique clustered index on direction column.

create unique clustered index idx_Direction_Count
on vw_Direction_Count([direction])

-- The query runs quicker and extracts the report from the index.
--Note: If you want to update the base table, then you will have to drop the indexed view.
--Once updates are done to the base table, then you can recreate your view.