Can a large number of schemas present a problem in some ways (performance, maintenance)? By large I mean a few thousands, and schemas will mostly contain views, fetching data from shared tables.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Since SQL Server can handle up to 2,147,483,647 objects in a database, I suspect that having thousands of schemas will only incrementally cause slowness. (Just like anything else, the more different things you have the more processing you need.)
I imagine that your biggest problem will be remaining organized with thousands of schemas to manage. Depending on what you are trying to accomplish, there may be other approaches. For example, row and cell level security.
You might have seen this before, but here is a 2005 paper on implementing row and cell security. http://technet.microsoft.com/library/Cc966395
I have done row/cell security without implementing everything in this paper. So, if that looks better to you, then be judicious in choosing the level of complexity.