Sql-server – Select Table Vs Select View

sql-server-2008

I was told from a SQL-DBA that there is a huge gap in performance between these statements, both statements are called from a stored procedure:

-- Procedure that select from table directly
Select * From [Table] Where condition

-- Procedure that select from a view
Select * From [View] Where condition

The SQL-DBA told me that selection from table is faster because when you select from a View, all data will be selected first then filtered, but from table it will apply filtration directly. I am a developer and I don't have much knowledge in database so I was hoping for some expert to advice me in this matter.

Upon answering this question, I think I will be able to know weather using Views is a best practice or not.

Best Answer

The SQL-DBA told me that selection from table is faster because when you select from a View, all data will be selected first then filtered, but from table it will apply filtration directly.

As a general statement this is nonsense. The view definitions are expanded out during query optimization and the predicates are pushed down as if you had filtered against the table directly.

You can check the execution plans to confirm this.

If you are filtering on the result of a calculated expression then this may be problematic however (the same as any unsargable expression on a table) and there have been issues with SQL Server not pushing predicates down as far as it could do for views containing window functions.

Related Question