Partitioning using Date - TimeKey (Ref to another table) column

Hello.
I'd like to implement table partitioning for one of my tables in my DB
(Sales History). I'd like to setup partitioning using a date. I'm planning
to use a partition per year that are going to be spread among multiples
Filegroups. This table contains an integer field that link to my TimeKey
table to get the Date of the transaction. I would know how to setup
partitioning if my date was directly in my table but since I have to join to
another table, how can I achieve this? I can't use a range from my integer
since they're not really sorted sequentially. Any help would be
appreciated. Thanks!"Christian Hamel" <chamel@.notyourbusiness.com> wrote in message
news:e8gEWeTEGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I'd like to implement table partitioning for one of my tables in my DB
> (Sales History). I'd like to setup partitioning using a date. I'm
> planning to use a partition per year that are going to be spread among
> multiples Filegroups. This table contains an integer field that link to
> my TimeKey table to get the Date of the transaction. I would know how to
> setup partitioning if my date was directly in my table but since I have to
> join to another table, how can I achieve this? I can't use a range from
> my integer since they're not really sorted sequentially. Any help would
> be appreciated. Thanks!
>
Not what you want to hear but I'd be inclined to allocate an intelligent
time key to start with. If it's keyed on date only then use the date as an
8-digit number in the form yyyymmdd.
Possibly you could create a computed column that derives a date from your
key and use that as your partitioning column.
David Portas
SQL Server MVP
--|||I DON'T WANT TO HEAR THAT!
:)
That's what I thought, it is not a big deal since I'm @. the beginning of
implementing our new DW using SQL Server 2005 so I will definitively
consider your "intelligent" key suggestion. Thanks!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le
message de news: OQMwB1TEGHA.376@.TK2MSFTNGP12.phx.gbl...
> "Christian Hamel" <chamel@.notyourbusiness.com> wrote in message
> news:e8gEWeTEGHA.532@.TK2MSFTNGP15.phx.gbl...
> Not what you want to hear but I'd be inclined to allocate an intelligent
> time key to start with. If it's keyed on date only then use the date as an
> 8-digit number in the form yyyymmdd.
> Possibly you could create a computed column that derives a date from your
> key and use that as your partitioning column.
> --
> David Portas
> SQL Server MVP
> --
>