1

What is the maximum number of user schemas one can create in a single SQL Server 2005 database?

Is this figure different on SQL Server Express vs SQL Server Standard?

I've searched the MSDN documentation but can't find anything approaching an answer. The max capacity page at MSDN doesn't mention schemas.

hjd
  • 115
  • 1
  • 7

1 Answers1

2

Schemas are just objects in the databases so they fall under the maximum objects counters which have a limit of 2,147,483,647.

It'll be the same for all editions of the engine.

mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • slight nitpick, but wouldn't it be 2,147,483,648 ? The total number of possible values with a 32 bit integer? – Neil N Sep 17 '10 at 20:04
  • I copied the number from the MSDN page so its probably correct. – mrdenny Sep 17 '10 at 20:04
  • Maybe 0 is reserved. – Neil N Sep 17 '10 at 20:27
  • Probably given that there are system objects created with positive object_ids you can't ever actually get that many as you can't create an object with an object_id of 1 or 2 as the system objects use sporadic numbers from 3 to 98. Unless of course the object creating code is just grabbing a random number and using that so it will eventually fill in the holes. But then we have to ask, why can't negative numbers be used. There are many systems objects with a negative object_id already. – mrdenny Sep 17 '10 at 20:50