Views in SQL Server

Question

Can we use views to improve the performance for a query?

Answer 1

Indexed views will improve query performance if they are designed properly.

http://technet.microsoft.com/en-us/library/cc917715.aspx

EDIT : Be aware that unless you are using Enterprise Edition or Developer Edition, you'll need to specify the WITH (NOEXPAND) hint to get the performance boost in your selections.

Answer 2

A non-materialized view can benefit from query plan caching, and depending on setup can support predicate pushing. Predicate pushing is where the optimizer determines that the WHERE clause on a view:

SELECT v.* 
  FROM VIEW v
 WHERE v.column = 5

...can be pushed into the query used to construct the view:

SELECT *
  FROM VIEW_TABLE(S)
 WHERE column = 5

Otherwise, a non-materialized view can be considered a macro -- a placeholder that expands into the underlying query. Which means that depending on the use, a view can perform worse than incorporating the underlying query into the outer query. Layering views on top of one another is not a wise practice, because errors won't be encountered until runtime (queries that use the views).

A materialized view (known as an indexed view in SQL Server) has at least one index on it, and can be as fast as querying a normal table.

Answer 3

You should at least see a minor performance gain with views as they will basically tell the DB engine that the tables are related.

That is if you have indexing enabled and properly configured for the tables.

Otherwise the minor amount of performance increase is just from the DB engine already knowing that it needs to run that basic statement.

Answer 4

I don't think so. It all depends on the indexes on the tables used in the queries. I don't think there is any "performance" gain in using views on any database. The only gain is that they do the join work for you.