Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2013
    Posts
    10

    Unanswered: Schema vs Table - lowly newbie question

    Looking at the large picture I get the relationship of schemas vs. tables. But...looking at the table provided in the book I'm getting confused. I see that all of the Names in the example below are tables in the database. So what is a schema?

    Definition being used for schema is "A schema is a container that you can use to organize database objects. A schema is a way to organize the tables and object within the database." I don't see a separate container, I see a lot of separate tables in a database.

    This seems to be implying that a schema is separate from tables sooo, what am I missing?

    I'm reading Beginning T-SQL 2012, and using the AdventureWorks database

    Name Schema Object
    HumanResources.Employee HumanResources Employee
    Sales.SalesOrderDetail Sales SalesOrderDetail
    Person.Address Person Address

  2. #2
    Join Date
    Feb 2013
    Location
    perth australia
    Posts
    24
    schema is just a logical grouping of objects. the Adventureworks sample database has schema based on depts. You can assign permissions at the schema level and all the underlying objects under that schema inherit the permissions. you don't have to assign individual permissions to the tables. If someone/group belongs to HR, you grant datareader/datawriter to the schema for the user/group and the user automatically has those rights to all objects under the schema.

  3. #3
    Join Date
    Jan 2013
    Posts
    10
    Thank you. That would make sense if there were a "container" for HR, but there are only various HR tables so what are the permissions being assigned too. Obviously I'm still not getting the concept.

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Obviously I'm still not getting the concept.
    Schema objects are in a hierarchy. A column is a scalar value drawn from a domain (somethings work on them, like built-in functions). A row is made up of columns (somethings work on them, like DELETE, INSERT and UPDATE). A table is made up of rows (somethings work on them, like DROP). A schema is made up of tables, triggers, views, and the whole ball of wax.

    In fact, the ANSI Standards never defined a "database", just schema.

  5. #5
    Join Date
    Feb 2013
    Location
    perth australia
    Posts
    24
    don't quite follow what you are saying.There are 6 tables under humanresource schema in adventureworks. Assuming you wanted some to read all 6 humanresource tables, you do it in one hit by assigning read to the humanresource schema.

    Can you answer this question. Is it possible to have two tables with the same name under the same database?

  6. #6
    Join Date
    Jan 2013
    Posts
    10
    I don't think it is possible to have two tables with the same name in the same database.

    Perhaps what I am looking for is the container that contains the database objects it is supposed to organize. Looking at the same HR list you do I see 6 different tables in the Adventure works database. I'm not sure what the dots mean, I'm assuming (bad I know) that the 6 different human resource tables are child tables of the human resource table.

    I'm wrong aren't I?

    At this point I don't understand what a schema is. I read the definition, but I don't see it in the database structure.

  7. #7
    Join Date
    Feb 2013
    Location
    perth australia
    Posts
    24
    yes it is possible to have two tables under the same database if they belong to different schemas. table HR.table1 and Production.table1 are separate.

    to allow for multiserver queries, you select a 4-part name.

    select * from servername.schemaname.databasename.tablename

    From management studio, you can see the schemas defined for a database under security->schemas.

    having said that, a lot of of the shelves apps out there don't use multiple schemas, everything comes under dbo. We use it to when we consolidate data from multiple systems to indentify the source system.

  8. #8
    Join Date
    Jan 2013
    Posts
    10
    Thank you. That was something I didn't know about. Unfortunately, I'm not pulling this idea together.

    What is the container that is organizing these objects? How would HR.table1 and Production.table1 be tables with the same name? They have different tables names.

  9. #9
    Join Date
    Jan 2013
    Posts
    10
    Celko, I'm trying to follow your explanation, but I'm not even going to pretend to understand? Could you clarify?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •