An Indexed View story....

An Indexed View story.

Playing with INDEXED VIEWS on Sql 2008 Enterprise today, these are some facts I found. Fussy creatures, these Indexed views...

Attempt 1)

create index ix_testindex on [Report].[myView] (id)

Msg 1939, Level 16, State 1, Line 1

Cannot create index on view 'myView' because the view is not schema bound.

(I went back to the view definition and added WITH SCHEMABINDING )

Attempt 2)

create index ix_testindex on [Report].[myView] (id)

Msg 10140, Level 16, State 1, Line 1

Cannot create index on view 'dbtest.Report.myView' because the view contains a table hint. Consider removing the hint.

OK, you caught me out, I had a dirty (NOLOCK) hint in there. I recreated the view without this, and ...

Attempt 3)

create index ix_testindex on [Report].[myView] (id)

Msg 1940, Level 16, State 1, Line 1

Cannot create index on view 'Report.myView'. It does not have a unique clustered index.

Fair enough, I added 'UNIQUE CLUSTERED' to index creation script...

Attempt 4)

create unique clustered index ix_testindex on  [Report].[myView] (id) with (data_compression=page,maxdop=1,online=on) ON [indexes]

Msg 1967, Level 16, State 1, Line 1

Cannot create a new clustered index on a view online.

Argh! I cannot create the index online (an Enterprise feature if you didn't know).

Attempt 5)

create unique clustered index ix_testindex on [Report].[myView] (id) with (data_compression=page,maxdop=1,online=on) ON [indexes]

Phew! , this worked.

Note : I'm using named filegroups for indexes.