I am curious as to what are the advantages of creating custom table spaces for Microsoft SQL Server 2008. As of now, and based on my limited experience all I can really tell/assume are the following
Creating custom table spaces organizes the physical files of the database therefore it might help improve the speed of disk reads
Disk reads might also improve if your table spaces are partitioned.
You will be able to clearly segregate database objects into separate physical locations (tables/clustered indexes/etc) Although I am not sure how this exactly works and how this might help improve the performance of the database
Now I'm not really sure if these assumptions of mine are correct and if so, what are the other advantages aside from what I have mentioned?
On the other hand, are there any quantitative measures that I can use in order to design my custom table spaces? For example, is there any mathematical formulas that I can use in order to set the growth size of table spaces according to the table's number of columns? Or is it more of predicting growth based on a timeline of gathering statistics of the table's usage?
One last thing, Is it much more efficient to segregate table spaces according to database object type? or according to logical design? (e.g. create separate table spaces for indexes and tables or create separate table spaces for each business module/function)
Please take note that I still have little experience with this and feel free to ask for any clarification if my question/s are not understandable
Edit: So I've read a bit about the mdf and ldf files and it seems to slightly address my problem with the growth of the database.
but still, Is there any importance of segregating per table space?
Edit 2: Okay based on further reading, if I am not mistaken, this basically falls on the category of segregating into different files/filegroups.. What advantages can I also expect from this aside from having to control the growth of the different mdf/ldf files, instead of maintaining just one big file?