We have two tables.... A with 70 fields and B 30 fields with the same primary KEY and same number of records(50 million records). Is it better to store them in one table or better to separate them in target?
NO, they do not have same fields. Only the PK is same for the two tables. My question is, is it better to have a table with 100 fields or is it better to have two tables with 70 and 30 fields. Once this is determined, i need to use this table with other tables for reporting purposes.
I would have them in same table with better indexing. Lookup for Table Partitioning. You can also think about normalizing table if it is used for Transactional system.
question is what are you storing in those 100 columns? will they have data atleast for good majority of rows? are they optional attributes? if yes you could consider other options too like going for EAV approach or using sparse columns etc
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
Two Source tables in to One Target table
Posted - 01/07/2013 : 13:59:08
