I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out whether to divide my state using seperate Databases or using seperate Schema's in the same database. I've done a little reading on SQL Server Schema's and it seems like a natural way to seperate object domains (which I like), but I'm not sure if there may be hidden costs to this pattern.

What are the more practical things I should consider when selecting between these two approachs? If I avoid the dbo.mytable in favor of myschema.mytable will I be creating other challenges (or problems) for my architecture?

As a side note... At some point this will be handed over to a real DBA to maintain/support so I'm trying to make sure I don't make their lives harder.

  • a side effect of using a schema other than dbo is that you do not forget to write it. This is a step towards execution plan re-use. Commented Jan 11, 2016 at 9:47

2 Answers 2


I'll start by saying don't consider schemas as namespaces or object domains in the OO sense. Schemas are essentially permission containers with some added value (see below)

Also, "separate schemas" or "separate databases" are 2 different concepts. Data that needs to be transactionally and referentially consistent needs to be in the same database. See One Database or Ten? blog article for more.

In that database, you may or may not use schemas for organising your objects.

Personally, I'm a fan of schemas and always use them but for things like permissions and logical grouping. For that, I'll refer you to previous questions where you can see the general opinion is in favour of them:

For the case for separate databases, see Aaron's answer, but this all hinge on the "transactionally and referentially consistent" requirement.


Agree with @gbn. I've architected solutions using schemas for separation and databases for separation, and I find using databases is much more practical. A couple of reasons:

  1. Having your app connect to a context-specific database and then running the same queries is much easier than having your queries inject <schema> in front of all the object references. This lends itself to either a lot of dynamic SQL, a lot of different application logins with default schemas (meaning your code would not use schema prefix, relying on default schema of application user - can lead to massive plan cache bloat and difficult debugging), or a lot of stored procedures to manage (picture just a simple report, if you need one per schema, then the code changes, now you have to go change the same code multiple times, once for each schema).
  2. Separating by database allows you the flexibility to move busy databases to faster or different storage / instances without having to rip objects out of one big all-encompassing database. Most folks don't think about it this far in advance, but it is definitely a potential scale problem. Especially if you end up having one schema that "takes over" and requires most of the resources on the server, splitting them out can be an extremely cumbersome task, even if they are already separated by schema.
  3. Separate databases can also allow you to have different maintenance and backup schedules for each division. I am not sure what you mean by "divide by state" but assuming you mean isolating customers, you may have customers with different requirements and different tolerance for data loss, index maintenance, etc.
  4. You may end up with customers who, by law or by corporate policy, need you to keep their data separate from anyone else. This is usually acceptable at the database level, but schema level is usually going to be insufficient. You may not have these customers now, but it could become a real problem later if you do.
  • 3
    The golden question now is "is all data transactionally and referentially consistent"? I assume now and your answer is correct for this
    – gbn
    Commented Jan 21, 2012 at 19:25
  • @gbn That’s a really good question and something I need to give some thought too before committing on a path.
    – JoeGeeky
    Commented Jan 22, 2012 at 10:53
  • @AaronBertrand This is interesting... Sounds like I need to do a little capacity planning, and see where scaling issues may arise. If scaling horizontally is suitable, then separate databases may be a good choice? Otherwise, I'll have to consider other patterns.
    – JoeGeeky
    Commented Jan 22, 2012 at 11:05
  • 2
    @JoeGeeky: subject to "transactionally and referentially consistent", a single database can be scaled with filegroups too. Anyway, you have 2 valid ansers based on your use case. Neither is incorrect.
    – gbn
    Commented Jan 22, 2012 at 11:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.