Does the View in database reduce the query performance?



Not really!! Here is why...

Few days back, I was speaking about one of our newly created Filter API with server side pagination to my friend. In the implementation we were querying the view to get the data. Since the pagination was at the server side, at a time the query will return only 10 records. His question was since views internally going to use the tables for querying, will it end up being less performative? Will it create the result set of all data and then pick the top 10 items?

My initial reaction was to agree with question and confess we have an issue. But I was sure that this can not be the case. If this is so then we could have had severe issues. I am not much of a DB guru myself so went and did some research with others to figure out the story behind the scenes. By the way we use MS SQL Server 2008 and I am not sure if this descriptions applies to other database or not. 

The question to ask was. We know that the views are used to simplify other queries or standardize access to data. but do we compromise on speed while doing so? 

I got a related answer from SO. When an SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view and then resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view. So it is fine to use the views for my above use case.

Further, I got to know about Indexed views from this discussions, which can further improve the performance. Microsoft has a nice documentation on Improving Performance with SQL Server 2005 Indexed Views.