SQL Server Compact

Overview

SQL Server Compact shares a common API with other Microsoft SQL Server editions and includes ADO.NET providers for data access, built-in synchronization capabilities, and support for LINQ and Entity Framework. It runs in-process with the hosting application, with a disk footprint under 2 MB and memory footprint around 5 MB. Windows Store apps for Windows 8 cannot use SQL Server Compact or any other SQL Server edition.

Support

SQL CE databases support ACID-compliance but do not meet the durability requirement by default unless the commit call specifies the immediate flag. It supports transactions, referential integrity constraints, locking, and multiple connections, but not nested transactions.

Stored procedures and native XML data type are not supported, and it uses a subset of T-SQL without XQuery. SQL CE supports indexing, remote data replication, and merge replication. Databases can be created and managed from Microsoft Visual Studio and some older versions of SQL Server Management Studio.

File format

SQL CE databases reside in a single .sdf file, which can be up to 4 GB in size and optionally encrypted with 128-bit encryption. The .sdf file can be deployed by copying or through ClickOnce, and the runtime supports DataDirectories for simplified connection strings. SQL Server Management Studio 2005 can read CE 3.0 and 3.1 files, while version 3.5 requires Management Studio 2008 or later; CE 4.0 files cannot be read by Management Studio.

Visual Studio 2010 SP1 can handle CE 4.0 files. The .sdf extension is optional, and a password for the database file is optional. The database can be compressed and repaired into a new file.