Thursday, September 25, 2014

Comparison of SQL Server Compact, SQLite, SQL Server Express and LocalDB

Now that SQL Server 2014 and SQL Server Compact 4 has been released, some developers are curious about the differences between SQL Server Compact 4.0 and SQL Server Express 2014 (including LocalDB)

I have updated the comparison table from the excellent discussion of the differences between Compact 3.5 and Express 2005 here to reflect the changes in the newer versions of each product.

Information about LocalDB comes from here and SQL Server 2014 Books Online. LocalDB is the full SQL Server Express engine, but invoked directly from the client provider. It is a replacement of the current “User Instance” feature in SQL Server Express.

Deployment/ Installation Features

SQL Server Compact 3.5 SP2

SQL Server Compact 4.0

SQLite, incl. ADO.NET Provider

SQL Server
Express 2014

SQL Server 2014 LocalDB

Installation size

2.5 MB download size
12 MB expanded on disk

2.5 MB download size
18 MB expanded on disk

10 MB download, 14 MB expanded on disk

120 MB download size
> 300 MB expanded on disk

32 MB download size
> 160 MB on disk

ClickOnce deployment

Yes

Yes

Yes

Yes

Yes

Privately installed, embedded, with the application

Yes

Yes

Yes

No

No

Non-admin installation option

Yes

Yes

Yes

No

No

Runs under ASP.NET

No

Yes

Yes

Yes

Yes

Runs on Windows Mobile / Windows Phone platform

Yes

No

Yes

No

No

Runs on WinRT (Phone/Store Apps) No No Yes No No
Runs on non-Microsoft platforms No No Yes No No

Installed centrally with an MSI

Yes

Yes

Yes

Yes

Yes

Runs in-process with application

Yes

Yes

Yes

No

No (as process started by app)

64-bit support

Yes

Yes

Yes

Yes

Yes

Runs as a service

No – In process with application

No - In process with application

No - In process with application

Yes

No – as launched process

Data file features

SQL Server Compact 3.5 SP2

SQL Server Compact 4.0

SQLite, incl. ADO.NET Provider

SQL Server
Express 2014

SQL Server 2014 LocalDB

File format

Single file

Single file

Single file

Multiple files

Multiple files

Data file storage on a network share

No

No

No

No

No

Support for different file extensions

Yes

Yes

Yes

No

No

Database size support

4 GB

4 GB

140 TB

10 GB

10 GB

XML storage

Yes – stored as ntext

Yes - stored as ntext

Yes, stored as text

Yes, native

Yes, native

Binary (BLOB) storage

Yes – stored as image

Yes - stored as image

Yes

Yes

Yes

FILESTREAM support

No

No

No

Yes

No

Code free, document safe, file format

Yes

Yes

Yes

No

No

Programmability

SQL Server Compact 3.5 SP2

SQL Server Compact 4.0

SQLite, incl. ADO.NET Provider

SQL Server
Express 2014

SQL Server 2014 LocalDB

Transact-SQL - Common Query Features

Yes

Yes

No

Yes

Yes

Procedural T-SQL - Select Case, If, features

No

No

Limited

Yes

Yes

Remote Data Access (RDA)

Yes

No (not supported)

No

No

No

ADO.NET Sync Framework

Yes

No

No

Yes

Yes

LINQ to SQL

Yes

No (not supported)

No

Yes

Yes

ADO.NET Entity Framework 4.1

Yes (no Code First)

Yes

Yes

Yes

Yes

ADO.NET Entity Framework 6 Yes (fully) Yes (fully) Yes (limited) Yes Yes

Subscriber for merge replication

Yes

No

No

Yes

No

Simple transactions

Yes

Yes

Yes

Yes

Yes

Distributed transactions

No

No

No

Yes

Yes

Native XML, XQuery/XPath

No

No

No

Yes

Yes

Stored procedures, views, triggers

No

No

Views and triggers

Yes

Yes

Role-based security

No

No

No

Yes

Yes

Number of concurrent connections

256 (100)

256

Unlimited

Unlimited

Unlimited (but only local)

There is also a table here that allows you to determine which Transact-SQL commands, features, and data types are supported by SQL Server Compact 3.5 (which are the same a 4.0 with very few exceptions), compared with SQL Server 2005 and 2008.

40 comments:

guercheLE said...

Erik,


Due to a problem (not fixed yet) on ping.fm “RSS feeds” http://ping.fm/feeds/ I had to resort to Google Reader + Posterous + hello.txt, but did not note Google Reader sent full content to Posterous. Sorry for that.

I deleted referred post and I'll look for another way for publishing titles and links only. Hope ping.fm “RSS feeds” http://ping.fm/feeds/ come back soon.

Deleted post on my posterous account and clicked the t.co link you sent me just to check it was gone and was directed to same post on another posterous user account (Aleksandr's)

Sorry for the inconvenience.

ErikEJ said...

Guerche: Thanks, and again thanks for linking to me!

limin said...

Hi,ErikEJ.
the SQLCE4 Database file size support is 4G,how many records can be store?
tks~

ErikEJ said...

Limin: It depends on the size of your table! http://msdn.microsoft.com/en-us/library/ms172451(v=SQL.110).aspx

Victor Carazo, PMP said...

Hi Erik,

What about max memory and max number of processors?

ErikEJ said...

Victor: There are not limits on CPUs and memory in SQL Server Compact

BuddyP said...

I have an existing compact 4.0 database created in webmatrix and want to use it in WP7.5. It complains that it is not compatible. How do I create a 3.5 since all my PCs have compact 4.0 installed (Sql Server Management Studio)?

ErikEJ said...

BuddyP: You can use bothe SQL Server Management Studio (it only supports 3.5) or Visual Studio 2010 to create 3.5 files. If you would like to downsize existing data or schema, you can use my scripting tools

Dito said...

Eric,

are there any speed (especially reading speed) differences between 3.5 and 4 versions. The application I am going to use one of these databases will have about 500-600 Mb of text data.

Other features of SQL Server Compact are good for me.

Thanks.

ErikEJ said...

No, I do not think there are any differences in speed between 3.5 and 4.0

Hrvoje said...

Some details abour SQL 2012 Express are not correct. Like filestream support etc. More details are here: Features Supported by the Editions of SQL Server 2012

ErikEJ said...

Hi Hrvoje, I do not mention SQL Server 2012 Express for now, and LocalDB does not support filestream. Any other inaccuracies, please let me know.

Travis Shepherd said...

I notice you say SQL Compact 4 is not supported with Microsoft Sync. Is there an article or anything where this is documented?

Do you know why SQLCE4 would not be compatible with sync? Microsoft is usually very good about backwards compatibility.

ErikEJ said...

Travis: Yes, it is documented here: http://blogs.msdn.com/b/sqlservercompact/archive/2011/01/12/microsoft-sql-server-compact-4-0-is-available-for-download.aspx (but I may come with a solution for that soon!)

Travis Shepherd said...

Thanks for your response. I still don't see any reason (technical or otherwise) that it wouldn't work.

What is the solution you may have soon?

ErikEJ said...

Agreed, cannot see why it should not work, unless SQL CE engine change tracking was disabled.

Bhavesh modi said...

Hi Erik,
I found your comparison Table very useful but still have one confusion.
I want to develop a database for Windows Compact 7, which will be database running on ARM architecture to collect data from PLC Controller under Windows Compact 7.
Please let me know if SQL CE 4 is compatible or not? Will SQL CE 3.5 work fine.

Regards
Bhavesh

ErikEJ said...

Bahvesh: The database on the Windows Compact 7 OS is SQL Server Compact 3.5

mohansagar said...

Hi Erik,
I have installed SQL 2014 CTP2 and need sql server compact edition for it. currently i have SQL server compact edition 4.0 for CTP1 is installed.Would this also support SQL 2014 CTP2?

Could you please provide your inputs?

ErikEJ said...

Mohansagar: SQL Server 2014 and SQL Server Compact 4.0 are in no way related, and SQL Server 2014 merge replication does not support SQL Server Compact 3.5 any more

Dominique said...

If I migrate SQL Compact to SQL Express, I will get features that are not supported in Compact.

What about performance? Would I get better performance with SQL Express?(for use on a laptop)

My guess is, on a laptop, Compact is more appropriate, but I would like to have your advise !

ErikEJ said...

Dominique - it depends, for smaller amounts of data, and for low memory, sqlce is probably best

Afshan Raees said...

Do you have a comparison table between the SQL Server full version and SQL Server Express

ErikEJ said...

Afshan: it is available here http://msdn.microsoft.com/en-us/library/cc645993.aspx

محمد عویدص عطاری said...

Dear Sir, please suggest me which database will be best for my small asp.net mvc application (for 3-4 users).

SQL-CE or SQLITE ?

I'm using VS 2012 express.

ErikEJ said...

It depends - which data access api do you plan to use - if EF, then SQL CE, if just ADO.NET, then both will do fine

Roberto Acosta González said...

It's possible to know if SQL Server 2014 (localdb) use 1 CPU per instance? or use 1 CPU for all instance?

ErikEJ said...

Roberto: that would be per instance

Shravya said...

Not sure if anyone faced same issue. As I scroll down while reading comparison, I had to scroll back and forth to read the column name again. Otherwise nice exhaustive comparison

ErikEJ said...

Shravaya: Updated...

Poey Ching Neo said...

Hi ErikEJ,

I'm migrating an application from older OS 2005 to 2012. This application embeds SQL Compact database. However, I could not locate a SQL Compact version that I can use with Microsoft 2012 R2 OS. I'm new to this ... Any advice will be appreciated.

Vishal Mittal said...

Hi ErikEJ Sir,
Nice article(as always) specially for beginners like me :P .. I wanna ask one question.. Like by SQLCe 3.5 i can add database in my application and use it via dll's in user's machine then user don't have to install SQL Server so any of these databases can do this thing.?? Sorry if u didn't understand as i said I'm beginner... :)

ErikEJ said...

Vishal: Thanks. No, only Compact and SQLite can be installed as files with the app, not LocalDB or SQL Server

Vishal Mittal said...

So can i use SQL CE 4.?? version also for it???

ErikEJ said...

Vishal: Yes, see other blog posts here...

Vishal Mittal said...

Where? and so means i can use Visual Studio 2013... Yipeee!!!!! Thanks but provide me link please...

Ventsi said...

Hi Erik,
I am currently using SQL CE 3.5 and I have trouble switching to VS2013. My database is one table only with less than 50,000 records. The project is VSTO Excel and I am using DataSet to connect DB to dataGridView. I distribute the tool to large number of people WW, so the installation must be as simple as possible. I use Windows Installer 4.5. Could you please recommend one of the following options:
- Use SQL CE and your tool
- Switch to LocalDB
- Switch to SQLite
Thanks!

ErikEJ said...

Ventsi: What "trouble" exactly? Use SQLCE and my tool. of course ;-)

Ventsi said...

Thanks Erik!
I repeated your example with my database in a console application - no issue. But when I tried to repeat it in my existing project, I’ve got a list of errors. Most of them are connected with the dataset I am using to connect the database with the dataGridView. If I cannot use a dataset, what is the other option? I’ve tried Entity and it doesn’t work either. And again, this is a perfectly working tool in VS2010 and now in VS2013 it is a nightmare. Even if I managed to show the data in the dataGridView, I expect to have more problems when I distribute the program to the other computers and when I try to update the local SDF files from the SDF file on the server. But first I have to fix this dataset errors, so could you please give me an example how to load a table from a SDF file without using a dataset or Entity model?
Thanks.

ErikEJ said...

Ventsi: suggest you ask support question like this on msdn or stackoverflow. You can use DataSet and Datagridview - see the Souce code for the toolbox how to do that (resultsetgrid.cs)