“But I still see a lot of applications, developed by shops I know as well as vendors, that don’t make use of a Schema. Everything is piled under dbo…. But if you’ll use them properly you can make your application more understandable and portable.”
I disagree – and Buck and I started a conversation on Twitter which quickly turned into a group discussion. It merits more than 140-character comments, but since Buck’s blog temporarily has comments turned off, I wanted to give people a place to discuss.
My thoughts: schemas are naming conventions in lipstick. When I look at somebody’s code, the last thing I ever think is, “Man, if these tables just had schemas, I could understand everything better.” In fact, when I see objects broken up into different schemas, I usually end up asking questions like, “Why are we writing user reports against something in the ETL schema?” The answer comes back, “Well, it used to be an ETL table, but then our needs changed, and we couldn’t rewrite all our code, so it just turned into a production table.” Great.
If they’re used right, they let you segregate permissions by groups of objects. But here’s the thing – almost nobody ever does that. It’s more work than it’s worth except in the largest of databases. When we’re talking about a vendor app that gets sold to third parties, you usually don’t want anybody jury rigging permissions together – just query through the app like you’re supposed to, and keep your filthy hands out of my database.