Improving query performance by using views

Question

I have a large table with 10+ millions records in a SQL Server database. The table contains certain type of data for all 50 states in the US. So if I create 50 views, one for each state, from this table, would the performance of making queries from my application be improved? Other suggestions?

Answer 1

No. A view is a macro that expands so the same tables end up in the plan anyway.

Unless it's indexed. 50 indexed views is most likely overkill.

If you have slow performance with 50 million rows (it's not that much really) then it's an indexing problem.

Edit:

To start with, I'd use the weighted missing index dmv query to see where you get the most bang for your buck