Temporary Databases

Microsoft SQL Server Compact creates a temporary database for storing temporary data such as:

• Interim result sets that are created during a query.

• Interim sort tables that are created when executing an ORDER BY, GROUP BY, or DISTINCT clause.

The temporary database is created only if it is explicitly specified to be created. The name must be specified when the engine starts. The temporary database is removed when the database engine is shut down. Abnormal termination of a SQL Server Compact application leaves temporary database files in the system. Those files must be manually removed.

Location of the Temporary Database

For operations requiring a large temporary database, you should explicitly specify a location for creating a temporary database. If a temporary database location is not specified, then the current database location is used as the location of the temporary database. You do not specify a name for the temporary database; the filename begins with "SQLCE", for example: SQLCE334241234.tmp.

The location of the temporary database must be specified before the engine is started and cannot be changed while the engine is running. You can change the location of the temporary database when the database is compacted. For more information, see Maintaining Databases and How to: Compact a Database (Programmatically).

Specifying the Location

You specify the location of the temporary database in the connection string to the database by using the temp path, temp file directory, or ssce:temp file directory parameter.

For more information, see How to: Specify the Location of the Temporary Database by Using ADO.NET (Programmatically) and How to: Specify the Location of the Temporary Database by Using OLE DB (Programmatically).

You use the connection string with the SqlCeConnection objects.

Growth of the Temporary Database

Large databases can generate large amounts of temporary data during normal execution. When the temporary database grows to the point that there is insufficient storage space on the default storage device or the maximum temporary database limit is reached, the application fails.

The following operations cause growth in the temporary database, particularly when a group of the operations are wrapped in a single explicit transaction:

• UPDATE and DELETE statements that affect a lot of data.

• Sort operations. Sorting might not require using the temporary database if there is an index that satisfies the sort. Some sort operations might create some additional temporary files for the temporary sort buffers. These files are different from the temporary database. There is one temporary database for every database, but there can be any number of temporary sort buffer files.

For more information about explicit transactions, see Types of Transactions.

To prevent large database files from growing beyond the storage limits of the device, store databases on a storage card instead of in internal RAM.