First let me state I am a C# developer, not a SQL Server DBA, so please excuse my ignorance on SQL Server database query execution plans.
What I want to know is for a stored procedure which the results are pulled from two or more tables using primary and foreign keys to reference them, is it better practice to:
- Have a view which performs the joins and contains flattened data for ALL corresponding records from the two (or more) tables and let the stored proc select from and filter that view
- Let the stored proc do the join and the filtering.
I ask because in a C# world we would separate out the concerns, so one object or function would be responsible for the joining (in this case the view) and another object or function would be responsible for the filtering (in this case the stored proc).
- Which option from above will perform better?
- Which option from above is considered best practice?
In C# separation of concerns is generally considered more important than performance until it is proven that performance has become a serious issue with the code. But I don't know who things stack up in the SQL world!