We're planning to move a SQL 2000 database to SQL 2005, and I am familiar with the ability in 2005 to create tables or other objects under a variety of owner/schemas.
We didn't really have that ability in SQL 2000, so I'm wondering what my guidelines/best practices would be for creating/managing multiple schemas.
Should I create one schema for all objects? How should I divide them up?
I use one giant schema for everything that way when setting up a new test server I just have one file to run that I know it contains everything needed.
Some ORMs generate one file per object which can help with tracking changes, perhaps? But I don't see the purpose in doing that manually.
I try to use it to divide up areas of responsibility within the database.
I'll have a util/utils/tools Schema which is pretty portable between databases and has a Numbers table, UDFs, SPs and things to help work on the database. The procedures don't reference anything outside the utils schema.
Then I'll have a scratch/work/temp schema where I can do SELECT INTO and create tables where I want a real table instead of a temp #table. There are basically just tables here, but possible also some views on the tables.
I have a completely separate database for imports and testing results to verify against, but if you didn't have that, I might have an import, export and test/testresults schema which contained those things that are ETL or known good results to regression test against.
Then everything else will only be in a few schemas - or maybe just one. In a large system, each subsystem might be a schema. Code in these can reference other schemas, but should be pretty carefully looked at any time it references anything outside the schema.