Now that SQL Server 2012 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 2012 (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 2012 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.
| Feature | SQL Server Compact 3.5 SP2 | SQL Server Compact 4.0 | SQL Server | SQL Server 2012 LocalDB |
| Deployment/Installation Features | ||||
| Installation size | 2.5 MB download size | 2.5 MB download size | 120 MB download size | 32 MB download size |
| ClickOnce deployment | Yes | Yes | Yes | Yes |
| Privately installed, embedded, with the application | Yes | Yes | No | No |
| Non-admin installation option | Yes | Yes | No | No |
| Runs under ASP.NET | No | Yes | Yes | Yes |
| Runs on Windows Mobile / Windows Phone platform | Yes | No | No | No |
| Installed centrally with an MSI | Yes | Yes | Yes | Yes |
| Runs in-process with application | Yes | Yes | No | No (as process started by app) |
| 64-bit support | Yes | Yes | Yes | Yes |
| Runs as a service | No – In process with application | No - In process with application | Yes | No – as launched process |
| Data file features | ||||
| File format | Single file | Single file | Multiple files | Multiple files |
| Data file storage on a network share | No | No | No | No |
| Support for different file extensions | Yes | Yes | No | No |
| Database size support | 4 GB | 4 GB | 10 GB | 10 GB |
| XML storage | Yes – stored as ntext | Yes - stored as ntext | Yes | Yes |
| Binary (BLOB) storage | Yes – stored as image | Yes - stored as image | Yes | Yes |
| FILESTREAM support | No | No | Yes | No |
| Code free, document safe, file format | Yes | Yes | No | No |
| Programmability | ||||
| Transact-SQL - Common Query Features | Yes | Yes | Yes | Yes |
| Procedural T-SQL - Select Case, If, features | No | No | Yes | Yes |
| Remote Data Access (RDA) | Yes | No (not supported) | No | No |
| ADO.NET Sync Framework | Yes | No | Yes | Yes |
| LINQ to SQL | Yes | Yes | Yes | |
| ADO.NET Entity Framework 4.1 | Yes (no Code First) | Yes | Yes | Yes |
| Subscriber for merge replication | Yes | No | Yes | No |
| Simple transactions | Yes | Yes | Yes | Yes |
| Distributed transactions | No | No | Yes | Yes |
| Native XML, XQuery/XPath | No | No | Yes | Yes |
| Stored procedures, views, triggers | No | No | Yes | Yes |
| Role-based security | No | No | Yes | Yes |
| Number of concurrent connections | 256 (100) | 256 | 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.
16 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.
Post a Comment