currently, as I understand, to build a large db, we need to pay attention to its size and performance. Below are what to reach:
Size: HDD has > 4tb to store filegroup if using partition tables.
Performance: Using partition tables. In diagram, we will not create relationship among tables. I heard that it will help us query data faster.
But, in reality, I don't know it is good solution or not, please advice me with some concerns:
if I have 100 milion records in each tables, and using parttion table, each quarter is 1 table/part. So, when I need to report in 1 year, how is its performance to query/filter data?
With 100 milion records, it is posible to query data faster with no relationship?
How about insert data with checking exist data first? it will take a long time to check duplicate before insert?
How about replicate data by using partition table?
I know that, some ways, this is the job of DBA, but I readly want to understand them, please advise me.
I'm very appriciate for you helps.