We all know that SQL Server 2005 Express Edition is a relational database that stores and retrieves data for various database applications. These applications typically connect to the SQL Server Express database by using a variety of middleware technologies including ODBC, OLE DB, and ADO.NET. But when you're working with an application that’s using the database, you don't always see the database’s architectural underpinnings that make all this work. Applications typically see the data one row at a time because they issue T-SQL queries to the database to retrieve data. T-SQL's row-oriented nature lets it return collections of rows to the application. These collections of rows are called result sets.
If you’re new to SQL Server Express, you might wonder where the data in these databases comes from. You might be surprised to learn that SQL Server Express stores its database data in two primary files: the database data file that ends in the extension .mdf and the transaction log file that ends in the extension .ldf. Both the OS and a standard file can see these files. For example, if you follow the SQL Server Express installation defaults, you should wind up with the following directory in the file system:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
This directory contains the SQL Server Express database and log files. In its default state, this directory will contain data and log files for the various SQL Server Express system databases including: master, model, msdb, mssqlsystemresource, and tempdb. If you create user databases and accept the default storage locations, those database data and log files will also be created in the Data directory. You define the SQL Server Express data and log files when you create a database. For example, you use SQL Server Management Studio Express (SSMSE) to create a database by right-clicking the Database node and selecting the New Database option to display the New Database dialog box. Entering MyDB into the database name field will create a database data file named MyDB.mdf and a log file named MyDB_log.ldf, both in the Data directory. If you want to distribute a database with your application, you need to include the database's .mdf and .ldf files with your application's installation program.
Although SQL Server Express databases are usually composed of two database files, they aren’t limited to just two. When you create a database, you can specify multiple data files by clicking the Add button on the New Database dialog box. Most databases won’t need this addition, but sometimes backup-and-restore or availability considerations might prompt you to create multiple data files.
