SDF: It is the database format of SQL Server Compact edition. This is the embedded edition of sql server. Think about it as a “single-dll” sql server. Thus it will run together with the application that is using it. It is not a service, it is not a real server. But if your database is accessed from a single application (even a web application that is multi-user), this is a good approach, although it has some limitations.
An SDF file is a Sql Server Mobile file. It is also referred to as Sql Server compact. It is predominantly used in mobile development but recently more and more common in single user desktop developments as well.
Visual Studio 2008 does not support Linq to Sql for Sql Server Mobile but the Framework does.
MDF: It is the database format of the “big”, real server editions. Indifferent which one you use (from express to datacenter), this is the format. But it depend on many things, which edition you need/can use – one of this things is the amount of money you are willing to pay .
One of the main differences between a Sdf and Mdf is that until recently an Mdf could not be opened and treated like a normal file, whereas an Sdf is accessed as a file.
However in Visual Studio this concept was reproduced by allowing Sql Server Data files which dynamically attaches the Mdf to a Sql Server Express session.
The difference is that SQLServer is a database server intended to serve
1 to many users (client apps, web requests or whatever) while SQLServerCE
is an embedded database that is superb for serving 1 users