I guess I get to be one of the few to see a value in schemas. While there are probably far more scenarios where schemas can be a hinderance, scenarios still do exist where they can be useful. Many have commented on the idea that a database is a shared repository of information, yet, there are times when it is helpful for various organizations to have a "view" or way of seeing a database that is distinct from other organizations that access that database.

For example, suppose that you provide a web-based application to various organizations. You host the databases at your site, and each database has the same database structure, but different data (data unique to each organization) is contained in each database. Oftentimes, in a situation such as this, you would create a different database for each organization that is using our application, but suppose there is some data that is relatively generic and could usefully be shared between all organizations that use the product; zip code information is one example, and there may be other examples unique to the particular class of business you are dealing with. You may want to enforce certain referential integrity constraints on this data to assure - for example - that address information that is entered into a database always has legitimate zip codes, or to protect yourself from various data anomalies that can arise without RI. You could continue to maintain a separate database for each of your customers, and simply keep a copy of the shared information in each database, but that creates more work. Or, you could store the shared information in its own separate database, but then RI becomes troublesome - since you can't have declared RI that spans databases.

Here is a place where the "schema" concept can be useful. An alternate - and workable - solution is to keep all of your customers in a single database, but provide a different schema for each customer. Any objects that are to be shared by all customers can be owned by DBO. Every customer can have their own login that matches the owner of their schema (remember that in this case, a customer is an organization, so this may be actually several users belonging to the same organization that share the login); hence, each customer can see only their objects and objects owned by DBO (to which they've been granted access). Thus, they have an isolated view of the data in the database. Your web-based application becomes simple to program and can be shared by all customers - the login they provide is the key that gives them access to their schema.

Ultimately, it boils down to this (and I've seen at least one post here on SQL Server Central where a database developer had a scenario like this); there are situations where it is helpful for different groups to access different database schemas (either different in structure or different only in data) while at the same time having access to certain common, shared data. There are several ways you can meet this need, but one way - one way that often goes overlooked - is to use the "schema" functionality of SQL Server. Certainly, it can be confusing at times, but the confusion is less a problem with the concept, and more a problem with the fact that we don't use it frequently enough to be familiar with it; and if done properly, the confusion can be largely minimized. In return, you get the ability to give different organization different schemas while still being able to share common data and even have declared referential integrity to that data. This reduces data storage space, database coding (that you might otherwise need to do to work around the inability to declare RI across databases), and application coding (since you can write the code once and not have to write any code to deal with multiple databases).

The need may not be common, but it is still there.

Matthew Burr