Create partitioned view on linked tables

Question

I'm learning partitioning tables in SQL Server and I got stuck with this problem: I have 2 linked tables - parent and child. One of them (or maybe even both) is a partitioned table. When I'm implementing a partitioned view do I need to include 2 identical columns in it - the one that references parental table (from child) and the primary key that is being referenced (from parent)?

I'm having troble with it since MSDN says:

Column Rules:

All columns in each member table must be included in the select list. SELECT * FROM is acceptable syntax.

But views should make the representation of (linked) tables easier, so not all the columns should be included in view.

And in my case, according to MSDN I have to include all the columns of both tables into a view (and 2 of them would be identical). It seems to me as not very logical solution.

For example:

Database 1:

create table manufacturer 
(
    id                 int,
    manufacturer_name  varchar(35),

    PRIMARY KEY (id),
    CONSTRAINT CHK_manufacturer_id
        CHECK (id < 1000)
);

create table product 
(
    pid           int,
    product_name  varchar(35),
    mid           int,

    PRIMARY KEY (pid),
    CONSTRAINT CHK_product_pid
        CHECK (pid < 1000),
    CONSTRAINT FK_product_mid
        FOREIGN KEY (mid)
        REFERENCES manufacturer(id)
);

Database 2:

Same tables with CHECK constraints (id >= 1000)

View:

create view dist_view as
    select * 
    from db1.product p1
    inner join db1.manufacturer m1 on p1.mid = m1.id

    UNION ALL

    select * 
    from db2.product p2
    inner join db2.manufacturer m2 on p2.mid = m2.id

So the view with look like

pid | prod_name | mid | id | manufact_name

and mid = id.

In my opinion a view like this contradicts the main advantage of using views - simple representation of tables. So I would like to have a view like this:

(pid) | prod_name | manufact_name

How do I solve this?

Answer 1

First, you probably should not bother learning about partitioned views. The correct way to do partitioning is using partitioned tables, rather than views.

This is repeated in the documentation. For instance:

Note

The preferred method for partitioning data local to one server is through partitioned tables. For more information, see Partitioned Tables and Indexes.

(And I note that your queries are all on one server.)

Second, a partitioned view is created by union all on base tables. You are using joins, so that is just a regular view.

I would suggest that you re-evaluate your data structures and think more about partitioned tables than partitioned views.