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

Take a look here for a list of benefits of using schemas: http://blog.sqlauthority.com/2009/09/07/sql-server-importance-of-database-schemas-in-sql-server/

Answer 2

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 3

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.

Answer 4

To me, they can cause more problems because they break ownership chaining.

Example:

Stored procedure tom.uspFoo uses table tom.bar easily but extra rights would be needed on dick.AnotherTable. This means I have to grant select rights on dick.AnotherTable to the callers of tom.uspFoo... which exposes direct table access.

Unless I'm completely missing something...

Answer 5

There can be several reasons why this is beneficial:

• share data between several (instances of) an application. This could be the case if you have group of reference data that is shared between applications, and a group of data that is specific for the instance. Be careful not to have circular references between entities in in different schema's. Meaning don't have a foreign key from an entity in schema 1 to another entity in schema 2 AND have another foreign key from schema 2 to schema 1 in other entities.

• data partitioning: allows for data to be stored on different servers more easily.

• as you mentioned, access control on DB level