When a project is created in Asp.net MVC you have the possibility to choose between different types of database, which is the recommended and what are their differences and uses.
SQL Server runs as a service, whereas SQL Server Compact is an embedded database in the same physical location as the application. SQL Server Compact is meant for a standalone application and is not intended to be shared among many users or in situations where there is a lot of concurrent access, such as with a web site.
SQL Server runs as a service and listens for requests on a port. You can have multiple concurrent connections with its use and it acts as a gatekeeper checking for permissions from incoming connections. It is meant for work groups and enterprises, and is not intended (overkill) for single-user/single-computer applications.
SQL Server Express is the free edition of the full SQL Server, and has certain limitations such as a maximum database size of 10GB, among others. SQL Server Standard is like the full edition. There are also Enterprise, Datacenter, and Developer editions.