CODEWITHSUNDEEP
sql-serversql-server-2005schema
BradC
BradC

Reputation: 39916

Best practices for using Schemas in SQL 2005?

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?

Upvotes: 2

Views: 1140

Answers (2)

Cade Roux
Cade Roux

Reputation: 89661

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.

Upvotes: 3

DavGarcia
DavGarcia

Reputation: 18792

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.

Upvotes: -1

Related Questions