I saw a piece recently by Brent Ozar about the lack of value in using database schemas. I have, for most of my career, had Brent's viewpoint that they weren't that useful and I haven't seen much value in using them, even in terms of permissions. Perhaps that's because I've seen a number of third party applications that used incredibly poor database design and soured the notion of schemas for me. Or possibly I don't like them since I've had any number of issues in development as people create their own objects under their own schema and cause integration issues.

In any case, I followed the discussion on Brent's blog, and then on alater editorial from Phil Factor. I noticed that the topic seemed to come up in a number of other blogs, like Alexander Kuznetsov's, and I have to say that I might be changing my mind. My guess is that I haven't run into a situation where I was stumped enough to turn to a schema, but as I read through the arguments given by various people in support of schemas, I suspect that I just haven't encountered enough problems.

The biggest reason I've seen for schemas seems to be the multi-user database, where you have different  types of data or different application data that you want to somehow co-locate in the same database and apply referential integrity. That, and CDC, which seems to use schemas in creative way.

I don't buy the permissions argument, especially as I've often had two different permissions needs for a set of tables.  Roles work fine, and I can see the permissions easily set by the role or object. Perhaps that is more work, but it's never felt even remotely difficult to maintain for me when I have hundreds of tables and thousands of stored procedures.

I am still not sold that schemas are that valuable for most purposes, and I think they can easily confuse people. I'd recommend that you stick with one schema until you know better, and then have a good case for using multiple ones, but if I'm wrong, let me know. And if you want to share your use of schemas, send me an article.

Steve Jones

The Voice of the DBA Podcasts

• Windows Media Podcast - 29.3MB WMV

  

• iPod Video Podcast - 20.6MB MP4

  

• MP3 Audio Podcast - 4.4MB MP3

  

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed:

or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter: