SEO

#1 (**permalink**) 04-25-2008, 12:42 AM

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?

#2 (**permalink**) 04-25-2008, 12:42 AM

"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

#3 (**permalink**) 04-29-2008, 08:26 PM

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?

#4 (**permalink**) 04-29-2008, 08:26 PM

On Apr 25, 8:42*am, Benny <bennyandli...@gmail.com> wrote:
> 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?- Hide quoted text -
>
> - Show quoted text -

Create a view with the new column in the child table.

Regards,
Nagy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Partitioning Child Tables