May 28, 2007 at 1:31 am
Hello,
I am going to building partitioned tables and I have read some articles/white papers about it, bat I need some informations.
I have read that all tables involved on partitioned tables needs to have a datatime column in common.
The problem is that I have to apply partitioned tables on some table already created in a old database, they are key referenced and not have a datatime col in common.
How can I do?
Any help will be very appreciated.
Thank
May 28, 2007 at 5:06 am
Partitioned tables don't need any specific scolumn types in them. The common example is to partition on a datetime column (ann rows between 1 jan 2005 to 31 dec 2005 in partition 1, 1 jan 2006 - 31 dec 2006 in partition 2, etc)
All the partitions need to have the same columns and data types (since its a single table in a few peices) and you need to decide on the partitioning column and function.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 28, 2007 at 7:12 am
Hello,
I make an example: T1, T2 and T3 are tables:
and they have the following cols:
T1(t1_pk (int), col2 (varchar), col3 (varchar), col4 (varchar), createtime (datetime))
T2(t2_pk(int),t1_pk(int), col5 (varchar))
T3(t3_pk(int),t2_pk(int), col6 (varchar)).
My question is:
Can I make T1, T2 and T3 partitioned tables, when there is a relationship between T1 and T2 with FK, and T3 with T2, but they have not a common column (in this case createtime) on which to make partition function?
So, I want to partion the tables above but I want to maintain referential integrity between them after partitioning.
What I have seen, all tables should have same col (createtime) in common in order to partition the tables
Thank
May 28, 2007 at 8:01 am
I think you've misunderstood partitioned tables.
Partitioned tables are for when you want to split a single table into pieces either for IO performance or for fast loading and deleting of data.
Examples of partitioned tables are things like a customer table, partitioned by the first letter of the surname or order history, partitioned by the month of the order.
From what you said, you've got three seperate tables with relationships between them, not a table with partitions
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 28, 2007 at 9:12 am
Hello,
thank for reply.
I know what is partitioning table, so I want to use this new feature to divided data into "partitions" when table increase too much, this is very helpful in performance, reporting and maintenance (for this reason I thought to partitioning).
Of course, the 3 tables that I said before are 3 tables in the OLTP without Partitions, but I want to apply partition on them, because are too big.
The problem is this:
think that T1 is a very large table, so it stores data , just an example, from 2000. So because there are lots of data I want to separate this values into different partitions (performance, reporting and so on) may be every year...just as example.
If I had a DB with only T1, this very easy to apply partitioning, but the problem is that T1, is linked with T2, then T2 with T3, then T3 with T4 and so on... Tn with Tm.
So I partition T1, Do I have to partition all other tables correleted with it? or not? (I think yes...so all data belong to 2000 are in the same partition for all tables). Correlation is 1-->n so it means that T2 has more data then T1, T3 more data than T2 and so on, so you understand that tables are very big going through the relation tree.
This my problem, I want to know how to apply partitioning table in this case, when lots of tables are corraleted between them but without a common column (datetime in my case if want to separate partitions by year) and so separated them into different file groups.
I want to know if this case, partitioning table is applicable?
I have read a good article from MS: "SQL Server 2005 Partitioned Tables and Indexes", here only 2 tables (Orders and OrderDetails) are shown to explain partitioning and both have a field in common (orderDate on page 24) and they are linked through purchaseorderID. OrderDate is used to create the partitions.
In my example only one table have datetime column (for year separations), the other ones no.
Thank
May 30, 2007 at 12:15 am
Ah, sorry. I misunderstood. I thought you wanted to convert three tables into 1 partitioned table.
As I said before, the examples may show partitions done on datatime columns, but you can partition on any data type (other than varchar/nvarchar(max), varbinary(max) and xml)
You don't need to correlate the partitions of joined tables. It may give a performance benefit if you do, but is not required. You don't even need to partition all related tables. You can partition just one of them if you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2007 at 1:03 am
Thank,
but the problem is that the size of corralated table increase (T1, T2, T3 and so on are corraleted), so I need to partition them.
but I want to maintain a correlation with them and I think it is better to have same data column on them, in my opinion it looks strange to have correlated tables but partition them in different way.
In the previous post I speak about datatime because I want to partition them by time.
Any other contribution will be very appreciated.
Thank
May 30, 2007 at 1:08 am
Then partition them. Use whatever parition scheme makes the most sense for the tables
They don't all have to have the same partition function. Related tables don't have to be correlated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2007 at 6:08 am
I would think the child tables would be partioned by whatever method you use to link them now to the main table (i.e. the FK). Just would be my natural inclination, but have not studied up on partioned tables in detail yet.
May 30, 2007 at 1:41 pm
Hello,
thank for replying. Any further help will be very appreciated
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply