Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zrw
Starting Member
4 Posts |
 Posted - 2014-09-08 : 08:31:36
|
| Hello,I do a lot of database report writing and was wondering what option is better?1. Create a view per report?2. Create 1 large view with everything required across all reports?With option 1, this is preferred to me because if the report view requires changing it will not impact any other report. And only the data required is been returned.The second option is what is been pushed as "it will have less impact on the server"Do the number of views in a db impact performance?Wouldn’t returning large datasets have more of an impact then multiple views? |
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2014-09-08 : 10:10:12
|
| I would do the former, but that is said not knowing how many additional attributes you would have in this master view. I get this image of many attributes from many tables which would force you to make joins that may not be needed for many of your report, but rather only a satisfy the needs or a few or even one. that would not make sense.1 view per report would be a better way to go. Creating views that satisfy functional needs for shared logic would also be very helpful. For example: let's assume you have many reports that need calculated sales for your employees across many stores to determine bonuses (10% for sales over 1000). Perhaps this is accomplished by a calculation using an orders and table and joining with an employees table and maybe a location table. You could create a core view producing such a result that other views could use. Now lets say the company policy changes and reduces it to 8% over sales 1000 or need to exempt certain employees or locations. You could change the core view and not worry about any of your report views having to be updated or giving a different result with respect to bonuses. Much better than having the logic all in individual views and needing to go to each one to modify the code so that all views produce the same result. obviously if you have a attribute name change in the core view you would need to go and make changes in all the views using the core view, but if you are adding columns,changing calculation or join logic, or changing\ adding filters you will be guaranteed to produce the same results with respect to bonues or what have you in all your views using this core view . just some thoughts - hope this helps |
 |
|
|
zrw
Starting Member
4 Posts |
Posted - 2014-09-08 : 10:23:10
|
| yes that is great.Thanks for your reply. |
|
|
|
|
|
|
Views - less or more