Posted by david on 7. January 2009 21:20
I wasn’t terribly sure about schemas in SS ‘05 … I knew it was probably an organizational technique. Worried that I it would lead me down a permissions nightmare - but I wasn’t sure & didn’t have much a need to look into it … even at my last gig, with 50 bagillion tables in the db, dbo was the only schema.
My host only gives me only one db in my package. I’ve been using this one db for several different applications. Wasn’t sure whether that was a good thing or a bad thing … kinda figured it was a bad thing since it could easily become a huge mess of unintelligible shite. But, after consulting with my esteemed colleague, used a different schema for an app that I had started to build. It worked … didn’t run into any messes. So far, so good.
Now I’m adding db tables to power the map site & am facing the same question again. This time, I looked about for more solid information.
“A schema is nothing more than a named, logical container in which you can create database objects.” ( source )
While schemas seem to be primarily a security function, it seems that consolidating things into one physical database actually has some benefits:
“[Brings] related groups of objects (logical entities) together within one physical database to reduce physical database administrative overhead” ( SQL Server Best Practices – Implementation of Database Object Schemas )