August 19, 2010 at 9:14 am
I just inherited a new SQL Server project. I noticed that the previous developer did not refer to tables in his stored procedures. All references are made to views. For example:
SELECT Field1, Field2 FROM vwProducts INNER JOIN vwCustomers WHERE vwProducts.CustomerID=vwCustomers.CustomerID
There are hundreds of stored procedures and all of them do this. Is this considered best practice? Are there exceptions to the rule? All security is in place, including permissions to tables, and views, etc.
Thanks in advance
August 19, 2010 at 9:24 am
I wouldn't necessarily consider this a bad practice, since it's not creating any extra work. If you weren't using the view, you would have to use the source tables, and then probably still apply the same filters/transformations that the view is already doing (creating a larger execution plan).
That said, 99.9% of the queries ever written can probably be improved in some way, either by reducing the amount of data it needs to read, using better indexes, etc.
Unless you're having performance issues or other kinds of problems, I don't see any reason to change it.
August 19, 2010 at 9:41 am
Ray Cacciatore (8/19/2010)
I just inherited a new SQL Server project. I noticed that the previous developer did not refer to tables in his stored procedures. All references are made to views. For example:SELECT Field1, Field2 FROM vwProducts INNER JOIN vwCustomers WHERE vwProducts.CustomerID=vwCustomers.CustomerID
There are hundreds of stored procedures and all of them do this. Is this considered best practice? Are there exceptions to the rule? All security is in place, including permissions to tables, and views, etc.
Nothing wrong with using views.
You may want to take a deeper look and check why original designer went that way, sometimes views are used for security purposes, sometimes to encapsulate a particular dataset.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply