CODEWITHSUNDEEP
sqlt-sql
mko
mko

Reputation: 7335

Set index on a table or a view?

I am testing couple of stored procs in SQL Man. studio and I received couple of suggestions on where and how to add indexes on a relevant table.

My dilemma is - is it better to add additional indexes to original table, or a better way would be to create a view from that table and set indexes on a newly generated view? I am currently setting index(es) on original table and hopefully that is a right thing to do.

Upvotes: 1

Views: 248

Answers (2)

DeadlyDan
DeadlyDan

Reputation: 699

Yes if I have performance issues on a table, I've found the SQL Server DMV suggestions on what indexes to add to your tables to be very helpful and really speeds up CRUD performance issues on the table.

One word of warning though, after you add a suggested index to a table its important to test if the performance has improved or gotten worse.

The pros and cons of views is well documented here

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453898

It's much better to do it on the original table.

Even on Enterprise Edition indexes on views are much less likely to be matched and used than on the base table directly.

Indexed views are typically more useful when wanting to materialise a more complex subset of the data (e.g. a pre calculated aggregate query).

Upvotes: 3

Related Questions