What's New (SQL Server Compact Edition)

  • Article

Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition), previously Microsoft SQL Server 2005 Mobile Edition (SQL Server Mobile), is a compact database that can be deployed on desktop computers, smart devices, and Tablet PCs. SQL Server Compact Edition can be deployed on smart devices either by manually copying and installing the .cab files or through Microsoft Visual Studio 2005 Service Pack 1 (SP1). For desktop computers and Tablet PCs, SQL Server Compact Edition has a separate installer (SQLServerEv31-EN.msi). For more information, see Deploying Desktop and Tablet PC Applications.

The development support for SQL Server Compact Edition will be provided by Visual Studio 2005 SP1 and the database administration support will be provided by the Microsoft SQL Server 2005 SP2 release. You can access SQL Server Compact Edition databases stored on a smart device or on the desktop computer by using SQL Server Management Studio in SQL Server or SQL Server Management Studio Express (SSMSE). The SSMSE user interface is a subset of SQL Server Management Studio. SSMSE is a free download from the Web.

The two distinct features of SQL Server Compact Edition are DataDirectory Support and ClickOnce deployment. For more information, see "Integration with Microsoft Visual Studio 2005" in this topic.

Note

SQL Server Compact Edition documentation uses two terms: supported devices and smart devices. The list of supported devices includes the smart devices and Tablet PCs. The term smart device does not include Tablet PCs.

Feature Improvements to Enhance Reliability and Performance

SQL Server Compact Edition has many new and updated features that enhance the database reliability and performance. These improvements can be divided into general feature categories: synchronization, the storage engine, and the query processor.

Synchronization

Several enhancements are available to improve the process of replicating and synchronizing data with SQL Server Compact Edition. The enhancements include the following:

  • Support for multiple subscriptions
    In earlier versions of SQL Server Compact Edition, only one subscription could be created for each database. In an environment where the published data derives from multiple publications, a developer had to create a separate subscription database for each publication, and then specify which database the application needed to open. With SQL Server Compact Edition, multiple subscriptions can be contained in a single database, reducing the programming required. For more information about multiple subscriptions, see Supporting Multiple Subscriptions.
  • Multiuser support and synchronization
    To support multiple applications accessing the same database at the same time, SQL Server Compact Edition provides multiuser support. Multiuser support enables multiple users of a database to synchronize data without having to disconnect the database before they use merge replication or remote data access (RDA). For more information about multiuser synchronization, see Multiuser Access and RDA and Multiuser Access and Synchronization.
  • Synchronization progress status
    Developers can use the managed APIs to provide synchronization status feedback. This can be used to inform users of synchronization progress. For more information about the status bar, see Asynchronous Data Synchronization.
  • Column-level tracking
    In earlier versions of SQL Server Compact Edition, a whole row was synchronized with Microsoft SQL Server, even if only one column in the row had been modified. This frequently resulted in longer synchronization times if the unmodified columns in the row contained lots of data. In SQL Server 2005 Compact Edition, only the modified columns are synchronized. For more information about column-level tracking, see Using Row-Level and Column-Level Tracking.

Storage Engine

The new and improved features of the storage engine in SQL Server Compact Edition enhance the reliability and performance of mobile applications. These features include the following:

  • Revised storage engine
    The storage engine architecture has been rewritten to optimize for a mobile architecture in which each application shares a common memory pool. Better data reliability is now ensured through true atomicity, consistency, isolation, and durability (ACID) support, and when devices experience battery power issues and connectivity disruptions. For more information, see Overview of Database Engine (SQL Server Compact Edition).
  • Multiuser support
    To support multiple applications accessing the same database at the same time, SQL Server Compact Edition provides row-level locking of data pages, page-level locking, and isolation levels to help guarantee data integrity during concurrency. For more information about locking, see Locking (SQL Server Compact Edition) and Lock Escalation.
  • Auto reuse of empty pages
    SQL Server Compact Edition supports the autoshrink feature. This automatically reclaims unused data pages and saves device storage space. For more information about autoshrink, see Maintaining Databases (SQL Server Compact Edition).

Query Processor

Improvements to the SQL Server Compact Edition query processor include the following:

  • Cost-based optimization
    The query processor takes advantage of statistics support in the storage engine to create significantly better cost-based query plans. For more information about statistics, see Overview of Database Engine (SQL Server Compact Edition).
  • Execution plan and query hints
    With SQL Server Compact Edition, developers can view the query plan and then refine the queries as appropriate. For example, you can refine the query plan based on the database design, access time of the media, or CPU speed for your specific supported devices.
  • A new SqlCeResultSet object
    The new SqlCeResultSet object reveals an updateable, scrollable cursor so that developers can directly access a SQL Server Compact Edition database without double-buffering data on the device with a DataSet object. This class provides significant performance improvements over using the DataSet object while reducing the code that is required to accomplish the same task. It also supports WinForms data binding interfaces and can be bound to UI controls such as DataGrid, TextBox, and ListBox. For more information about cursors, see Cursors (SQL Server Compact Edition) and System.Data.SqlServerCe Namespace Overview.

Integration with Other Microsoft Products

SQL Server Compact Edition has been integrated into two desktop-based applications: SQL Server 2005 and Microsoft Visual Studio 2005. This allows for more flexibility when you create and test SQL Server Compact Edition databases.

Integration with SQL Server 2005

You can manage a SQL Server Compact Edition database on a desktop computer or a device by using SQL Server Management Studio, a SQL Server 2005 management tool that replaces SQL Server Enterprise Manager and SQL Server Query Analyzer. This integrated tool provides the same experience for users whether they connect to SQL Server 2005 or SQL Server Compact Edition. SQL Server Compact Edition databases can be also managed by using SQL Server Management Studio Express.

Benefits of using Management Studio include the following:

  • Graphical query execution plans
    Graphical query execution plans enable developers to easily see query execution plans and so better understand potential query performance issues. A separate feature, query hints, is available to control some aspects of the execution plan.
  • New Subscription Wizard
    The New Subscription Wizard requires minimal effort by users when they create, and subsequently synchronize, a subscription database. For more information, see New Subscription Wizard.
  • Improved Configure Web Synchronization Wizard
    SQL Server Compact Edition supports improved versions of the Configure Web Synchronization Wizard. Improvements in the Configure Web Synchronization Wizard include enhanced usability and support for HTTPS virtual directories. For more information, see Configure Web Synchronization Wizard Help
  • Integration with SQL Server 2005 Integration Services (SSIS)
    SSIS lets developers transfer data between a SQL Server Compact Edition database and a variety of data sources. They include Microsoft Access, Oracle, and IBM databases. SSIS includes a destination adapter and connection manager for SQL Server Compact Edition.
  • SQL Editor
    The SQL Editor component of Management Studio is the primary tool for interactively designing and testing Transact-SQL statements, queries, and scripts. SQL Editor has several features that simplify writing and editing queries and code. You can also edit scripts that are created from files or from Object Explorer.
  • Database Deployment
    From Management Studio and Management Studio Express, you can create SQL Server Compact Edition databases on the local computer. You can configure these databases, populate them with data, and then deploy the databases to multiple devices. This saves significant development and deployment time.

Some of the enhancements made in SQL Server 2005 for merge replication provide the following benefits for SQL Server Compact Edition applications:

  • Managed API access to synchronize business logic
    SQL Server 2005 reveals a managed API that lets developers modify business logic data before that data is stored in the SQL Server database. For more information, see "Executing Business Logic During Merge Synchronization" in SQL Server 2005 Books Online.
  • Support for download-only articles (tables)
    This feature reduces the amount of metadata transferred during initial synchronization and reduces the processing time on SQL Server when developers perform subsequent synchronizations. For more information, see Introducing Merge Replication and Creating the Publication.
  • Support for partitioned (filtered) articles
    Many mobile applications filter data so that users do not access the same data across devices. With support for partitioned articles, developers can significantly reduce synchronization time and increase scalability. This feature is especially useful for deployments of many smart devices. For more information about partitioned articles, see Creating the Publication.

Integration with Microsoft Visual Studio 2005

There are major enhancements to the integration of SQL Server Compact Edition with Visual Studio 2005.

  • Database Management
    Developers can manage a SQL Server Compact Edition database on a desktop computer or supported devices by using Visual Studio 2005. With this feature, developers can drag a SQL Server Compact Edition table to a control in the application to enable that control to automatically bind to the table.
  • Automatic Installation of SQL Server Compact Edition
    When you use Visual Studio to build a Microsoft .NET-connected application that uses SQL Server Compact Edition, the SQL Server Compact Edition engine is automatically installed on the device the first time that you deploy the application. If you are building a native application by using Visual Studio 2005, you will have to manually install SQL Server Compact Edition. For more information, see Deploying Smart Device Applications.
  • Data Directory Support
    DataDirectory is a substitution string that indicates the path of the database. DataDirectory makes it easy to share a project and to deploy an application by eliminating the need to hard-code the full path. For example, instead of having the following connection string:
    "Data Source= c:\program files\MyApp\Mydb.sdf"
    By using |DataDirectory| (enclosed in pipe symbols as shown), you can have the following connection string:
    "Data Source = |DataDirectory|\Mydb.sdf"
    You set the DataDirectory property on the AppDomain by calling AppDomain.SetData.
  • ClickOnce Deployment for Managed Applications
    ClickOnce is a new software installation technology that is supported by SQL Server Compact Edition to deploy managed applications on a desktop and a Tablet PC. ClickOnce simplifies deploying a Windows-based application to a Web server or network file share. For administrators, deploying or updating an application consists of updating files on a server. You do not have to update each client individually. Microsoft Visual Studio 2005 provides full support for publishing and updating applications that are deployed with ClickOnce. ClickOnce deployment is available for projects that are created by using Visual Basic, Visual C#, and Visual J#, but not for Visual C++.

See Also

Reference

New Subscription Wizard Help
Configure Web Synchronization Wizard Help

Concepts

Multiuser Access
Transactions (SQL Server Compact Edition)
Subscribing to Publications (SQL Server Compact Edition)
Adding a Filter to an Article

Help and Information

Getting SQL Server Compact Edition Assistance