SQL Server Performance Forum – Threads Archive

Clustered Indexes on Views

I have tried to create a clustered index on the view – but get the following error
‘Msg 1941 – Nonunique clustered index cannot be created on view ‘%.*ls’ because only unique clustered indexes are allowed.’
Cant find any decent documentation on this, any help appreciated. Cheers

---

Hi ya, check out "indexes, on views" in Books Online. Unfortunately the clustered index does need to be unique for it to be allowed on a view. After this you can create non-clustered non-unique indexes. What are you hoping to achieve with the indexed view? They are really only useful for DSS where you often need to summarise data… Cheers
Twan

---

And also check thishttp://www.sqlteam.com/item.asp?ItemID=1015 link. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

Thanks, good link on indexed views.
Using the view to partially replace complicated stored proc which was taking to long to return contrived data. works well in a view, but now want to join the views results within another query and takes considerably slower, so thought if I create an indexed view, the join would be quicker.

---

The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create additional nonclustered indexes. The naming conventions for indexes on views are the same as for indexes on tables. The only difference is that the table name is replaced with a view name. As suggested by Twan go thru books online for more information. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

]]>