There's no doubt that writing SELECT * FROM viewCostumerAddress much simpler and intuitive than SELECT c.id,c.name,a.id,a.fullAddress FROM customer c JOIN address a on a.id_costumer=c.id ORDER BY a.id,c.Priority, but when a herd of people are telling you that "the view's performance is way better" and your tests does not show such gain what you do?
All tests that I have conducted were using the same SQL Server 2014. The dataset had about 2kk clients and 2.5kk addresses. At no point, the servers memory got over 60% consumption. The cold-queries tests were always followed by a complete service restart to "force-clean" any pre-compile queries and any cached info. The results for hot-queries were only recorded after each query/view got hit 200 times, filtering as much different values as possible. All tests were conducted 1000 times on a dedicated, not virtual server (bare-metal).
And yet, the end results varied less than 1% (0.971%) for hot-queries and almost 0 for cold-queries (0.0024%).
In face of these results I came to wonder if in a modern RDBMS world and "object-relational mappers", does the old-claim of performance gain still valid reason to query data from views instead of tables directly?
PS: Please try to be as scientific as possible in your answer, providing testing methodology (if the case) and/or scientific paper or be as specific and thorough in the case of reporting a event that you witnessed.
if you have a normal view which is not an indexed view there is no performance gain. This is because, when you select from the view, SQL-Server runs the T-SQL-Statement as it would do without a view.
These are some of my thoughts on views:
1. views are security layers
I personally do not want anybody get direct access to my base tables. In a view I can filter out rows based on the permission of the caller. And up to SQL Server 2014 you can't do this with tables.
2. views are returning specific set of columns
If I have to change the schema of underlying tables, I can ALTER the view in a manner, that it returns the same columns as before. If you do not use views and the user SELECTs from the base tables himself, queries might fail.
3. Views are saving development-time
You do not have to write the same T-SQL-Statement over and over again. And what if there is an error in the T-SQL-Statement? Just fix it in one place, in the view. You might not have to change the application or many functions and Stored Procs.
4. Using indexed view to Speed up SELECTs
When you see that your INNER JOIN in the view is too expensive and there are no more other options (like useful indexes or schema-changes) you can use a indexed views to speed up SELECTs. But you may have performance decrease on INSERTs, UPDATEs and DELETEs because the index of the view has to be updated too.