October 10, 2002 at 10:02 pm
Folks,
I need to create Views that perform UNION queries on identically structured tables in multiple client databases. All the databases are in the same MSSQL installation, but each client gets their own db. For reporting, we need to perform queries against these tables -- and for various reasons the best solution is to create these views like so:
CREATE VIEW dbo.AggregateTable
AS
SELECT DISTINCT TOP 100 PERCENT Field1, Field2
FROM (
SELECT Field1, Field2
FROM client1.dbo.ClientTable
UNION
SELECT Field1, Field2
FROM client2.dbo.ClientTable
UNION
SELECT Field1, Field2
FROM client3.dbo.ClientTable
) DERIVEDTBL
ORDER BY Field1
This is MSSQL 2000, and I know it's possible to put indexes on views, which would certainly be useful. But I run into errors when I try to use the SCHEMABINDING statement, which seems to be related to the fact that the view is assembled from distinct databases.
Is there a workaround? Some of these views take a long time to run queries against!
Thanks,
- Tom
(And yes, if I could just pour all the raw data into tables and use replication or something similar, I would. But we are contractually obligated to not comingle client data -- so views are our only option.)
October 11, 2002 at 2:11 am
quote:
But we are contractually obligated to not comingle client data -- so views are our only option
Just remember that even if you use an indexed view you are essentially copying the data and storing it in another location.
As for the problem, as far as I know you can't do it.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
October 11, 2002 at 3:43 am
Actually have you considerd using a Stored Procedure instead. You can pass parameters to take advantage of the underlying indexes for each databases table. VIews do this already but occasionally make poor index decisions, but by using an SP you can run each query specifically and union as a whole and you get the bennefit of stored execution plans.
The way to build is to consider each SELECT individually and build them with the proper WHERE cluse and input fields, then UNION each together. Of course depending on how you query the data you may be making several of these as opposed to the views method.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 10/11/2002 03:45:33 AM
October 11, 2002 at 7:33 am
quote:
Just remember that even if you use an indexed view you are essentially copying the data and storing it in another location.
Is this true? A view actually gathers up and physically *stores* the data somewhere? How? Where? Wouldn't I be obliged to specify a location for that storage (like PRIMARY or Secondary) when I create a view then, like I do for a table?
I've always presumed a view was analogous to a telescope: it doesn't bring the moon closer, or make a copy of the moon, it just lets you see the details you want more easily.
quote:
As for the problem, as far as I know you can't do it.
Okay, good to know. Thanks, Chris.
October 11, 2002 at 7:43 am
quote:
The way to build is to consider each SELECT individually and build them with the proper WHERE cluse and input fields, then UNION each together. Of course depending on how you query the data you may be making several of these as opposed to the views method.
I had considered this, but not in quite this regard. And I didn't think that you could UNION the recordset results of stored procedures together. Do you mean something like so:
spGetClientTable1 'parameter1'
UNION
spGetClientTable1 'parameter2'
UNION
spGetClientTable1 'parameter1'
Part of the problem here is the new requirement we're trying to respond to. Our users have for the longest time, just logged in, picked a client, and worked against that client's data using our standard program. Now they want those users to work against a kind of "aggregated" meta-client -- with all the data from the various clients at once. BUT...we aren't allowed to physically comingle the data. So the best option we've come across is to create a new database, but rather than load it up with tables, we create one view per table, that does SELECT...UNIONs across all the others. That way, our existing code continues to work. (It's an ASP application with about 250 pages; rewriting those pages would be a terrific task.)
Thus my interest in just making views, rather than getting into writing a new stored procedure for every possible database call (worst case), or writing a library of generalized ones (somewhat less worse case).
- Tom
October 11, 2002 at 9:34 am
Tom,
In response to your question about views physically storing data, your understanding about views being like "telescopes" is correct for traditional views, but indexed views are different. An index is a data structure, and therefore it must be stored like any other data. Additionally, the first index you create on an indexed view must be a CLUSTERED UNIQUE index; since it is clustered, this means that all of the data in the view's result set will be stored as well. You use a CREATE INDEX statement to create indexed views, consequently you can specify where the data structure is placed when you execute that statement.
However, in your scenario, you cannot create indexed views. There are several restrictions on the types of statements that can appear in views that you would like to index. Among other things, UNION statements are not allowed. Additionally, all tables in the view must reside in the same database. Clearly, your scenario violates both of these principles.
If you'd like to understand indexed views better, look in Books Online for the article, "Creating and Indexed View," which is located in the "Creating and Maintaining Databases" book, under "Views: Creating a View." This will more thoroughly explain the restrictions imposed on indexed views, and how they work.
Matthew Burr
October 11, 2002 at 10:43 am
Matthew, Antares686 and Chris -
Thanks, all of you. Matthew, thanks for the elaboration on Chris' point -- which now of course makes much sense. If something is indexed, that index must exist as a separate-and-additional data structure. It's like the index of a book then, which must be printed on actual physical pages. I had it in my head that the index is something virtual, which of course it can't be.
So Chris, you're right: if I had made schema-binding work for this project (which I've learned I can't), we might well end up violating our client contracts.
I've posted variations on this problem before here in these forums. In fact, Antares686, I think you replied to them before. But if anyone has any suggestions for how to solve this puzzle without falling afoul of all these parameters (aggregating the client data, honoring the client prohibition against data-comingling, minimal rewrite of existing code, reasonable speed and responsiveness)...then please, shout it out!
Thanks again,
- Tom
October 11, 2002 at 11:05 am
quote:
I had considered this, but not in quite this regard. And I didn't think that you could UNION the recordset results of stored procedures together. Do you mean something like so:spGetClientTable1 'parameter1'
UNION
spGetClientTable1 'parameter2'
UNION
spGetClientTable1 'parameter1'
Sorry should have included an example.
Ex.
CREATE PROC sp_repData
@parm1 int,
@parm2 varchar(10)
AS
SET NOCOUNT ON
SELECT Field1, Field2
FROM client1.dbo.ClientTable
WHERE Field7 = @parm1
UNION
SELECT Field1, Field2
FROM client2.dbo.ClientTable
WHERE Field7 = @parm2
UNION
SELECT Field1, Field2
FROM client3.dbo.ClientTable
WHERE Field6 = @parm1
Or whatever fits you needs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply