Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ygeorge
Yak Posting Veteran
68 Posts |
 Posted - 2003-06-19 : 14:10:37
|
| When creating a data warehouse database, there are always some huge fact tables. Is it a good practice to spread a fact table to several data files? For example, I have a TB1 table which has hundred of million rows. Is it a good idea to spread the table on data files (such as DF1, DF2...). How to make it, and how many data files are proper?Thanks,George |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-19 : 18:28:27
|
| Yes, you can:A) Create a filegroup for just that table, and add multiple files to the filegroup. The table will be spread evenly across the files and can allow for parallel reads. However, this is only useful if each file in the filegroup resides on a separate physical disk (not just a logical partition), and you have no real control over exactly how the data is stored.orB) Look into partitioning the data into several tables and use a partitioned view to manage the querying. This is really only viable under SQL Server 2000, although it *can* be done in SQL 7.0 it's far more difficult to manage the INSERT, UPDATE and DELETE operations. You'd still need to set up separate filegroups for each table, and again it's only worthwhile if the files reside on separate disks.There are two options for partitioned views: horizontal and vertical. One has multiple tables with the same structure but fewer rows, the other has fewer columns but contains all of the rows in each table. You'd have to look at how you query the data to determine which is right for you, maybe even a combination of the two (not likely though). Books Online explains partitioned views better than I can, take a look. |
 |
|
|
|
|
|
|
|
Data warehouse database