Partager via

Using SQL Server 2005 Compact Edition

  • Article

4/19/2010

Microsoft SQL Server 2005 Compact Edition is a powerful database engine and query system, included in the ROM on all Windows Mobile 6.5 devices. Devices using Windows Mobile Version 5.0, may install SQL Server 2005 Compact Edition from this MSDN location.

SQL Server 2005 Compact Edition n provides relational database functionality in a small footprint: a robust data store, and an optimizing query processor. In addition to being able to manage local data, SQL Server 2005 Compact Edition supports synchronization with other Microsoft SQL Server editions.

Windows Mobile applications can use SQL Server 2005 Compact Edition to store data in a database on the device itself, or to query or sync with databases stored on remote servers or desktop PCs. It is primarily designed to be used with managed code (Visual C#, or Visual Basic .NET), although Visual C++ is supported through an OLE DB provider. For information using Visual C++ with SQL Server 2005 Compact Edition, see SQL Server Compact Edition Native Programming.

Introduction to SQL Server Compact Edition

A SQL Server 2005 Compact Edition database is stored in a file with the .sdf extension. The file contains the complete contents of the database and can be up to 4GB.

Some of the functions you can perform in a SQL Server 2005 Compact Edition database include the following:

  • Creating, deleting, and editing tables and the associated data.
  • Creating, maintaining, and deleting indexes.
  • Examining information schema views and data types.
  • Initiating replication and remote data access (RDA) synchronization.

You can use Transact-SQL commands, including data manipulation language (DML), data definition language (DDL), and functions, with the SQL Server 2005 Compact Edition database.

You can also use replication or RDA with a database that is exposed through native and managed APIs.

Architecture of SQL Server Compact Edition

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

Storage Engine

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

The SQL grammar that is used with SQL Server 2005 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 2005 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 2005 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.

Developing with SQL Server Compact Edition

There are two approaches to consider when initialization the database required by your application.

  1. Create the database programmatically,
  2. Create the database using the Visual Studio Data Connection tools.

The programmatic approach offers more flexibility, for example, when the exact specifications of the database may not be decided until runtime. The Data Connection tools are quicker and easier to use.

Creating the Database Programmatically

Creating a database requires only a few lines of C++ code:

// Create the ‘connection string’ which defines the database name, ID
// and access password.
string connString = "Data Source='Test.sdf'; LCID=1033; Password=\"s$;2'!dS64\"; Encrypt = TRUE;";
// Instantiate an instance of the SQL Database engine
SqlCeEngine engine = new SqlCeEngine(connString);
// Create the database
engine.CreateDatabase();
// Create a table
SqlCeConnection conn = new SqlCeConnection(“Data source='Test.sdf”);
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE table1 (fieldname fieldtype)";
cmd.ExecuteNonQuery();

For more information on creating and accessing databases from managed code, see: Building Managed Applications (SQL Server Compact Edition).

Creating the Database using the Visual Studio Data Connections

To create a database using the Visual Studio Data Connection tools, use Add New Item from the Project menu within the Visual Studio IDE, and select Database File from the dialog.

For more information on creating databases using the Data Connection tools, see:

References

See Also

Concepts

Using the Embedded Database Engine