Overview of Database Engine (SQL Server Compact Edition)

  • Article

The Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) Database Engine is the core technology for storing, processing and securing data. By using the SQL Server Compact Edition Database Engine, you can create, access, and modify SQL Server Compact Edition databases in supported devices. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the data consuming applications within your enterprise. The Database Engine is installed and runs on the supported devices.

Components of the Database Engine

The components of the SQL Server Compact Edition Database Engine are the storage engine and the query processor.

Storage Engine

The SQL Server Compact Edition storage engine:

  • Manages the file that the database is stored on and using space in the file.
  • Builds and reads the physical pages that are used to store data.
  • Manages the data buffers and all I/O to the physical files.
  • Manages transactions and uses locking to control concurrent user access to rows and schemas in the database.
  • Ensures the atomicity, consistency, isolation, and durability (ACID) of transactions.
  • Creates and maintains the index structure.
  • Supports referential integrity.
  • Supports encryption and password-protected databases.

Query Processor

The SQL Server Compact Edition query processor parses, compiles, optimizes and executes SQL expressions, queries, and commands.

The SQL grammar that is used with SQL Server Compact Edition is a subset of the Transact-SQL grammar supported by Microsoft SQL Server 2005. During parsing, the query syntax is validated and data structures representing the parsed query are built. The query processor then compiles and optimizes the query. During query optimization, the query processor produces an execution plan for the query.

The SQL Server Compact Edition query optimizer considers all available indexes when processing a query, including:

  • Data manipulation language (DML) statements.
  • Joins and predicates with ORDER BY.
  • GROUP BY and DISTINCT clauses.

The query processor then executes the SQL commands and returns the results of the query.

The SQL Server Compact Edition query processor automatically creates statistical information about the distribution of values in an index. The query processor uses this information to help determine the optimal strategy for evaluating a query. The query processor uses statistics to help select the most appropriate index for efficient processing.

Note

Any choice of index can be overridden by using index hints. There is also support for force join order.

See Also

Concepts

Transactions (SQL Server Compact Edition)
Locking (SQL Server Compact Edition)

Other Resources

Querying Databases (SQL Server Compact Edition)

Help and Information

Getting SQL Server Compact Edition Assistance