SQL Server Compact

SQL Server Compact enables you to create compact databases that can be deployed on desktop computers and smart devices. SQL Server Compact shares a common programming model with other SQL Server versions for developing both native and managed applications. SQL Server Compact provides relational database functionality: a robust data source, an optimizing query processor, and reliable, scalable connectivity.

Development support for SQL Server Compact is provided by Visual Studio. Database administration support is provided by SQL Server. You can access SQL Server Compact databases stored on a smart device or a desktop computer by using SQL Server Management Studio in SQL Server. You can create managed applications by using either Microsoft Visual Basic or Visual C#, or you can use Visual C++ to create native applications. 

Note

SQL Server Compact 3.5 Service Pack 1 (SP1) is included with SQL Server 2008 and Visual Studio 2008 SP1. It is also available as a download from the Microsoft SQL Server Compact Web site.

Installing SQL Server Compact Components

SQL Server Compact components are available in the following Microsoft Windows Installer (.msi) files:

  • SQL Server Compact Design Tools (SSCEVSTools-ENU.msi)

  • SQL Server Compact Runtime (SSCERuntime-ENU.msi)

  • SQL Server Compact for Devices (SSCEDeviceRuntime-ENU.msi)

  • SQL Server Compact Query Tools (SSCESqlWbTools-ENU.msi)

  • SQL Server Compact Server Tools (SSCEServerTools-ENU.msi)

  • SQL Server Compact Books Online (SSCEBOL-ENU.msi)

For more information about how to install SQL Server Compact components, see the Microsoft SQL Server Compact Web site.

Note

When you install the SQL Server 2008 Books Online, by default SQL Server Compact documentation is not installed locally. To download the SQL Server Compact Books Online, visit the SQL Server Compact Books Online Download Center.

Overview and SQL Server Compact Scenarios

SQL Server Compact provides the following features when you are using it as the local data source for your applications.

  • SQL Server Compact is file-based, which means that the connection string is a file path to the database (.sdf) file.

  • SQL Server Compact does not run as a service. This is one of the main differences between using SQL Server Compact 3.5 and using SQL Server or SQL Server Express.

  • SQL Server Compact supports multiple connections up to the 256 connection limit. Opening connections on different processes is also supported.

  • SQL Server Compact supports database files up to 4 GB.

SQL Server Compact is a file-based database that consists of DLLs that are approximately 1.4 MB. The following list provides some scenarios in which you might want to use SQL Server Compact in your applications:

  • In applications intended for desktop computers and mobile devices.

  • In applications that will be used in occasionally connected scenarios.

  • When you need a database that is redistributable at no cost.

  • When application size and memory requirements must be compact.

  • When you want the data access code to run in a process.

New Features in SQL Server Compact

The following sections describe new features in SQL Server Compact 3.5 and SQL Server Compact 3.5 SP1.

SQL Server Compact 3.5

SQL Server Compact version 3.5 was released with Visual Studio 2008. Starting with the SQL Server Compact 3.5 release:

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

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

  • SQL Server Compact now supports the following Transact-SQL statements:

    • Nested queries in a FROM clause

    • CROSS APPLY and OUTER APPLY

    • CAST

    • TOP

    • SET IDENTITY INSERT

  • SQL Server Compact 3.5–based applications can be developed for desktop computers by using Visual Basic 2008 Express Edition and Visual C# 2008 Express Edition.

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

  • SQL Server Compact implements the timestamp (rowversion) data type.

  • SQL Server Compact supports LINQ to SQL. LINQ to SQL is a component of the language integrated query (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.

SQL Server Compact 3.5 SP1

SQL Server Compact 3.5 Service Pack 1 (SP1) is included with SQL Server 2008 and Visual Studio 2008 SP1. SQL Server Compact 3.5 SP1 provides several enhancements and new features for software developers. The following list includes some new features among several others: 

  • SQL Server Compact supports case-sensitive collations at the database level.

  • 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 themselves with the underlying database tables and columns where this data is stored.

  • SQL Server Compact supports LINQ to Entities. LINQ to Entities allows developers 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 can run natively in a 64-bit environment.

  • SQL Server Compact provides support for replicating the new data types in SQL Server 2008, such as date, time, datetime2, datetimeoffset, geography, and geometry. For more information about data types in SQL Server 2008, see Data Types in SQL Server 2008 Books Online.

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

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

Integration with SQL Server

You can manage a SQL Server Compact database on a desktop computer or mobile device by using SQL Server Management Studio, which is a SQL Server management tool. This tool provides the same experience for users whether they connect to SQL Server or SQL Server Compact. SQL Server Compact databases can also be managed by using SQL Server Management Studio Express. You can download SQL Server Management Studio Express for free from the Download Center.

You can create SQL Server Compact databases on your local computer from Management Studio and Management Studio Express. You can configure these databases, populate them with data, and then deploy the databases to multiple devices. This saves significant development and deployment time.

See Also

Concepts