Difference between Index on Table and Index on View

Question

Hello  all,

I need your hep to understand the below query

Assume if  i have a table with two columns (column1,Column2) and a view with Select Column1,Column2 on this base table.

SQL server allows to create a unique index on column1 for table at table level

and

Unique index on column1 at view level.

My question is what is the difference in these 2 scenarios?

When people go for indexed views instead of creating indexes at table level?

Thanks & Regards

Samba

Answer 1

The index on both table or view basically serves to speed up searches.

The main thing is: views normally do not have indexes. When you add a clustered index to a view, you're basically "materializing" that view into a system-maintained, always automatically updated "pseudo-table" that exists on disk, uses disk space just like a table, and since it's really almost a table already, you can also add additional indices to an indexed view.

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Read the article "Creating Indexed Views" in books online; there are many restrictions that must be observed in order to create indexed views; therefore, there are many different kinds of views in many different situations that can be created but cannot be indexed.

Answer 3

In your scenario where the view is only referencing one base table you would be duplicating data (due to the materialisation of the view). The only possible reason to create the view would be to abstract it out for security reasons at the application layer. That said if you created an index on the base table and referenced the view the optimizer would chooose to go to the base table as it knows it is the same thing.

Bottom line,  in the scenario you described creating an index view would create an overhead when compared to creating an index on the base table.

---

If you find this helpful, please mark the post as helpful,
If you think this solves the problem, please propose or mark it an an answer.

Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

Richard Douglas
My Blog: Http://SQL.RichardDouglas.co.uk
Twitter: @SQLRich

Answer 4

Hello,

Refer the link below. It will help you.

http://stackoverflow.com/questions/2595136/what-is-the-difference-between-an-table-index-and-a-view-index