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
> --
>
Showing posts with label partitioning. Show all posts
Showing posts with label partitioning. Show all posts
Monday, February 20, 2012
PARTITION'ing Types
Hello All,
I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
2000.
I have to implement PARTITION feature that is in SQLServer 2005 and I was
going through BOL and I just wanted to clarify.
Are there Different Types of PARTITIONs. I'm asking since I would want to
chose the one that is optimal.
Thanks,
rgnHi rgn
It was originally planned that you could have either a RANGE partition or a
HASH partition, but HASH partitions did not make the cut. We only have RANGE
partitions.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:618481E3-2D7F-481D-9012-7B311D80BB8A@.microsoft.com...
> Hello All,
> I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
> 2000.
> I have to implement PARTITION feature that is in SQLServer 2005 and I was
> going through BOL and I just wanted to clarify.
> Are there Different Types of PARTITIONs. I'm asking since I would want to
> chose the one that is optimal.
> Thanks,
> rgn
>
I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
2000.
I have to implement PARTITION feature that is in SQLServer 2005 and I was
going through BOL and I just wanted to clarify.
Are there Different Types of PARTITIONs. I'm asking since I would want to
chose the one that is optimal.
Thanks,
rgnHi rgn
It was originally planned that you could have either a RANGE partition or a
HASH partition, but HASH partitions did not make the cut. We only have RANGE
partitions.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:618481E3-2D7F-481D-9012-7B311D80BB8A@.microsoft.com...
> Hello All,
> I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
> 2000.
> I have to implement PARTITION feature that is in SQLServer 2005 and I was
> going through BOL and I just wanted to clarify.
> Are there Different Types of PARTITIONs. I'm asking since I would want to
> chose the one that is optimal.
> Thanks,
> rgn
>
PARTITION'ing Types
Hello All,
I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
2000.
I have to implement PARTITION feature that is in SQLServer 2005 and I was
going through BOL and I just wanted to clarify.
Are there Different Types of PARTITIONs. I'm asking since I would want to
chose the one that is optimal.
Thanks,
rgn
Hi rgn
It was originally planned that you could have either a RANGE partition or a
HASH partition, but HASH partitions did not make the cut. We only have RANGE
partitions.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:618481E3-2D7F-481D-9012-7B311D80BB8A@.microsoft.com...
> Hello All,
> I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
> 2000.
> I have to implement PARTITION feature that is in SQLServer 2005 and I was
> going through BOL and I just wanted to clarify.
> Are there Different Types of PARTITIONs. I'm asking since I would want to
> chose the one that is optimal.
> Thanks,
> rgn
>
I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
2000.
I have to implement PARTITION feature that is in SQLServer 2005 and I was
going through BOL and I just wanted to clarify.
Are there Different Types of PARTITIONs. I'm asking since I would want to
chose the one that is optimal.
Thanks,
rgn
Hi rgn
It was originally planned that you could have either a RANGE partition or a
HASH partition, but HASH partitions did not make the cut. We only have RANGE
partitions.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:618481E3-2D7F-481D-9012-7B311D80BB8A@.microsoft.com...
> Hello All,
> I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
> 2000.
> I have to implement PARTITION feature that is in SQLServer 2005 and I was
> going through BOL and I just wanted to clarify.
> Are there Different Types of PARTITIONs. I'm asking since I would want to
> chose the one that is optimal.
> Thanks,
> rgn
>
PARTITION'ing Types
Hello All,
I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
2000.
I have to implement PARTITION feature that is in SQLServer 2005 and I was
going through BOL and I just wanted to clarify.
Are there Different Types of PARTITIONs. I'm asking since I would want to
chose the one that is optimal.
Thanks,
rgnHi rgn
It was originally planned that you could have either a RANGE partition or a
HASH partition, but HASH partitions did not make the cut. We only have RANGE
partitions.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:618481E3-2D7F-481D-9012-7B311D80BB8A@.microsoft.com...
> Hello All,
> I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
> 2000.
> I have to implement PARTITION feature that is in SQLServer 2005 and I was
> going through BOL and I just wanted to clarify.
> Are there Different Types of PARTITIONs. I'm asking since I would want to
> chose the one that is optimal.
> Thanks,
> rgn
>
I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
2000.
I have to implement PARTITION feature that is in SQLServer 2005 and I was
going through BOL and I just wanted to clarify.
Are there Different Types of PARTITIONs. I'm asking since I would want to
chose the one that is optimal.
Thanks,
rgnHi rgn
It was originally planned that you could have either a RANGE partition or a
HASH partition, but HASH partitions did not make the cut. We only have RANGE
partitions.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:618481E3-2D7F-481D-9012-7B311D80BB8A@.microsoft.com...
> Hello All,
> I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
> 2000.
> I have to implement PARTITION feature that is in SQLServer 2005 and I was
> going through BOL and I just wanted to clarify.
> Are there Different Types of PARTITIONs. I'm asking since I would want to
> chose the one that is optimal.
> Thanks,
> rgn
>
partitioning the table
I have some tables in the large production database which suppose to grow hi
gh and obviously the time consumption on making query on those tables will b
e really high, so is there any way by which I can partition the table, I mea
n the records entered befor
e a particular date will go into some separate partition and will be archive
d on requirement the query can be passed to that partition.
Hope this could be the way to handle the large tables. Or if you can suggest
some ways or links to handle very large database then I'll be thankful for
you.
Thanks in advance
Regards,
SunilHi Sunil.
You can use partitioned views to do this. These can either be local or
distributed accross physical servers.
Do these tables have identities? If so, this is a gotcha but I have a work
around if you do.
Regards,
Greg Linwood
SQL Server MVP
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:4BC00EDE-6F44-4B1C-ACB0-F77D2035DE53@.microsoft.com...
high and obviously the time consumption on making query on those tables will
be really high, so is there any way by which I can partition the table, I
mean the records entered before a particular date will go into some separate
partition and will be archived on requirement the query can be passed to
that partition.
suggest some ways or links to handle very large database then I'll be
thankful for you.
by the design of table|||Partition the tables and use partitioned views to consolidate... You should
do lots of load testing to make sure this scales to suit your needs...
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:4BC00EDE-6F44-4B1C-ACB0-F77D2035DE53@.microsoft.com...
high and obviously the time consumption on making query on those tables will
be really high, so is there any way by which I can partition the table, I
mean the records entered before a particular date will go into some separate
partition and will be archived on requirement the query can be passed to
that partition.
suggest some ways or links to handle very large database then I'll be
thankful for you.
gh and obviously the time consumption on making query on those tables will b
e really high, so is there any way by which I can partition the table, I mea
n the records entered befor
e a particular date will go into some separate partition and will be archive
d on requirement the query can be passed to that partition.
Hope this could be the way to handle the large tables. Or if you can suggest
some ways or links to handle very large database then I'll be thankful for
you.
Thanks in advance
Regards,
SunilHi Sunil.
You can use partitioned views to do this. These can either be local or
distributed accross physical servers.
Do these tables have identities? If so, this is a gotcha but I have a work
around if you do.
Regards,
Greg Linwood
SQL Server MVP
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:4BC00EDE-6F44-4B1C-ACB0-F77D2035DE53@.microsoft.com...
quote:
> I have some tables in the large production database which suppose to grow
high and obviously the time consumption on making query on those tables will
be really high, so is there any way by which I can partition the table, I
mean the records entered before a particular date will go into some separate
partition and will be archived on requirement the query can be passed to
that partition.
quote:
>
> Hope this could be the way to handle the large tables. Or if you can
suggest some ways or links to handle very large database then I'll be
thankful for you.
quote:|||Design the table again for the larger table, I think the problem is caused
>
> Thanks in advance
> Regards,
> Sunil
by the design of table|||Partition the tables and use partitioned views to consolidate... You should
do lots of load testing to make sure this scales to suit your needs...
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:4BC00EDE-6F44-4B1C-ACB0-F77D2035DE53@.microsoft.com...
quote:
> I have some tables in the large production database which suppose to grow
high and obviously the time consumption on making query on those tables will
be really high, so is there any way by which I can partition the table, I
mean the records entered before a particular date will go into some separate
partition and will be archived on requirement the query can be passed to
that partition.
quote:
>
> Hope this could be the way to handle the large tables. Or if you can
suggest some ways or links to handle very large database then I'll be
thankful for you.
quote:
>
> Thanks in advance
> Regards,
> Sunil
Subscribe to:
Posts (Atom)