Wednesday 18 August 2010

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.

3 comments:

Unknown said...

So, you mean to say that the fourth attempt which is exactly like the third was successful on Enterprise? Or you had to switch the online option off to make it work, hadn't you? I'm just trying to do exactly that and I get this 1967 message popping up.

r5d4 said...

Had to switch online OFF.

It would have been nice if SQL had told me everything that was wrong first time round, i.e a list of errors!

Unknown said...

Okay, then the last script reads wrong because it clearly says that online option is on. No matter, though, it's just a typo.