Share via

What's New in SQL Server Compact 4.0

  • Article

This topic describes the new features provided in SQL Server Compact 4.0 and previous releases including, SQL Server Compact 3.5, SQL Server Compact 3.5 Service Pack 1 (SP1) and SQL Server Compact 3.5 Service Pack 2 (SP2).

What's New in SQL Server Compact 4.0

Microsoft SQL Server Compact 4.0 has a group of new features, and enables a new scenario where SQL Server Compact 4.0 can be used as a database for ASP.NET Web applications and Web sites. The main features of SQL Server Compact 4.0 include the following:

  1. Development of SQL Server Compact 4.0 applications with WebMatrix and Visual Studio 2010 Service Pack 1

    • The Premium, Ultimate and Professional editions of Visual Studio 2010 SP1 can be used to develop ASP.NET Web applications and Windows desktop applications for SQL Server Compact 4.0. The designers, such as Server Explorer, Query Designer, and Transact-SQL Editor; can be used to browse the database. The designers in the ASP.NET Web applications project system can be used to bind controls on the Web pages to the data in SQL Server Compact.

      In addition, the Microsoft Visual Web Developer 2010 SP1 Express also supports SQL Server Compact 4.0 for ASP.NET Web application development. Visual Studio 2010 SP1 also supports application development for SQL Server Compact 3.5 side-by-side with SQL Server Compact 4.0.

    • WebMatrix can be used to develop ASP.NET Web applications with SQL Server Compact 4.0 and to manage the SQL Server Compact database including running Transact-SQL queries against SQL Server Compact.

    • WebMatrix can be used to script data and schema from SQL Server Compact 4.0 into the higher versions of SQL Server and a Web site can be migrated to the other versions of SQL Server with a single click.

  2. API Enhancements

    • Integration with ADO.NET Entity Framework 4.0 (.NET Framework 4)

      SQL Server Compact 4.0 supports the code-first programming model of ADO.NET Entity Framework. In addition, the columns that have server generated keys such as identity or rowguid, are also supported in SQL Server Compact 4.0 when used with ADO.NET Entity Framework 4 (this is the version of ADO.NET Entity Framework that released with .NET Framework 4).

      Note

      The SQL Server Compact 4.0 release only works with ADO.NET Entity Framework 4 that released with .NET Framework 4. Using SQL Server Compact 4.0 with ADO.NET Entity Framework 3.5 SP1 (released with .NET Framework 3.5 SP1) results in an error.

    • New API for DbConnection.GetSchema

      SQL Server Compact 4.0 has a new API -System.Data.SqlServerCe.SqlCeConnection.GetSchema() that you can use to get the schema from a SQL Server Compact database file. The API is also provides support for System.Data.Common.DbConnection.GetSchema.

    • New API for SqlCeConnectionStringBuilder

      The new API - System.Data.SqlServerCe.SqlCeConnectionStringBuilder() allows developers to programmatically create correct connection strings for SQL Server Compact 4.0, and to parse & rebuild existing connection strings. The API also provides support for System.Data.Common.DbConnectionStringBuilder.

  3. New Transact-SQL Syntax

    SQL Server Compact 4.0 supports Transact-SQL queries if further extended with new Transact-SQL syntax for OFFSET & FETCH with Order BY clause. OFFSET-FETCH enables users to write paging queries and run against the database file. For more information, see ORDER BY Clause (SQL Server Compact).

  4. Improvements for ASP.NET Web applications and Web sites

    • Support for ASP.NET

      SQL Server Compact 4.0 supports ASP.NET without the need for any specific configuration. The flag SQLServerCompactEditionUnderWebHosting is no more required in SQL Server Compact 4.0 and is removed.

    • Virtual Memory Reduction

      The usage of virtual memory for each connection of SQL Server Compact has been reduced in SQL Server Compact 4.0. The visible difference is that if an application tries to open 40 to 50 simultaneous connections in SQL Server Compact 3.5 SP2, an ‘out of virtual memory’ exception will be hit. But with SQL Server Compact 4.0, the application can easily open as many connections as needed to the database file, up to the full extent of 256 connections without running out of virtual memory.

    • Medium Trust

      The main requirement of ASP.NET applications is to run in a Medium (Partial) trust environment, which is supported in SQL Server Compact 4.0.

  5. More reliable, secure and easily deployable

    • Higher Reliability

      SQL Server Compact 4.0 has been tested and certified to work with ASP.NET Web applications and can handle the load of starter Web sites. SQL Server Compact 4.0 performs excellently, in scenarios where there are a number of concurrent connections and multiple requests are made to the database.

    • Encryption Algorithm Update

      SQL Server Compact 4.0 supports the SHA 2 encryption algorithms that provide a higher level of security for the encrypted SQL Server Compact database files.

      Tip

      The algorithms that were supported in the earlier versions of SQL Server Compact are supported in only for opening the database file to upgrade them to SQL Server Compact 4.0. Therefore, when you open a SQL Server Compact 3.5 database file with SQL Server Compact 4.0, the SQL Server Compact 3.5 database file is required to be upgraded to SQL Server Compact 4.0 using the System.Data.SqlServerCe.SqlCeEngine.Upgrade() API.

    • Setup Enhancements

      SQL Server Compact 4.0 setup and installation is enhanced to ensure that the x86 and x64 components of SQL Server Compact install properly on a 64-bit machine. This ensures that SQL Server Compact 4.0 supports both the WOW64 mode and the 64-bit native applications on the 64-bit machine without any problems.

      • In SQL Server Compact 4.0, the x86 EXE will install on a 32-bit machine and the x64 MSI will install on a 64-bit machine only. The x64 MSI will install both the 32-bit and 64-bit SQL Server Compact components on the 64-bit machine. This change in installation behavior removes the requirement of installing the x86 MSI on the 64-bit machine.

      • For private deployment of SQL Server Compact 4.0, all the binaries (both 32-bit and 64-bit) for the private deployment are available in the %Program Files%\Microsoft SQL Server Compact Edition\v4.0\Private and the %Program Files (x86)%\Microsoft SQL Server Compact Edition\v4.0\Private folder.

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 database. The Transact-SQL Editor also provides the ability to view and save detailed estimated and actual query show-plans for SQL Server Compact 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.

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

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.

Other New Features

SQL Server Compact 4.0 provides the following components: SSCEVSTools-ENU.msi, SSCEWebTools-ENU.msi, SSCERuntime_x86-ENU.exe and SSCERuntime_x64-ENU.exe.

  • Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 (SSCEVSTools-ENU.msi): This file installs the SQL Server Compact design-time components that are needed for the designer support in Visual Studio 2010 SP1. 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\v4.0\Samples. These components are tied to Visual Studio and cannot be used independently. SQL Server Compact samples and SQL Server Compact header files (for native development) are also installed by this .msi file.

  • SQL Server Compact 4.0 Runtime (SSCERuntime_x86-ENU.exe & SSCERuntime_x64-ENU.exe): This file installs the SQL Server Compact runtime components for desktop computers in a central folder: %ProgramFiles%\Microsoft SQL Server Compact Edition\v4.0. 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.

  • SQL Server Compact 4.0 Web Tools (SSCEWebTools-ENU.msi): This file installs the database scripting component in the global assembly cache. This provides the database migration capability to higher SQL Server SKUs, for WebMatrix. The MSI can only be used with WebMatrix.