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.
But recently I came across some database on a server where every database had multiple mdf files.
That is because of incorrect naming convention. Microsoft says that every database has one primary data file but it does not means it can have only one "mdf data file", database can have many data file with .mdf extension but only one will be primary data file. It is better to give mdf extension to primary data file and ndf to secondary data file to have proper demarcation but this is no hard and fast rule you can also give .abc extension to primary data file so what you witnessed is normal. As a fact you can give any extension you like.
Is there any wisdom in having multiple .mdf files for a database?
If you mean:
Is there any wisdom in having multiple primary files for a database?
The answer is no, a database can have only one primary data file.
But if you mean:
Is there any wisdom in having multiple data files (.mdf, .ndf, or differently named) for a database?
It depends, you can and cannot have advantage with multiple data files. If they are spread across different physical drives (I am talking about spindles) you would see some advantage with write intensive application. If they are all on same logical partitions it won't have any advantage because underlying they would be using common resource. Using files and filegroups improves database performance, because it lets a database be created across multiple disks, multiple disk controllers, or RAID (redundant array of independent disks) systems. For example, if your computer has four disks, you can create a database that is made up of three data files and one log file, with one file on each disk. As data is accessed, four read/write heads can access the data in parallel at the same time.
As per this MSDN BOL article
Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.
Other advantage which I know is consider a 1 TB database if you would have single data file for it and you want to restore this database on some other server its highly unlikely you would have 1 TB of free space. Now if the same database is spread on different files each having size 250 G it becomes easier in restoration. This might not be actually your scenario but it does helps a lot its little easy to find a server with four 250 G drives than one 1 TB drive
I would say instead of many data files its better to have different file groups but again there are not many environments. Databases made up of multiple filegroups can be restored in stages by a process known as piecemeal restore. Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple mode. Files or filegroups in a database can be backed up and restored individually. This allows you to restore only the damaged files without having to restore the rest of the database. Files in a filegroup backup can be restored individually or as a group
Best Answer
Every table or index has at least one partition. Each partition is stored on a single filegroup*. So when that filegroup is full and none of its files can be expanded, any operation that needs to allocate additional space for a partition that is stored on that filegroup will fail.
*Except that a table can be configured to store its blob data on a separate database filegroup, or filesream filegroup.