recruz


What are the downsides to have many multiple views on a SQL Server Database

It was somebody's design decision to create 10 views for each vendor in our database- that could range from 20 to 500 vendors ... which means we could potentially have 5000+ views in our database.

What is the downside/problems with doing this




Re: Hundreds Of VIEWS

cgraus


Is it not possible to create ten views that allow you to specify the vendor







Re: Hundreds Of VIEWS

Blair Allen Stark

generally, I would say no, not much downside. Depends on if they are indexed or not.

if they are indexed views, I could see some slowdown as datachanges are made, but an increase on selections.

this is with regards to SQL 2000

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3661.mspx







Re: Hundreds Of VIEWS

recruz

I would love to do that- but apparently we can't because of what we are trying to accomplish-

Maybe I'll ask again why we can't- but it has to do something with Microsoft SQL Server 2005 Reporting Services not being designed for multiple vendors.





Re: Hundreds Of VIEWS

cgraus

But surely a vendor is an entity in your database, and SQL Server doesn't care about it Do you have a table that maps vendor names to IDs If so, you can pass that ID into a view, surely

I'm thinking of your maintenence problems more than anything. Can you imagine changing 500 views






Re: Hundreds Of VIEWS

recruz

It has nothing to do with the way our tables are structured-

They are absolutely well normalized and I can easily group them by their respective ID's-

It has to do with Microsoft SQL Server 2005 Reporting Services not being able to separate out our reports amongst our vendors-





Re: Hundreds Of VIEWS

Blair Allen Stark

wait a second. . .

you mean, create views like


Re: Hundreds Of VIEWS

cgraus

That's what I said, but he believes that SQL Server Reporting Services can't deal with that.






Re: Hundreds Of VIEWS

recruz

Yea see- that would actually be the smart thing to do- and I would be overjoyed if I could do something so wonderfully simple.

But as I said, Microsoft Reporting Services cannot pass in variables to the report builder function that it has- it can only take a specific view and query on it-

but that wouldn't prevent our vendors from seeing other vendors' information - which is the requirement.





Re: Hundreds Of VIEWS

Brett Hickenbottom

You've been misinformed about the limitations of Reporting Services. There should be absolutely no reason why you cannot use a parameterized stored procedure or query to get the results you need from a single view (or 10, depending on why you need 10).



Re: Hundreds Of VIEWS

recruz

Feel free to explain if you can.

We are talking about Report Models here- not just a single report. Report Models deal with Report Builder.

So if you have an answer, I'm still awaiting one from somebody- anybody ...





Re: Hundreds Of VIEWS

walter_verhoeven

Why not make the vendorID in your table the same as your vendors loginid then you could use logic like where vendorID = @SUSER_SNAME the one view could then use this.

I guess it is a lilte late to do that now but you could add a "managment table" joining the Vendor to the loginname and then use a join on this "Management table" in your view.





Re: Hundreds Of VIEWS

Umachandar Jayachandran - MS

I am moving the thread to the Reporting Services forum for better answers to this question since this is not a SQL issue.




Re: Hundreds Of VIEWS

Xrider

I bet you can pass variables to "whatever" feeds the report.

But, if for any reason you can't...

Create your "base" 10 views and "inherit" from them for all the vendors.

like that:

baseView1

Vendor1View1 = select * from baseView1 where vendorid = vendor_1_id

etc...

now.. you just have to change baseView1 and all you 500 vendorViews will be ok.





Re: Hundreds Of VIEWS

recruz

This issue still exists for us, and I fear it creeping up against us in the near future- so basically, Report Models use views- and when referencing the view, the system does not allow you to pass in a parameter to call a stored procedure or anything in order to filter out individual vendors' data. (SQL Server Reporting services was not designed to be used by multiple vendors- it is designed to be used in conjunction with a single development shop, where all the data is available to everyone)

So, if anybody else is having this issue, please let me know that at least I'm not crazy.

-rob