20

I'm planning to use SQL CE 4.0 in a small but production web site and I'd like to know how much load SQL CE 4.0 can handle:

  1. Number simultaneous connections,
  2. Number of rows per table, and
  3. The total DB size.
Ricardo Altamirano
  • 14,650
  • 21
  • 72
  • 105
TheBlueSky
  • 5,526
  • 7
  • 35
  • 65
  • It will be very slow. I strongly suggest you use SQLExpress at least. – leppie Jan 14 '11 at 09:06
  • 5
    I want to know how far I can scale with accepted performance, that's why I asked the question :) I'm not expecting 1000's of simultaneous users, but also not 1 user at a time. – TheBlueSky Jan 14 '11 at 09:13
  • 1
    Microsoft seems to have dropped support for CE a couple of years ago (no new version, no press release, nothing...), so it would be a bad idea if you think at least half a step in advance ;) –  Jun 23 '14 at 14:46
  • 1
    @SitnTheShade, agree, but I asked the question more than 3 years ago when SQL CE 4.0 was still fresh :) – TheBlueSky Jun 24 '14 at 05:38
  • 1
    I know this is old now but a good comparison chart can be found here : http://erikej.blogspot.co.uk/2011/01/comparison-of-sql-server-compact-4-and.html – tinmac Feb 25 '16 at 08:48

4 Answers4

13

Something that might be useful to you is Scott Guthrie's blog post on Wednesday 12 Jan 2011 regarding the latest version of SQL CE and its VS2010 integration: VS 2010 SP1 and SQL CE

To cherry pick a couple of quotes:

  • SQL CE can be used for development scenarios, testing scenarios, and light production usage scenarios.
  • Starting with SQL CE 4 you can use it in a web-server as well.
  • For high-volume sites and applications you’ll probably want to migrate your database to use SQL Server Express, SQL Server or SQL Azure.

Personally I would consider that a 4GB & 256 simultaneous connections in-memory database on a webserver is starting to get a bit big, even though that is the limit for SQL CE, I would look to migrate well before then. It is aimed more at certain scenarios, and if your app doesn't fit that scenario then you should consider SQL Express as Leppie mentioned; i.e. if you can install SQL Express either on your web server or on a machine accessible from your web server then you should do so.

Edit:
just as a follow-up, people should note that you must use VS2012 to work with SQL CE 4.0. SSMS 2008 can be used to work with SQL CE 3.5 (but not 4.0), and SQL CE capability has been removed from SSMS 2012. This means if you are developing with SQL CE 4.0, you must use Visual Studio or a third party tool to do your development work (table creation, etc.).

slugster
  • 49,403
  • 14
  • 95
  • 145
  • 2
    When you are in a shared hosting environment, you don't always have the "luxury" of SQL Express, and when you have that you most probably are limited to one DB :) In short, I'm trying to find out how far can I take SQL CE 4.0, while my 1-DB-only SQL Express is used by another application :) – TheBlueSky Jan 14 '11 at 09:50
  • 2
    @TheBlueSky - shared hosting environments is exactly one of the scenarios that SQL CE *is* aimed at. – slugster Jan 14 '11 at 09:58
  • 1
    That's corrent and it's exactly what I'm saying. I already have a SQL Express DB that is used by an application hosted in that shared hosting environment. Now I want to host another application but will use SQL CE this time. My question was how much can I push this application. – TheBlueSky Jan 14 '11 at 10:12
3

According to this page: http://social.msdn.microsoft.com/Forums/en/sqlce/thread/7657710c-a27d-4702-8155-e6886957465a

SQL Server Compact 4.0 is limited to 4 GB.

2

These are the limits for SQL CE 3.5 : http://msdn.microsoft.com/en-us/library/ms172451.aspx.

I couldn't find anything about the limits in SQL CE 4.0, but for what you ask, in 3.5 :

  1. Number simultaneous connection : 256
  2. Number of rows per table : Unlimited
  3. The total DB size : max 4 Gbs.

My guess is it will more or less be the same.

LaGrandMere
  • 10,265
  • 1
  • 33
  • 41
  • 1
    I also couldn't find anything about SQL CE 4.0, and hopped that someone here has something about it :) – TheBlueSky Jan 14 '11 at 09:44
  • What if you had 2 databases at 4Gbs, would that be supported, or would that count as if it were one 8Gb database? – VoidKing Jun 03 '15 at 18:10
1

I only know about 3.5 ,

One issue I found was that COUNT_BIG isn't supported, therefore, using COUNT, which returns an Int, you cant count over 2,147,483,647 records in such a query.

Also no full-text search.