Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Data Warehousing / April 2008

Tip: Looking for answers? Try searching our database.

Partitioning Child Tables

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Benny - 24 Apr 2008 19:16 GMT
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?
David Portas - 24 Apr 2008 23:46 GMT
>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
[quoted text clipped - 3 lines]
> 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 ... */);

Signature

David Portas

Benny - 25 Apr 2008 13:42 GMT
On Apr 24, 5:46 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:

> >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
[quoted text clipped - 22 lines]
> --
> 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?
naggy - 25 Apr 2008 14:54 GMT
> On Apr 24, 5:46 pm, "David Portas"
>
[quoted text clipped - 37 lines]
>
> - Show quoted text -

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

Regards,
Nagy
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2010 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.