0

What are the maximum number of tables that you can have in:

  • SQL CE4
  • SQL 2008 R2 Express

Numbers here http://msdn.microsoft.com/en-us/library/ms143432.aspx I guess is for SQL Server 2008, but are they valid for Express and what about SQL CE4?

The sum of the number of all objects in a database cannot exceed 2,147,483,647

BTW. Sorry for duplicating questions, but it seems to me Stackoverflow is providing answers more quickly.

Community
  • 1
  • 1
Daniel
  • 8,133
  • 5
  • 36
  • 51
  • 1
    SQL CE 4 limits here, but no word about max objects: http://msdn.microsoft.com/en-us/library/ms172451(v=SQL.110).aspx – ErikEJ Jan 09 '12 at 10:10
  • 1
    If you're doing anything that gets close to hitting these numbers you should really look at your DB design process, if they're being automatically generated then consider not partitioning into so many tables. – Seph Jan 09 '12 at 10:48
  • @Seph Well thank you! Most helpful answer to my question. – Daniel Jan 09 '12 at 11:08

3 Answers3

1

No word about CE, but Express is normal SQL Server + database size and memory / processr limitations.... If you can fit yoru tables into the size, the normal SQL Server limity apply.

TomTom
  • 61,059
  • 10
  • 88
  • 148
1

SQL Server Compact 3.5 limitations here: http://msdn.microsoft.com/en-us/library/ms172451%28v=SQL.105%29.aspx but that only points out 1024 columns per table and no specific table limitation.

SQL Server Express is the same as normal SQL Server as TomTom points out, it's just limited by memory, processor and database size, the limits of SQL Server Express edition are:

  • Constrained to a single CPU
  • 1GB RAM
  • 10GB database size (4GB in versions before SQL 2008 R2 Express)

Otherwise express edition is standard, this is to allow databases to be moved to and from SQL Server Standard to SQL Server Express, which helps in system development. So for SQL Express it's still The sum of the number of all objects in a database cannot exceed 2,147,483,647

As I said in a comment, if you're doing anything that gets close to hitting these numbers you should really look at your DB design process, if they're being automatically generated then consider not partitioning into so many tables.

By this I mean that you should be selecting your SQL Server version based on other requirements, if suppose SQL CE is limited to 256 tables (not saying that it does since I can find no such thing, and it's much more likely to be at least past 1024), and say you need 500 tables. Then either look at denormalizing some of your tables, or at that point you really need to upgrade to including SQL Express with your program (this just complicates your software deployment process, and not a lot more).

I don't have SQL CE avail atm, but the easiest way would be to just have a test go run off and generate 10k create table statements, with each table having 20+ columns. If your requirements are greater than that then just use numbers that you know.

Lastly, there's SQLite which you haven't asked about, again there's no documentation on this but I suspect it's likely 64k+ you're limited to only 64 tables in a join though.

Seph
  • 8,472
  • 10
  • 63
  • 94
  • Thanks! Follow up question, more aligned to your point. Dynamic model where each entity gets a key-value per type of values: Integers, Fractals, Booleans, Strings, Texts, Guids, Dates. That is key-value tables per entity. Purpose. More effective indexes in the tables as opposed to one table holding 7 columns. Thoughts? – Daniel Jan 09 '12 at 11:35
  • Typically I have just used strings (`VARCHAR`) or binary blobs (`VARBINARY`) to store the variable data so the whole table looks like (roughly): `{key VARCHAR(100), dataType INT, value VARBINARY(MAX)}` or similar (maybe with a auto-incriment 'id' column sometimes), and then the type conversion to integer, string, guid, bitmap image etc is done at a simplified ORM layer. Then having an index on the `key` field is sufficient (it's usually also the primary key). You then up implementing a property store pattern. – Seph Jan 09 '12 at 12:00
  • But if you focus on faster queries, the index should include value to, at least for the simple value types (ints, bools etc). And if you query often on values in the integer table and not the others, why should other data affect the integer queries? – Daniel Jan 09 '12 at 12:35
  • True, if you're not querying the data values then you don't need indexes or anything other than a flat property store. But if you're querying the data values (and not the keys so much) then in that case it makes perfect sense to want to store them in separate columns and index them accordingly. Additionally if you are not going to need most of the columns for most of the rows (ie: the columns will mostly be `null` for any given row, such as when only one of `int, string, boolean, date` would be set), at this point then you should look at splitting them off into their own table. – Seph Jan 09 '12 at 13:52
  • That is to say, if the `key` identifies the data type and you want to query on the value of almost any value, at this point it makes sense to have them all be separate tables and indexed accordingly (provided the one `key` won't have most of the data types associated with it) – Seph Jan 09 '12 at 13:54
  • Every key is actually a path of an property in an object hierarchy in a c# graph. The path doesn't have to be unique since it could be enumberables of nested items. When constructing queries the path and value is used to locate a row, that's why I'm separating them. More likely there's going to be more strees on strings table than on boolean hence a DBA can optimize it. – Daniel Jan 09 '12 at 14:22
  • If you are interested in trying it out I can notify you when the rewrite is done? Twitter name? – Daniel Jan 09 '12 at 14:22
0

For SQLCe4, I got the response:

There is no limit on number of tables, but we do have a limit of 4GB file size.

slang
  • 626
  • 7
  • 26
Daniel
  • 8,133
  • 5
  • 36
  • 51