I was told to query against views in our ERP system because this would prevent any locks put on the actual tables thus blocking actual work being done. After learning a bit more about SQL Server, I'm not sure this is accurate but wanted to get clarification.
Knowing that a view is just a “saved query”, it appears I'm querying the base table via the view so it would seem this wouldn't accomplish anything as far as preventing blocking.
In other words if I do the following and leave these open, am I understanding that either one would block sales table writes?
begin trans
select * from dbo.view_sales
versus
begin trans
select * from dbo.sales
Thanks!
holdlock
andxlock
hint won't block unless the default isolation level has been changed on the db to one of the more optimistic levels. Based on the CPU/memory/IO and other factors of the server reading from the tables vs the view, if its materialized, will usually be more efficient as all of the data to fulfill the query is in the views data pages. Without the view being materialized I've never seen a difference in performance as it will just expand the view during execution. On the dev db use the expand hint with the view to see if there is a diff. – Aaron Jul 13 '17 at 21:53