When to use .mdf and when .sdf?

Question

After seeing a lot of things in the internet I haven't found any explanation between the two file types: .mdf and .sdf.

.sdf would be a compact version of SQL Server (.mdf) I believe. Still they appear utterly different.

Have followed the two tutorials on msdn, i.e. MVC Movie App and the Musicstore. Both use .sdf.

Partial quote (full): 'First of all it should be "|DataDirectory|ASPNETDBContext.mdf" (notice the extension difference). Your applic...'

Both tutorials have the connection string to .sdf, i.e.:

<add name             ="MovieDBContext"
     connectionString ="Data Source=|DataDirectory|Movies.sdf" 
     providerName     ="System.Data.SqlServerCe.4.0"
/>

<add name             ="MusicStoreEntities" 
     connectionString ="Data Source=|DataDirectory|MvcMusicStore.sdf" 
     providerName     ="System.Data.SqlServerCe.4.0"
/> 

Question:

My question is what to use where and when? More over why to use which where.

Apples

You can find the answer within StackOverflow itself. MDF - Medial Disk image File & SDF - Standard Data File – Erelia

And the difference is? And which to use when? Besides that question you posted talk about .mdf only. Can only conclude (guessing) the only difference with sdf is the file extention. – Apples

Answer 1

SQL Server Compact (sdf files) popular in Windows phone for storing some data (small count of data), also it could be used in web sites, but it has poor performance, even SQLite is better.

SQL Server (mdf files) used in big sites with a lot of data. Its more powerful, but cant be used in phones and must be installed on servers.

Look this: Comparison of SQL Server Compact, SQL Server Express 2012 and LocalDB

Ophiuchus

Answer 2

SQL Server Compact Edition is indeed quite different from "regular" SQL Server.

SQL Server CE is a stand-alone, one-user database that gets loaded by your application on the machine where your code runs; it has all data in the one single .sdf file and support only a small subset of the "regular" SQL Server (and its features). SQL Server CE is great for simple, stand-alone, one-user apps (like phone apps).

"Real" SQL Server is a server product - you install it on a server, your clients only connect to it (typically, the client machines don't have SQL Server itself installed). It's a full-blown RDBMS with everything - stored proc, triggers etc. and thus best optimized to handle structured, large-scale data - think enterprise applications, such as order handling, invoicing, accounting, large-scale databases for multiple users of any kind.

Kellerman

Answer 3

First, the sql server database file is not a full sql server database, if you're creating it in this fashion it's a sql server express. Sql Server Express databases aren't terribly fantastic to use since most hosts don't run sql server express and publishing them is tricky if you then try to get them running on a sql server database. Better to start with sql server first then express and migrate.

the local db is actually sql server compact edition. http://en.wikipedia.org/wiki/SQL_Server_Compact

For connection strings, go to http://www.connectionstrings.com/sql-server/ There is pretty much every connection string available there. To work with SQL Server Express you will need to have it installed, and to manage it you will need to download the sql server express management tools. Generally, most sql express instances are designed to be used with local accounts but if I remember correctly you can use the management tools to create a sql server login.

Spitfire