What's New in SQL Server Compact Edition
Microsoft SQL Server 2005 Compact Edition (formerly Microsoft SQL Server 2005 Mobile Edition and Microsoft SQL Server 2000 Windows CE 2.0), 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 product details, see the readme file.
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 2005SP2 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 2005 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.
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 amount of programming required. For more information about multiple subscriptions, see "Supporting Multiple Subscriptions" in the SQL Server Compact Edition Books Online.
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 Remote Data Access (RDA)" and "Multiuser Access and Synchronization" in the SQL Server Compact Edition Books Online.
Synchronization progress status
Developers can use the managed APIs to provide synchronization status feedback, which can be used to inform users of synchronization progress. For more information about the status bar, see "Asynchronous Data Synchronization" in the SQL Server Compact Edition Books Online.
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 Compact Edition, only the modified columns are synchronized. For more information about column-level tracking, see "Using Row-Level and Column-Level Tracking" in the SQL Server Compact Edition Books Online.
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)" in the SQL Server Compact Edition Books Online.
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 ensure data integrity during concurrency. For more information about locking, see "Locking (SQL Server Compact Edition)" and "Lock Escalation" in the SQL Server Compact Edition Books Online.
Auto re-use of empty pages
SQL Server Compact Edition supports the autoshrink feature, which automatically reclaims unused data pages and saves device storage space. For more information about autoshrink, see "Maintaining Databases" in the SQL Server Compact Edition Books Online.
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)" in the SQL Server Compact Edition Books Online.
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 your 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 amount of code 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)" in the SQL Server Compact Edition Books Online, and System.Data.SqlServerCe.
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" in the SQL Server Compact Edition Books Online.
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" in the SQL Server Compact Edition Books Online.
Integration with SQL Server Integration Services (SSIS)
SSIS lets developers transfer data between a SQL Server Compact Edition database and a variety of data sources, including 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 the process of writing and editing queries and code. You can also edit scripts that are created from files or from Object Explorer.
Database Deployment
From within SQL Server Management Studio and SQL Server 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" in the SQL Server Compact Edition Books Online
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" in the SQL Server Compact Edition Books Online.
Integration with Microsoft Visual Studio 2005
There are two major enhancements to the integration of SQL Server Compact Edition with Microsoft 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 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 with Visual Studio 2005, you will have to manually install SQL Server Compact Edition. For more information, see "Deploying Smart Device Applications" in the SQL Server Compact Edition Books Online.
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++.