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 | SQL Server 2014 LocalDB |
| Installation size | 2.5 MB download size | 2.5 MB download size | 10 MB download, 14 MB expanded on disk | 120 MB download size | 32 MB download size |
| 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 | 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 | 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 | Yes | Yes | |
| LINQ to SQL | Yes | 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.
74 comments:
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.
Guerche: Thanks, and again thanks for linking to me!
Hi,ErikEJ.
the SQLCE4 Database file size support is 4G,how many records can be store?
tks~
Limin: It depends on the size of your table! http://msdn.microsoft.com/en-us/library/ms172451(v=SQL.110).aspx
Hi Erik,
What about max memory and max number of processors?
Victor: There are not limits on CPUs and memory in SQL Server Compact
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)?
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
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.
No, I do not think there are any differences in speed between 3.5 and 4.0
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
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.
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.
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!)
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?
Agreed, cannot see why it should not work, unless SQL CE engine change tracking was disabled.
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
Bahvesh: The database on the Windows Compact 7 OS is SQL Server Compact 3.5
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?
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
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 !
Dominique - it depends, for smaller amounts of data, and for low memory, sqlce is probably best
Afshan: it is available here http://msdn.microsoft.com/en-us/library/cc645993.aspx
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.
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
It's possible to know if SQL Server 2014 (localdb) use 1 CPU per instance? or use 1 CPU for all instance?
Roberto: that would be per instance
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
Shravaya: Updated...
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.
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... :)
Vishal: Thanks. No, only Compact and SQLite can be installed as files with the app, not LocalDB or SQL Server
So can i use SQL CE 4.?? version also for it???
Vishal: Yes, see other blog posts here...
Where? and so means i can use Visual Studio 2013... Yipeee!!!!! Thanks but provide me link please...
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!
Ventsi: What "trouble" exactly? Use SQLCE and my tool. of course ;-)
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.
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)
Erik: After a lot of experiments I finally found 2 lines of code that loads the “*.sdf” database table into the dataGridView:
private void ResultsetGridLoad()
{
BindingSource selectedMaps = new BindingSource();
using (var connection = new SqlCeConnection(dbConnectionString))
{
using (var context = new CompressorMapDBContext(connection))
{
selectedMaps.DataSource = context.CompressorMaps;
this.dataGridView1.DataSource = selectedMaps;
}
}
}
I have another stupid question: How to copy context.CompressorMaps, which is System.Data.Linq.Table (generated from your tool) into a simple System.Data.DataTable or into a DataSet. If this is not the proper blog, could you please send me a link to the correct one? Thanks!
Best regards, ErikEJ.
Thank you very much by comparison. I am currently a shared project WinRT and sought persistent tool for both data due to SQL Server CE is not supported and SQLite is fine, flexible and fits my chances. I feel that 140TB help me to start :D.
Good night.
Hey Erik,
thanks for that comarison. I need a small filebased local db with non admin install option, but i also need to sync this db with an oracle database.
You wrote that sync framework is supported in ce 3.5, but not in 4.0. do you have an idea why this features disappears?
Nico
Dosihris: You can actually use Sync Framework with SQL CE 4.0, I have added a link to the relevant post
Erik:
I have searched far and wide and I cannot seem to find out how you get transactions to work in SQL CE 4.0.
I have a WebMatrix app using the Database helper. As part of the integrity of the app, I need to be able to guarantee that a series of 5 INSERTS must all succeed (or none would succeed).
In SQL Server (Express and higher) I can wrap those inserts up inside a BEGIN TRANSACTION and COMMIT or ROLLBACK. No such capability seems to exist in SQL CE 4.0 or if it does, it is very well hidden.
Can you give me any advice on how (or even whether) I can use transactions in SQL CE 4.0 to maintain integrity?
You use can use SqlCeTransaction with SQL Compact, but the Database helper in Webmatrix (designed for beginner programmers) does not allow that to happen.
Hi Erik, I'm developing an asp.net mvc webapp and I need to store simple settings and relationships that are inherent to my app (not user data). They are settings that an Admin of the app will fulfill. What db is more suitable, LocalDb or SQL Compact?
Thanks for your help!
Germán
German: I wonder if a simple XML file would be better? But otherwise SQLCE
Thanks Erik I configured SQLCE 4.0 SP1 in my app and now I'm settings things up. For now it's working great, just does what I needed. Of course I've also installed SQL Server Compact/SQLite Toolbox ;-)
Thanx again for your help!
Cheers
Germán
German: Thanks, If you like my free tools, I would be very grateful for a review here: https://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1
Hi Erik, I'm looking for a database that I can use offline, build for the .NET 4.0+ framework. I see that SQL Compact Edition 4.0 does the job BUT I have read that Microsoft is dropping support for it. I was wondering if you can comment on this and provide some reasons why this is and what should be the solution moving forward.
Thanks, Dave.
Dave: SQL Compact is supported until 2021. Microsoft suggests using SQLite instead, but I find that a poor RDBMS when coming from SQL Compact
"Microsoft suggests using SQLite instead, but I find that a poor RDBMS when coming from SQL Compact"
I would love to hear more of your thoughts on why you think SQL CE is better, or at least in what generic usages it would be better, or worse. I am trying to figure out what sort of a disadvantage using SQLite would be compared to SQL CE. I know SQL CE is supported for another 5-6 years but that's actually not a great deal of time, I find time accelerates as you get older. :/
I have the same question as CAD bloke. I am using SQL CE in a WinForms app that's developed with VS Express 2013 and deployed via ClickOnce. I want to maintain the low overhead deployment capability.
My RDBMS needs are very modest. My databases are very small (2 with < 5 tables having at most 20 columns and 100 rows each and one with < 25 tables having a similar column/row profile). Also, I only do individual table "Fill" and "Update" operations via TableAdapters. No joins, referential integrity, etc.
I discovered the future of SQL CE when I found the database designer doesn't work anymore in VSE2013. Helpful to know that MS has a 2021 support end date. I'd just as soon get on with moving to SQLite (if that's what makes most sense for me), but am open to installing your Toolbox to get SQL CE database designer support back for now so I can make changes and still get the generated strongly-typed dataset code.
CAD bloke/Steve Rehling: If you are used to a strongly typed RDBMS, where you can only put numbers in number columns, and dates in datetime columns, you are in for a surprise with SQLite, which basically is "untyped" - this is what I mean by "poor". So a bucket versus a filing cabinet.
Steve: I suggest you use the approach I descibe here instead of DataSets, which incur way to much overhead for an embedded database.
Steve: Forgot the link : http://erikej.blogspot.dk/2013/10/sql-server-compact-4-desktop-app-with.html
Erik - Thanks for the advice. It will take me awhile to absorb the implications of moving to LINQ, which I assume will require code changes everywhere I reference or update the DataSets and their tables. Not as simple as replacing the backend DBMS which the DataSets mask.
Your suggestion means that I now have 2 different though related decisions: (1) LINQ vs DataSets; and (2) SQL CE vs SQLite. I'm guessing any of the 4 combinations will work. If that's not right, please let me know.
Your blog is great. Very clearly written and thorough. I appreciate the effort you've put into it and in giving me your personal advice.
Erik: In trying to follow your suggestion to use SQLite and Linq, I may be trying to do something that isn't possible with VS Express 2013, the IDE I’m using as noted in my first post.
1. I installed SQLite (System.Data.SQLite (x86 / x64)) in a test WinForms app using NuGet Package Manager.
2. I did Add / New Item / Linq to Sql Classes in my VS project. That was the only Add option that seemed connected to your suggestion (as I recall from VS Express 2010, there was as Add Local DB option, but I guess that’s gone in 2013).
3. I did Add New Data Source / Database / DataSet from Data Sources which failed to show any SQLite. I thought that would be true and that I’d need your Toolbox.
4. I tried to install your SQL Server Compact/SQLite Toolbox 4.5.0.2 from MSDN, but hit the restriction that the Toolbox requires VS Pro.
Is there any way to get SQLite database designer support in VS Express 2013? If not, is that also true for SQL CE?
Thanks for any continued help.
Steve: Sorry, I missed that you meant "VS Express" by VSE2013. Use VS Community, as VS Express does not support any DDEX extensibility or extensions
Erik - Thanks for your continuing help. I'm making progress understanding the direction you've guided me to take (move from SQL CE with strongly-typed DataSets to SQLite and Linq for my standalone WinForms app), but I clearly still have some big knowledge gaps that have kept me at times from even understanding what some of the instructions/guidance meant.
In case it’s helpful to others to have a consolidated recap and to confirm my own understanding, here’s what I’ve done:
• Installed VS Community 2015 (instead of VS 2013 Express as I had been using).
• Installed the appropriate version of SQLite via the SQLite.org website (I gather I could have also installed it via VS | Tools | NuGet Package Manager).
• Installed via VS | Tools | Extensions Manager your SQL Server Compact/SQLite Toolbox.
• Installed via VS | Tools | NuGet Package Manager the sqlite-net codebase that’s needed for the Toolbox generated DataAccess code.
• Added to my VS project references to System.Data.SQLite, System.Data.SQLite.Linq, and System.Data.SQLite.EF6.
• Studied Linq, including the following explanation which, while a bit old from 2007, is very well written (https://msdn.microsoft.com/en-us/library/bb425822.aspx?f=255&MSPPError=-2147217396).
This has enabled me to use your Toolbox to: (1) create SQLite databases; (2) create the database tables via the Toolbox’s generated scripts; and (3) as an alternative to #2, generate the DataAccess code that contains the classes corresponding to each table as well as the methods to programmatically create the tables via the sqlite-net codebase. This has been very helpful in getting me going.
I started on all this when it came to light the VS DataSources-based designer I had been using to manage my SQL CE databases and their associated strongly-typed DataSets was no longer supported as of VS 2013. I have been thinking your Toolbox is designed to replace that functionality, and to some extent I can see that it does. But I’m uncertain whether it will (as you continue to enhance it)generate the Linq DataContext code (including fully attributed entity classes with relationships) based on the database/table designer input? This looks to me to be the Linq equivalent of a strongly-typed DataSet along with the various ADO.Net objects if I’m getting all this.
Also, is there a more recent version of your Toolbox Visual Guide of Features than the one from August, 2014?
Thanks again for your help. Steve
Hi Erik,
I'm currently using SQL Server Compact 3.5 for an application. However, I found that the .sdf file should be granted with Full access for Everyone. I would like to deny the deletion action in OS level (Windows 7). Do you have any idea how to do so or I should change to use another database?
Thanks,
Quentin
quentin: That should not be required, why do you need it? Maybe we can continue the conversation here: https://github.com/ErikEJ/SqlCeToolbox/issues/new
WARNING: Sqlite can't drop/alter columns/constraints!!
It says "runs within an app". Is there a way to connect to SQL Compact Edition to browse tables?
Yes, I have a number of tools for table browsing
Hi,
Here is my situation
I did develop a WPF with SQL CE 3.5 Visual Studio 2010 for almost 7 years ago for one of mine customer.
We planning to upgrade the solution to VS 2015 or 2017. Here started my problem.
Open the solution in VS 2015, I get some error message regarding referace to sql CE 3.5 dll (this is OK since in my new development PC I had not installed this one but I have installed 4.0).
My issues ins that I can not open XSD file in Dataset designer. I get the following message:
the dataset designer in this version of visual studio is not compatible with SQL server compact connection.
Do you have any suggestion how I can solve this issue?
Is there any tools to open this files as I usd to in VS 2010?
By the way I use your solution (SQLite / SQL Server Compact Toolbox) in other program (greate tool) but I am not if it will help me with this issue.
Best Regards
Nasser
Nasser: DataSets and SQL CE is not recommended and not supported in VS 2013+ - I suggest you switch your data acess layer to LINQ to SQL
I am working with a proprietary product that has SQL Server output capability for reporting. The product is not very configurable on the connection, and will work with anything above SQL 2005. Basically it connects and writes data to database that encompasses about 20 tables.
I am trying to come up with a free solution for the customers that don't have a SQL Server, that will have relatively low resource requirements that would masquerade as the SQL server the product is expecting to connect to. We would be reading from this database for updating our cloud using a C# utility we've written. We can obviously install Express, but it feels bloated for what we need, and if possible, I'd like something more compact. Because it has to look like MSSQL to the originating application, I would think that SQL Lite won't work. Preferably, the originating app could connect to this via IP (assuming that it might be on another machine on the local network). Can you advise?
Furbo: I would recommend SQL Server Express, is 100% compatible with larger SQL Server editions, free and works over the network, and is a small 275 MB download
Is it the network thing that makes that necessary? Would I be able to use CE if it were local?
Furbo: Yes, but SQL CE is very limited compared to SQL Server in many ways.
Sir!
These is a problem for me. I want to attach my ASP.NET MVC 5 application with SQLITE. But I can't find any solution for me. Now, I am using old one SQL Compact which is going in a good way and also supporting code first and all other entity framework features. But I want to attach SQLite because Microsoft is not going to support Compact more. Can you have any solution for ASP.NET MVC 5. Also, I have not find any example on internet to solve my problem (some examples are there which are for Core and some did not provide Entity Framework support).
Also i want to know that, Is SQL Compact is faster or SQL SERVER Express is faster.
Thanks in advance
Best Regards,
Rahmat: I would not recommend using SQL lite on a web site, use SQL Server Express instead.
hello, I'm not a developer by any means, but I can tinker till something finally works! I'm actually a day trader who likes to write my own custom scripts (in C#, .Net 4.5 based) for my trading platform. Unfortunately, one of the things the platform is not good at is the way it loads historical tick by tick data (admittedly, this does ramp up the amount of data noticeably). I am considering attempting to put together something to save the data locally myself using a local/client side database, to hopefully speed things up a bit. In a situation like this one, where the database itself can become ever expanding, while there is no need for an actual server, which version of SQL would you recommend?
@Unsure : I think it would still prefer SQL Express, but keep in mind it has a max database size of 10 GB
Post a Comment