4

It's my impression that schemas are mainly for organizing the tables, view, stored procedures, etc... in a SQL Server database. Do schemas play a bigger role (perhaps in database security, storage, etc)?

Some clarification: I'm referring to "object" schemas. Sorry for the confusion.

Thank you.

Jon Onstott
  • 13,499
  • 16
  • 80
  • 133
  • 2
    this is awefully close to this question: http://stackoverflow.com/questions/529142/what-good-are-sql-server-schemas – Russ Bradberry Jul 14 '09 at 22:29
  • Not very satisfactory answers at that question. Let's see what happens with this one. – Robert Harvey Jul 14 '09 at 22:32
  • Neither is your answer @Robert Harvey, I think you are mixing concepts. Schema as in the "description of the database structures" vs SQL Server schema objects used to group other objects (such as tables) together. – jvanderh Jul 14 '09 at 22:38
  • Microsoft refers to that as an "Object Schema." – Robert Harvey Jul 14 '09 at 22:40
  • The article linked by Andriyev covers both the organizational/grouping/naming uses and the more concrete security uses. – Cade Roux Jul 14 '09 at 22:40

3 Answers3

4

Schemas allow you to group your tables for security and/or conceptual sanity. The group could be a department, a specific area of an application, Active Directory group, db role, etc.

If you have a group of tables that only your HR security group needs access to you can create them under the HR schema and enforce the priviledges from there.

If you have an application you might want to create schemas for Sales, Content, and Products just to separate the parts of the application.

dr.
  • 1,429
  • 12
  • 18
3

Check this link http://technet.microsoft.com/en-us/library/dd283095.aspx. It covers security aspects under the section 'Using Schemas in SQL Server'.

cheers

Arnkrishn
  • 29,828
  • 40
  • 114
  • 128
  • Ok. I wasn't the only one understanding the question differently. My bet this is what the question is getting at. – jvanderh Jul 14 '09 at 22:40
  • 1
    Security is certainly an aspect of it, however having a logical way to organize the objects within a datbase is another. I've worked on many projects with thousands of objects (procs, tables, views) and having them organized into schemas has always made life easier. – Keith Adler Jul 14 '09 at 22:53
-1

From an OO perspective, a database schema could be thought of as a class; with the database itself representing an object instance of that class.

The same implications apply from this analogy (re: security, memory use, etc)

Janie
  • 1,855
  • 2
  • 13
  • 9
  • He's referring specifically to the SCHEMA construct in SQL Server as opposed to what one might generally refer to the database schema meaning the table descriptions and constraints. – Cade Roux Jul 14 '09 at 22:36
  • I would think a SQL Server Database Object Schemas is more analogous to a Namespace in OO. – jvanderh Jul 14 '09 at 22:39
  • 1
    I heartily prefer to keep OO as far away from SQL as possible, thank you. – Eric Jul 14 '09 at 22:59