SQL Server CE (compact Edition)

Good news for small application developers , Microsoft released compact version of SQLSERVER (SQLCE 4.0 CTP1 ) for desktop ,web as well as for mobiles.This embedded database format is a huge relief from cumbersome and hectic installation of sql server .Although this was from long back still its reliability and features introduced in 4.0 CTP1 is awesome.I would like to introduce the some review and remarks on it in a very short manner.

Sql Server Compact Edition(SQLCE) is a embedded database for Mobile,Desktop and Asp.Net based projects.

For Whom :

• If you are going to develop small/Mid level office application.
• If you want hassle free installation of Database (Copy and Paste option)

What is the Latest Version :

Latest Version : 4.00 CTP1 Download

Last Release : 3.5 SP 2 Download

Some Facts -:

• Supports Data up to 4 GB.
• File based code free Database
• Supports concurrent multiple connection from .
• Runs in process of Application (Other MS sql Editions used to have separate services)
• SQL CE databases reside in a single .sdf file.
• The .sdf files / Database can be password protected as well as encrypted.
• SQL CE databases are ACID-compliant. Like Microsoft SQL Server,
• SQL CE supports transactions, referential integrity constraints, locking as well as multiple connections to the database store

Advantages

• Very Small footprint(2 to 2.05 MB).
• File based Database with enhanced security option.
• All you need is Copying of binary to your installation path.
• Easily Upgradeable to higher versions (MSSQL Express etc..)

Limitations

• Doesn’t support Stored Procedures, also they don’t have plan for it. Go through the nice article from Steve.Lasker Read it.

Just have a look at the detailed comparison matrix with sqlExpress Edition.

Feature	SQL Server Compact Edition	SQL Server Express Edition
Deployment/Installation Features
Installation size	1.7mb download size 1.8mb expanded on disk	53.8mb download size ~197mb expanded on disk
ClickOnce deployment
Privately installed, embedded, with the application
Non-admin installation option
Runs on Windows Mobile platform
Installed centrally with an MSI
Runs in-process with application
64-bit support		Windows on Windows (WOW)
Runs as a service	In process with the application	N + 1
Data file features
File format	Single file	Multiple files
Data file storage on a network share
Support for different file extensions
Database size support	4GB	4GB
XML storage	stored as nText
Code free, document safe, file format
Programmability
Transact-SQL Common Query Features
Procedural T-SQL Select Case, If, features
Remote Data Access (RDA)
ADO.NET Sync Framework		Planed support with a future version
Subscriber for merge replication
Simple transactions
Distributed transactions
Native XML, XQuery/QPath
Stored procedures, views, triggers
Role-based security
Number of concurrent connections	256	Unlimited