Overview of Database Engine (SQL Server Compact)

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

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

Storage Engine

The SQL Server Compact 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 query processor parses, compiles, optimizes and executes SQL expressions, queries, and commands.

The SQL grammar that is used with SQL Server Compact is a subset of the Transact-SQL grammar supported by Microsoft SQL Server. 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 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 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 Note

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