SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Partitioning Scenario question.


Table Partitioning Scenario question.

Author
Message
SQListic
SQListic
Mr or Mrs. 500
Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)

Group: General Forum Members
Points: 516 Visits: 1115
Hi Experts,

Migrating warehouse from Oracle to SQL Server :
Status: In process

In Oracle : 6 Schemas
In SQL Server: Created a database with 6 Schemas.

-Every Schema has 3 Paritioned and 3 non partitioned tables.
-Out of 3 partitioned tables one is parent table and all other 5 tables are child tables.
-Only parent table has date column and non other table has date column for partitioning.
-all the 5 tables in each schema has primary key.


My Question is can I partition parent table with date column and child tables with Primary key column ?

If so please can someone advise the partitioning method for a parent table and child tables.

sometimes the partitioning column in parent table has abnormal dates like 2099,2088. How can i seperate those values. I listened that we can do by MAX and MIN dates while partitioning but not sure how to implement that..


Thanks for all your time.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5365 Visits: 4639
Sqlism (4/30/2013)
Migrating warehouse from Oracle to SQL Server :
Status: In process

In Oracle : 6 Schemas
In SQL Server: Created a database with 6 Schemas.

-Every Schema has 3 Paritioned and 3 non partitioned tables.
-Out of 3 partitioned tables one is parent table and all other 5 tables are child tables.
-Only parent table has date column and non other table has date column for partitioning.
-all the 5 tables in each schema has primary key.


Are those tables partitioned on the Oracle implementation?
If the answer is yes... how are they partitioned?
If the answer is no... why are you thinking on partitioning?


Sqlism (4/30/2013)

My Question is can I partition parent table with date column and child tables with Primary key column ?


Short answer is: Yes... followed by a question: Why? What are you trying to accomplish?


Sqlism (4/30/2013)

sometimes the partitioning column in parent table has abnormal dates like 2099,2088. How can i seperate those values. I listened that we can do by MAX and MIN dates while partitioning but not sure how to implement that..


How is the distribution of such 'abnormal" values? Either way, usually those are catched in a maxval partition.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
SQListic
SQListic
Mr or Mrs. 500
Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)

Group: General Forum Members
Points: 516 Visits: 1115
Are those tables partitioned on the Oracle implementation?
If the answer is yes... how are they partitioned?
If the answer is no... why are you thinking on partitioning?


---Yes these table are partitioned in oracle. In Oracle all tables have date column and they are partitioned on that column. But in SQL Server we removed those columns.



--------------------------------------------------------

My Question is can I partition parent table with date column and child tables with Primary key column ?
Short answer is: Yes... followed by a question: Why? What are you trying to accomplish?

I am worried because it might cause performance issues.


-----------------------------------------------------


If i have data from 2010 to present. How should i plan partitioning with maxval and minval partition please advise.


Thanks for your time and reply.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5365 Visits: 4639
Sqlism (4/30/2013)
I am worried because it might cause performance issues.


Table partitioning is not about performance but administration.

In most cases a partitioning strategy is based on thinking how to make your life easier when archiving, purging and other processes like these are in the horizon.

No performance gain is achieved just by partitioning a table - a sound indexing strategy and well designed queries are the key for good performance.

Hope this helps.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
SQListic
SQListic
Mr or Mrs. 500
Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)

Group: General Forum Members
Points: 516 Visits: 1115
Thanks Paul for your time.

If i have data for Eg from

2009
2010
2011
2012
2013

How should i plan the partitioning for unexpected data where the date is like 1988 or 2050

Please advise
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5365 Visits: 4639
Sqlism (4/30/2013)
Thanks Paul for your time.

If i have data for Eg from

2009
2010
2011
2012
2013

How should i plan the partitioning for unexpected data where the date is like 1988 or 2050


Create a partition that catches data with dates to the left of 2009/01/01 for th 1988 alike data and a partition that catches data to the right of the top year on your schema

http://www.databasejournal.com/features/mssql/partitioning-in-sql-server-part-3.html

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search