Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Schema Debate

By Steve Jones,

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

Everyday Jones

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:

Total article views: 554 | Views in the last 30 days: 3
 
Related Articles
FORUM

Create procedure permission in schema

Create procedure permission in schema

FORUM

Database schema

Database schema

ARTICLE

Podcast Announcements

Podcast Feeds

FORUM

Schema versus Database

Schema versus Database

FORUM

Help ! Can't login, SELECT permission denied .......

SELECT permission denied on object 'configurations', database 'mssqlsystemresource', schema 'sys'

Tags
editorial    
schema    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones