• Article

To provide a rich development environment, Microsoft SQL Server Compact provides new features. This topic describes the new features provided by the SQL Server Compact 3.5, SQL Server Compact 3.5 Service Pack 1 (SP1), and SQL Server Compact 3.5 Service Pack 2 (SP2) releases.

What's New in SQL Server Compact 3.5

SQL Server Compact 3.5 was released with Visual Studio 2008. Starting with the SQL Server Compact 3.5 release, SQL Server Compact provides the following new features:

  • SQL Server Compact implements the timestamp (rowversion) data type. The rowversion is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique in a database. It is used typically as a mechanism for version-stamping table rows.

  • SQL Server Compact supports local transaction scope on desktop computers.

  • SQL Server Compact Table Designer in has been enhanced to provide a user interface for creating primary key and foreign key relationships between tables.

  • SQL Server Compact support for transact-SQL statements has been extended as follows:

    • Nested query in FROM clause

    • CROSS APPLY and OUTER APPLY

    • CAST

    • TOP

    • SET IDENTITY INSERT

  • SQL Server Compact supports Unicode characters of any locale, including Chinese GB 18030 characters.

  • SQL Server Compact supports the development of desktop applications using Visual C# 2008 Express Edition and Visual Basic 2008 Express Edition.

  • SQL Server Compact version 3.5 can be supported side-by-side with the previous version (3.1) on desktop computers. For more information about interacting with earlier versions, see Upgrading from Earlier Versions (SQL Server Compact).

  • To support older operating systems with older encryption algorithms and newer, more secure encryption, SQL Server Compact supports backward-compatible encryption modes. For a complete list of backward-compatible encryption modes, see Upgrading from Earlier Versions (SQL Server Compact).

  • SQL Server Compact supports LINQ to SQL. LINQ to SQL is a component of the LINQ project. It provides a run-time infrastructure for managing relational data as objects without giving up the ability to query. It translates language-integrated queries into Transact-SQL for execution by SQL Server Compact and then translates the tabular results back into the objects as defined by the application developer. There is no designer support for SQL Server Compact in LINQ to SQL. The SqlMetal.exe utility must be used for SQL Server Compact. The SqlMetal command-line tool generates code and mapping for the LINQ to SQL component of the .NET Framework. By default, the SQLMetal file is located at %ProgramFiles%\Microsoft SDKs\Windows\vn.nn\bin. For more information, see Code Generation in LINQ to SQL. SqlMetal can be used for the following:

    • From a database, generate source code and mapping attributes or a mapping file.

    • From a database, generate an intermediate database markup language (.dbml) file for customization.

    • From a .dbml file, generate code and mapping attributes or a mapping file.

What's New in SQL Server Compact 3.5 Service Pack 1

SQL Server Compact 3.5 Service Pack 1 (SP1) is included with SQL Server 2008 and Visual Studio 2008 SP1. Starting with the SQL Server Compact 3.5 SP1 release, SQL Server Compact provides the following new features:

  • SQL Server Compact supports the ADO.NET Entity Framework. The Entity Framework enables you to work with data in the form of domain-specific objects and properties, such as customers and customer addresses, without having to concern yourself with the underlying database tables and columns where this data is stored.

  • Support for the ADO.NET Entity Framework allows you to create flexible, strongly typed queries against the Entity Framework object context by using LINQ expressions and the LINQ standard query operators directly from the development environment.

  • SQL Server Compact supports case-sensitive collations at the database level. For more information, see Working with Collations (SQL Server Compact).

  • You can administer a SQL Server Compact database stored on a smart device or on the desktop computer by using SQL Server Management Studio (SSMS) in SQL Server 2008.

  • SQL Server Compact provides support for replicating the new data types in SQL Server 2008 such as date, time, datetime2, datetimeoffset, geography, and geometry. The new data types in SQL Server 2008 are mapped to nchar, nvarchar, image, etc. For more information about data types in SQL Server 2008, see Data Types in SQL Server 2008 Books Online Documentation.

  • SQL Server Compact can run natively in a 64-bit environment. The affected MSI files are SQL Server Compact 64-bit Runtime (SSCERuntime-ENU.msi) and SQL Server Compact 64-bit Server Tools (SSCEServerTools-ENU.msi). Support for 32-bit MSIs has not changed. Developers using ClickOnce deployment for their applications must specify both 32-bit and 64-bit download URLs. For more information, see Managing 64-bit Database Applications.

  • SQL Server Compact supports data replication with SQL Server 2000, SQL Server 2005 and SQL Server 2008 by using Sync Services for ADO.NET. Sync Services for ADO.NET is available for both desktop and mobile devices.

  • SQL Server Compact supports data replication with SQL Server 2005 and SQL Server 2008 using merge replication and Remote Data Access (RDA).

  • SQL Server Compact includes enhanced version compatibility between SQL Server Compact and SQL Server for Merge Replication.

  • Server Tools for configuring Merge Replication and RDA by using SQL Server Compact 3.5 SP1 can be downloaded from the Microsoft Download Center. SQL Server Compact 3.5 SP1 Server Tools cannot be installed side-by-side with earlier versions of Server Tools on the computer that is acting as the IIS server. SQL Server Compact 3.5 SP1 Server Tools can replicate data between SQL Server Compact 3.5 and SQL Server 2005 or SQL Server 2008. SQL Server Compact 3.5 SP1 Server Tools also support data replication between SQL Server 2005 Compact Edition or SQL Server 2005 Mobile Edition and SQL Server 2005 or SQL Server 2008.

  • SQL Server Compact supports Windows Server 2008. For a list of all supported Windows versions, see Hardware and Software Requirements.

What's New in SQL Server Compact 3.5 Service Pack 2

SQL Server Compact 3.5 Service Pack 2 (SP2) is included with SQL Server 2008 R2 and Visual Studio 2010. Starting with the SQL Server Compact 3.5 SP2 release, SQL Server Compact provides the following new features:

  • Support for the Transact-SQL Editor in Visual Studio 2010. You can now use the Transact-SQL Editor to run free-text Transact-SQL queries against a SQL Server Compact 3.5 database. The Transact-SQL Editor also provides the ability to view and save detailed estimated and actual query show-plans for SQL Server Compact 3.5 databases. Previously, the functionality provided by the Transact-SQL Editor was only available through SQL Server Management Studio. For more information, see Visual Studio Transact-SQL Editor.

  • Support for change tracking on SQL Server Compact databases. New classes and members have been added to the System.Data.SqlServerCe namespace to support row-level change tracking for database tables. When tracking is enabled on a table, the tracking infrastructure maintains information about inserts, deletes, and updates performed on the table. This information is stored both in columns added to the tracked table and in system tables maintained by the tracking infrastructure. By using these new classes, you can configure, enable, and disable change tracking on a table and you can also access the tracking data maintained for a table. You can use change tracking to provide functionality in a number of scenarios. For example you can provide custom implementations of client-to-server or client-to-client sync for occasionally connected systems (OCS); or, you can use change tracking to implement a custom listener application. For more information, see Building Applications that Use Change Tracking (SQL Server Compact).

  • New assembly versioning support and directory structure that enable SQL Server Compact applications that use private deployment to receive hotfixes, security patches, and critical updates. For more information, see Private Deployment vs. Central Deployment (SQL Server Compact).

  • Support added for 64-bit ClickOnce deployment. For more information, see Deploying Desktop Applications.

  • SQL Server Compact supports Windows 7 and Windows Server 2008 R2. For a list of all supported Windows versions, see Hardware and Software Requirements.

Other New Features

SQL Server Compact 3.5 provides the following components: SSCEVSTools-ENU.msi, SSCERuntime-ENU.msi, SSCEDeviceRuntime-ENU.msi, and SSCESqlWbTools-ENU.msi.

  • SQL Server Compact 3.5 SP2 Design Tools (SSCEVSTools-ENU.msi): This file installs the SQL Server Compact design-time components with Visual Studio 2010. The design-time components are the user interface, dialog boxes, and design-time environment, which are used to write applications for SQL Server Compact. The SQL Server Compact design-time components will be installed under the directory: %ProgramFiles%\Microsoft Visual Studio 10\Common7\IDE\. Samples are installed under: %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\Samples. These components are tied to Visual Studio and cannot be shipped independently of Visual Studio. SQL Server Compact samples and SQL Server Compact header files (for native development) are also installed by this .msi file.

  • SQL Server Compact 3.5 SP2 (SSCERuntime-ENU.msi): This file installs the SQL Server Compact runtime components for desktop computers in a central folder: %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5. The SQL Server Compact desktop computer runtime components are required for developing applications in Visual Studio and for running SQL Server Compact-based applications on the desktop. SSCERuntime-ENU.msi is also available for download from the Web. This .msi file also installs: Sync Services for ADO.NET components. Sync Services components are installed at %ProgramFiles%\Microsoft Synchronization Services\ADO.NET\v1.0. For more information, see Microsoft Synchronization Services for ADO.NET.

  • SQL Server Compact 3.5 SP2 for Devices (SSCEDeviceRuntime-ENU.msi): This file installs the SQL Server Compact devices' runtime components in %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\Devices. The runtime components are required for developing Windows Mobile and Windows CE device-based applications in Visual Studio and for deploying the applications on mobile devices. SSCEDeviceRuntime-ENU.msi is also available for download from the Web.

  • SQL Server Compact 3.5 SP2 Server Tools (SSCEServerTools-ENU.msi): This file installs the SQL Server Compact Server Tools in %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\sync\SQL. The components are required for connecting the SQL Server Compact database on a mobile device to a SQL Server 2008 R2, SQL Server 2008 or SQL Server 2005 database. SSCEServerTools-ENU.msi is also available for download from the Web.

With SQL Server Compact 3.5 SP2, the Query Tools component is also available when SQL Server 2008 R2 is installed.

  • SQL Server Compact 3.5 SP2 Query Tools (SSCESqlWbTools-ENU.msi): This file installs the SQL Server Compact query tool files in %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\Tools, and samples in %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\Samples. SSCESqlWbTools-ENU.msi is part of the SQL Server Compact 3.5 SP2 release. The file is installed as part of SQL Server 2008 R2.