SQL Server Compact

SQL Server Compact shares a common API with the other Microsoft SQL Server editions.{{cite web |url=http://www.devx.com/dotnet/Article/33049/0 |title=A Lap Around SQL Server 2005 Compact Edition |access-date=2005-07-03}} It also includes ADO.NET providers for data access using ADO.NET APIs, and built-in synchronization capabilities, as well as support for LINQ and Entity Framework. Future releases will unify the synchronization capabilities with Microsoft Synchronization Services.{{needs update|date=December 2023|reason=Did this happen? If not, it doesn't look like it ever will}}{{cite web |url=http://www.ddj.com/dept/database/193402926 |title=SQL Server Everywhere: Just Another Database? |access-date=2007-07-03}} Unlike other editions of Microsoft SQL Server, SQL CE runs in-process with the application which is hosting it. It has a disk footprint of less than 2 MB and a memory footprint of approximately 5 MB. SQL CE is optimized for an architecture where all applications share the same memory pool.{{cite web |url=https://technet.microsoft.com/en-us/library/ms172417.aspx |title=What's New (SQL Server Compact Edition) |access-date=2007-07-03}}

Windows Store apps for Windows 8 cannot use SQL Server Compact edition, or any other edition of SQL Server.{{cite web|title=How do i run SQL Server Compact 3.5 on Windows 8 developer preview?|url=http://social.msdn.microsoft.com/Forums/en-US/winappswithcsharp/thread/f71c518c-c288-459e-917f-52b9cf8442d7|publisher=social.msdn.microsoft.com|access-date=11 March 2013}}

SQL CE databases can support ACID-compliance, but do not meet the durability requirement by default because AutoFlush buffers changes in memory (including enlisted ambient transactions and explicit SQL CE transactions that do not override the Commit() call with an CommitMode.Immediate value). Therefore, committed transaction changes can be lost. To meet the durability requirement the commit call on the transaction must specify the immediate flag.{{cite web|title=Maintaining Databases (SQL Server Compact Edition)| date=28 June 2007 |url=http://msdn.microsoft.com/en-us/library/ms172411(v=sql.90).aspx}} Like Microsoft SQL Server, SQL CE supports transactions, referential integrity constraints, locking as well as multiple connections to the database store. However, nested transactions are not supported, even though parallel transactions (on different tables) are.{{cite web | url = https://technet.microsoft.com/en-us/library/ms172400.aspx | title = Transactions (SQL Server Compact Edition) | access-date = 2007-07-03}} The current release does not support stored procedures or native XML data type either. It uses a subset of T-SQL for querying and due to lack of XML support, XQuery is not supported either. Queries are processed by an optimizing query processor.{{cite web | url = https://technet.microsoft.com/en-us/library/ms172037.aspx | title = Overview of Databases (SQL Server Compact Edition) | access-date = 2007-07-03}} SQL CE databases also support indexing, as well as support remote data replication (local caching of data in remote databases) and merge replication (bidirectional synchronization with master databases).{{cite web | url = https://technet.microsoft.com/en-us/library/ms171986.aspx | title = Overview of Databases (SQL Server Compact Edition) | access-date = 2007-07-03}}

SQL CE databases can be created and managed from Microsoft Visual Studio and some older versions of SQL Server Management Studio as well.

{{Infobox file format

| name = SQL Server Compact

| extension = .sdf

| mime =

| owner = Microsoft

| creatorcode =

| genre = Relational database

| containerfor =

| containedby =

| extendedfrom =

| extendedto =

}}

SQL CE databases reside in a single .sdf file,{{cite web | url = http://msdn2.microsoft.com/en-us/library/bb380177.aspx | title = Data Storage Architecture with SQL Server 2005 Compact Edition | access-date = 2007-07-03}} which can be up to 4 GB in size. The .sdf file can be encrypted with 128-bit encryption for data security. SQL CE runtime mediates concurrent multi-user access to the .sdf file. The .sdf file can simply be copied to the destination system for deployment, or be deployed through ClickOnce. SQL CE runtime has support for DataDirectories. Applications using an SQL CE database need not specify the entire path to an .sdf file in the ADO.NET connection string, rather it can be specified as |DataDirectory|\.sdf, defining the data directory (where the .sdf database file resides) being defined in the assembly manifest for the application.{{citation needed|date=November 2016}}

SQL Server Management Studio 2005 can read and modify CE 3.0 and 3.1 database files (with the latest service pack), but SQL Server Management Studio 2008 (or later) is required to read version 3.5 files. Microsoft Visual Studio Express 2008 SP1 can create, modify, and query CE 3.5 SP1 database files. SQL Server Management Studio cannot read CE 4.0 files. Visual Studio 2010 SP1 can handle CE 4.0 database files.{{citation needed|date=November 2016}}

The .sdf ("Sqlce Database File") naming convention is optional, and any extension can be used.{{citation needed|date=November 2016}}

Setting a password for the database file is optional. The database can be compressed and repaired with the option of the compacted/repaired database to be placed into a new database file.{{citation needed|date=November 2016}}

In February 2013, Microsoft announced that SQL Server Compact Edition had been deprecated.{{cite web|title=Port SQL Compact to Windows RT|url=https://connect.microsoft.com/SQLServer/feedback/details/776328/port-sql-compact-to-windows-rt|author=Barranis, Patrick|date=2013-01-11|website=Microsoft Connect|access-date=2016-06-25}}

Although no new versions or updates are planned, Microsoft will continue to support SQL Compact through their standard lifecycle support policy. Extended support for SQL Server Compact 4.0 ended on July 13, 2021.{{cite web|title=Microsoft SQL Server Compact 4.0 Support Dates|url=https://docs.microsoft.com/en-us/lifecycle/products/microsoft-sql-server-compact-40|access-date=2020-09-17}}

• Mobile database
• SQLite
• {{slink|SQL Server Express#LocalDB}}
• Access Database Engine (ACE and formerly JET)
• HSQLDB
• Firebird

{{reflist}}

• [https://learn.microsoft.com/en-us/archive/blogs/sqlservercompact/ Microsoft's Embedded Database - SQL Server Compact - Team Blog] at Microsoft Learn
• {{webarchive |url=https://web.archive.org/web/20160215155139/http://blogs.msdn.com/b/sqlservercompact |title=Microsoft's Embedded Database - SQL Server Compact - Team Blog}}

{{Windows Mobile}}

{{Microsoft development tools}}

Category:Microsoft server technology

Category:Windows Mobile

Category:Relational database management systems

Category:Microsoft database software

Category:Discontinued Microsoft software

fr:Microsoft SQL Server#Compact Edition

ja:Microsoft SQL Server#MSDE