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

  1. Creating custom table spaces organizes the physical files of the database therefore it might help improve the speed of disk reads

  2. Disk reads might also improve if your table spaces are partitioned.

  3. 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.

source: http://www.simple-talk.com/content/article.aspx?article=920

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?

  • If in any case I have misunderstood any concept that I might have used above, please feel free to correct me
    – Avias
    Feb 1, 2013 at 8:01
  • In SQL Server they're called data spaces. Feb 1, 2013 at 8:06
  • okay now I'm confused.. what is the difference between table and data spaces? @marc_s
    – Avias
    Feb 1, 2013 at 8:24
  • bumping on this
    – Avias
    Feb 1, 2013 at 9:50

1 Answer 1


A tablespace creates a layer of abstraction between physical and logical data

By using tablespaces, an administrator can control the disk layout of an installation. A common use of tablespaces is to optimize performance. For example, a heavily used index can be placed on a fast disk. On the other hand, a database table which contains archived data that is rarely accessed could be stored on a less expensive but slower disk.

  • Very useful insight @Urban Björkman, but in terms of using a single disk, what other advantage can I expect if there are any?
    – Avias
    Feb 1, 2013 at 8:41
  • 2
    Splitting the database into filegroups would force sql server to use multiple IO streams. If your disk is unable to handle it then you would most likley en up with worse performance. With a single disk system you probably get the best performance with one file in one filegroup
    – Urban Björkman
    Feb 1, 2013 at 9:01
  • Again a very useful insight. Thanks @Urban
    – Avias
    Feb 1, 2013 at 9:45
  • 1
    This only answers a part of my question and not all of it. I still need to know what the measurable advantages are and what would the efficient logical design be, whether to segregate by database object type or by using business modules.. all of this using a single disk system. :/ @Urban
    – Avias
    Feb 1, 2013 at 9:54
  • after reading some more stuff, you're right @Urban Doing table/data space separation on a single disk adds little value aside from fully utilizing multiple CPU cores (multi threading) and table partitioning which is a completely different concept.
    – Avias
    Feb 4, 2013 at 8:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.