"Benny" <bennyandlinds@gmail.com> wrote in message
news:5121ce0d-0fdf-465d-9036-0bb0726a8d41@34g2000hsh.googlegroups.com...
>I have a parent table with a column I want to drive partitioning off,
> but the child tables, as you can guess don't have that same
> information in them, hence a normalized database . Is there an
> approach to add the same criteria for child tables in the same
> partition? The only solution I could come up with is using an SSIS
> package that would run every night and get a list of the criteria and
> alter the scheme function. Anyone have other ideas?

Perhaps you could push the partition column down into the referencing table
and add a constraint to keep them in sync. Example follows. I haven't tested
this with partitioning though.

CREATE TABLE Foo
(FooKey INT NOT NULL PRIMARY KEY,
z INT NOT NULL /* partitioning column */,
UNIQUE (FooKey, z));

CREATE TABLE Bar
(BarKey INT NOT NULL PRIMARY KEY,
FooKey INT NOT NULL,
z INT NOT NULL,
FOREIGN KEY (FooKey, z) REFERENCES Foo (FooKey, z)
/* , other columns ... */);

--
David Portas