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


Existing Table division


Existing Table division

Author
Message
nick.mcdermaid
nick.mcdermaid
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 Visits: 778
Those are very general benefits but it sounds like you've convinced yourself anyway.

If you are talking about having a seperate detail and summary table then it's not vertical partitioning because it isn't a 1:1 relationship.
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5294 Visits: 4076
nick.mcdermaid (2/6/2013)
Those are very general benefits but it sounds like you've convinced yourself anyway.
Honestly speaking database design is under physical design phase so cant tell whether my approach is good or not ? thats the reason i posted the question here.


nick.mcdermaid (2/6/2013)
If you are talking about having a seperate detail and summary table then it's not vertical partitioning because it isn't a 1:1 relationship.
i didnt get this , please explain .what i inderstand here is for every claimID from claim table there must be a record in claim_summary table.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
nick.mcdermaid
nick.mcdermaid
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 Visits: 778
Honestly speaking database design is under physical design phase so cant tell whether my approach is good or not ? thats the reason i posted the question here.


I really think you're over analysing it for that number of records. As far as DBA performance goes nowadays the focus is less on splitting components of tables over physical disks and more about getting everything into RAM. (as compared to when I did the SQL 2000 MCDBA) You will seriously see no performance advantage in vertically splitting data accross disks if you have only a couple thousand records.

i didnt get this , please explain .what i inderstand here is for every claimID from claim table there must be a record in claim_summary table.


If that's the case then yes it's 1:1. My mistake - usually a 'summary' table is a summary of some kind of detail and has less rows and less detail but in your case the summary appears to be additional generated information.



Looking at your original question, are the claim summary calculations based solely on columns in the claims table? Because you can simply use calculated columns in the same table for that. Then you don't need SSIS at all (and I would argue you still don't need it anyway)


I am of the opinion that a simple design is a good design and adding SSIS and filegroups and vertical table partitioning to a table with a few thousand rows is way over the top. But everyone has made good contributions here and some valid reasons have indeed been given to split it.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15021 Visits: 14396
I tend to agree that for a few thousand rows you'll find that the effort to split the tables will not be worth the gains you might receive, not to mention that it may actually perform poorly in some scenarios. That said it may still be worth trying to implement if you have the time to code it and test it. If you decide not to implement it in production you will at least have gone through the process of making an informed decision and there is value in that. Just make sure you're weighing the maintenance costs going forward with the performance gains you may achieve.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
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