I've been talking to some people at work who believe some versions of a database store their data in discrete tables. That is to say you might open up a folder and see one file for each table in the database then several other supporting files. They do not have a lot of experience with databases but I have only been working with them for a little over a half year so I am not a canonical source of info either.

I've been touting the benefits of SQL Server over Access (and before this, Access over Excel. Great strides have been made :) ). But, other people were of the impression that the/one of the the benefit(s) of using SQL Server over Access was that all the data was not consolidated down into one file. Yet, SQL Server packs everything into a single .mdf file (plus the log file).

My question is, is there an RDBMS which holds it's data in multiple discrete files instead of one master file? And if the answer is yes, why do it one way over the other?


Thank you everyone for you're answers. This has really helped clarify the whole situation.

  • 2
    Why does it matter to you how the data's actually stored? It seems that you'd be more interested in what the RDBMS can do with it - querying, flexibility, ease of installation, etc. – Michael K Apr 5 '12 at 19:11
  • Yeah, that's fine. It's just a matter of curiosity really. This is not going to make me pick one system over another, and I'm certainly not moving off SQL Server at this point. It's really just a talking point. Something to get me started to learn more. – Brad Apr 5 '12 at 19:13

Databases usually use one big file for performance reasons. The DB engine can use its own internal structure and not have to restrict itself to file system limits on block size, buffer size, or fragmentation policies. I know some systems used to (and possibly still do) provide their own block device drivers. For those systems, you'd simply hand them a raw disk partition and let the database handle its own storage management, cutting out a level of indirection and speeding up all disk operations. It's also easier to keep the database in contiguous blocks on disk the fewer files you have to fragment.

| improve this answer | |
  • there's not a whole heck of alot databases can do to circumvent the disk block-size; even if they are writing directly to the drive command queue. – SingleNegationElimination Apr 6 '12 at 17:30
  • Right, I was talking about the file system block size. – TMN Apr 8 '12 at 12:14

Many databases keep their data in one file per table. MySQL does, for example, for MyISAM tables. Whether the data is all kept in one file or not isnt very relevant. It just depends on the storage system being used.

Basically, its among the last things I'd care about when selecting a database server. For desktop databases, single file data might be more convenient.

| improve this answer | |
  • For the record this is not in anyway influencing the selection of a system. It's just so I can know what I'm talking about a little more and so that the quantity of file a RDBMS generates is not a focal point on the quality of the system. – Brad Apr 5 '12 at 19:04
  • Is there ever the concern that single tables can be accidentally deleted thus ruining the integrity of the system? – Brad Apr 5 '12 at 19:07
  • @Brad: Of course deleting files out of the databases on disk storage is going to break the database. The disk structures should be protected against accidential or malicious access and from catastrophic failures (disk failure/asteroid strike) – SingleNegationElimination Apr 6 '12 at 17:28

MySql does store data in a separate file per table AFAIK. I don't think there is a good reason about doing it one way or the other, alot of it has to depend on the implementation details of your storage I'd think. But I don't write database servers so I can't say for sure.

Also, sql server doesn't have a lock file. It has a transaction log file. Locks are for desktop dbs.

| improve this answer | |

In Oracle, you could define "External Tables". These tables are just separate text files but can be accessed via SQL. In xBase family of databases, each database has its own file (.dbf) and each index has its own file (.ndx) (extensions vary among products).

The drawbacks of having several files representing your database are security and consistency of the database. The major bad thing about MS-Access specifically is that the database file stores not only data but also some code. If the database file is corrupted you get a problem not only accessing the data but also in accessing the coed.

| improve this answer | |

I can see no reason why you would want one file per table. In SQL server you can partition the files if you want for performance but typically you don't need to do that until you have many millions of records.

| improve this answer | |
  • If the table files were usable on their own (as DBF files are in many cases), then it might be useful to be able to copy individual tables. For example, we have a table which tracks user actions on the system, which we analyse offline for bug-hunting, client support, usability stuff, etc. Being able to get a copy of it with a simple filesystem copy could be useful. – Tom Anderson Apr 9 '12 at 21:47
  • YOu can't do a file system copy of a SQL Server database without detaching it. YOu can always export a table to a file if you want to see it in file format. – HLGEM Apr 9 '12 at 21:53

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.