On Apr 24, 5:46*pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:
> "Benny" <bennyandli...@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

Thanks for the suggestion David. The problem is we already have a
very large database (almost 1TB) and would have a hard time moving the
column (which is a date) throughout all tables that need to go with
it. Is there any way to make reference to a child's parent table
column in a function?