“What’s in a name? That which we call a mdf file
By any other name would work as well.”
At a training recently I was asked this question, “How many additional mdf files can a database have?”. We all know that it can have additional (ndf) secondary files and log files but what about the mdf files? Well the answer is 1, the participant luckily tried creating additional files using the .mdf extension and lo and behold it went thru!! He later asked me to come over to his desk and showed me the GUI where the database had 2 entries with mdf as the file extension.
As I always say during my trainings do not take my word for it, try it out, he followed this and was able to create the file. So the question was does it really create an additional mdf file? Well the answer is No. To demonstrate this I asked him to add another file with extension .pdf, he did and it still went thru. The point being that SQL doesn’t really care what the extension of the file is, it looks at the file header to determine if the file is a database file. E.g. in the below screenshot I have created a database with .pdf for the mdf file and .mdf for the log file, the database was still created and so was table etc. So why does SQL allow this behavior, it’s one of those things that got carried over from the Sybase code base used for earlier versions for SQL but I guess a lot of people are not aware of it and it could cause the kind of confusion seen here.