Why use database schemas?

Question

I'm working on a single database with multiple database schemas,

e.g
[Baz].[Table3],
[Foo].[Table1],
[Foo].[Table2]

I'm wondering why the tables are separated this way besides organisation and permissions.

How common is this, and are there any other benefits?

Answer 1

You have the main benefit in terms of logically groupings objects together and allowing permissions to be set at a schema level.

It does provide more complexity in programming, in that you must always know which schema you intend to get something from - or rely on the default schema of the user to be correct. Equally, you can then use this to allow the same object name in different schemas, so that the code only writes against one object, whilst the schema the user is defaulted to decides which one that is.

I wouldn't say it was that common, anecdotally most people still drop everything in the dbo schema.

Answer 2

I'm not aware of any other possible reasons besides organization and permissions. Are these not good enough? :)

For the record - I always use a single schema - but then I'm creating web applications and there is also just a single user.

Update, 10 years later!

There's one more reason, actually. You can have "copies" of your schema for different purposes. For example, imagine you are creating a blog platform. People can sign up and create their own blogs. Each blog needs a table for posts, tags, images, settings etc. One way to do this is to add a column blog_id to each table and use that to differentiate between blogs. Or... you could create a new schema for each blog and fresh new tables for each of them. This has several benefits:

• Programming is easier. You just select the approppriate schema at the beginning and then write all your queries without worrying about forgetting to add where blog_id=@currentBlog somewhere.
• You avoid a whole class of potential bugs where a foreign key in one blog points to an object in another blog (accidental data disclosure!)
• If you want to wipe a blog, you just drop the schema with all the tables in it. Much faster than seeking and deleting records from dozens of different tables (in the right order, none the less!)
• Each blog's performance depends only (well, mostly anyway) on how much data there is in that blog.
• Exporting data is easier - just dump all the objects in the schema.

There are also drawbacks, of course.

• When you update your platform and need to perform schema changes, you need to update each blog separately. (Added yet later: This could actually be a feature! You can do "rolling udpates" where instead of updating ALL the blogs at the same time, you update them in batches, seeing if there are any bugs or complaints before updating the next batch)
• Same about fixing corrupted data if that happens for whatever reason.
• Statistics for all the platform together are harder to calculate

All in all, this is a pretty niche use case, but it can be handy!