Actually you have three options. The option you didn't describe is the one where you create a database with SSMS and then set up a connection to a file and select the MDB file that was created by SSMS (you will probably need to first dismount the database using SSMS to get SQL Express to release its file locks). When you created this connection to a file, you will be prompted as to whether you want to connect to it where it is, or add it to your project.
A local database can take two forms, depending on how you create it. For detailed information, consult How to: Manage Local Data Files in Your Project.
Client-server, SQL Express
If you set up a database with SSMS and connect to it via SQL Express then you don't have a local database that's part of your project, you have a database for which the server happens to be local to your workstation.
Local database, SQL Express
If you set up a database with SSMS, dismount the database and add the file to your project, then you have a local database that uses a private instance of SQL Express.
Local database, Compact Edition
If you create a new database with the Visual Studio menus, you have a local Compact Edition database .
SQL Express
When Visual Studio launches debugging, a private named instance of SQL Server Express is started, and the application communicates with this using shared memory rather than a network protocol.
However, there is absolutely nothing preventing you from installing an instance of SQL Express that runs as a service. You can mount the same database file (or a copy of it) and make it available to the network. You can even mount it on an instance of SQL Standard, or even SQL Enterprise.
Why then would you muck about with a local instance? It has advantages for multi-developer teams because developers can alter their schema without disrupting others. It allows development of desktop (as opposed to network) software, although in this day and age demand for that capability is diminishing.
Depending on how much hardware you have in your development environment, personally I wouldn't use a local database. SQL Server is a memory pig, and I'd much rather it ran on a completely separate box.
Some things to note
- TSQL is absolutely identical for all editions of MSSQL except for the Compact and Micro editions.
- Environmentally, SQL Express limits database size to 4G although I believe this went up to 8G for R2. This is unlikely to be significant for development but may impact testers.
- Some SQL Server Reporting Services features are not available in cheaper editions.
SQL Server Compact Edition
Information on this is pretty thin. Microsoft's version comparison doesn't consider Compact or Micro editions. Some of the blurb on the Compact edition web page claims full TSQL compatibility. SDF is an all-in-one file; there is no separate log file. The path from SDF to client-server is certainly less direct than for SQL Express, but it does appear to be a supported option since there are articles in msdn on this topic.
Replication tools are available for Compact edition so that it can be used as a local database cache in an occasionally connected system (aka briefcase model). Briefcase model requires more careful total system design, but it has a lot going for it: all the performance and simplicity of a single user standalone system with most of the advantages of a client-server system.
Conclusion
For your purposes I'd go with the Compact Edition option. The overheads and complexity of the other solutions are pitched at solving problems you don't and won't have. They are intended to solve the problems of team development in a networked, large scale environment with a formal release cycle.
You are in the fortunate position that you can keep it simple. The tools in Visual Studio are nicer anyway.
Add New Item -> Local Databasewill add an instance of a SQL Server Compact Edition (CE) database (a single .sdf file) to your project. This is an "embedded", single-user local database - it's NOT SQL Server Express (and not a server product)! – Gwendagwendolen