Horizontal partition - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Horizontal partition

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-06-21 : 08:31:13

Following on from my earlier thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36087

I have two databases which now have 80% of the tables "common" - currently the "child" database accesses the "Parent" using:


CREATE VIEW MyTableName
AS
SELECT * FROM OtherDatabase.dbo.MyTableName

the plan being to put them all in one database at some point in the future.

The tables that are different store stuff like Web Content (content for pages which the users can change themselves - T&C's, "How to buy", "News", "FAQ", that sort off stuff).

The IDs for the records in each of these need to be the same.

For example, assuming two web sites www.foo.com and www.bar.com T&C's ID record needs to be the same for each domain, so www.foo.com?PAGE=1234 and www.bar.com?PAGE=1234 will both give the T&Cs but the content will [most likely] be different.

This works curently because the MyContent table has a separate copy in each database.

However, I would like to combine them into a single database by adding an "attribute" column because:

That's the long term plan

Backup synchronisation would be better

Management would be easier (e.g. rollout of new versions)

Permissions issues would be less

however, in the short term, there is just not the time to make the necessary changes to the application.

So what I want to know is:

Can I do some sort of horizontal partition in the "Parent" database to allow me to have two sets of data on these few tables, but only ever deliver data from one of them [based on the UserID logged in, or something similar]?

The two domains could be set up to connect to SQL with different User IDs - lets say "FOO_IIS" and "BAR_IIS"

Could I have a VIEW (assuming my original table was called "MyContent") something like:


CREATE VIEW MyContent AS
SELECT *
FROM   MyContentFOO
WHERE  SESSION_USER = 'FOO_IIS'
UNION ALL
SELECT *
FROM   MyContentBAR
WHERE  SESSION_USER = 'BAR_IIS'

and then have an INSTEAD OF TRIGGER that deals with updates to the view - calling an SProc to do the UPDATE/INSERT/DELETE on the appropriate table

If that's a runner do I use SESSION_USER, SYSTEM_USER, USER, SUSER_SNAME() or something else?

Kristen
Test

22859 Posts

Posted - 2004-06-21 : 10:18:11

You folk over the pond not had your coffee yet?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources