SQL 2008 schema explanation

I've been struggling to get my head around schemas in SQL 2008.  BOL has this incredibly useful piece of wisdom to impart:  "Database schemas act as namespaces or containers for objects, such as tables, views, procedures, and functions, that can be found in the sys.objects catalog view".  Erm.  OK.

That actually didn't help me much.  What do they do?  What are they for?  Why not just apply permissions directly?

Finally, I read this article.  I know Googling will provide lots of hits on "SQL 2008 schema", and all of them say much the same as this article, but for some reason, this article made it all click.  Schemas are roughly analagous to folders in a file system.  An individual file can only live within a single folder at any given time.  A folder can contain lots of files.  In order to apply permissions within the file system, one doesn't waste one's time by applying permissions against each and every file within a folder.  Permissions are applied at the folder level, and the files within the folder inherit those permissions.  That's kind of how SQL schemas work.  I think.

I'm still getting it all straight.  But I think this article does a good job of putting it together.  Unless I've got it all wrong.  In which case, it's a terrible article.

Leave a Reply