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
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.
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.
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?
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.
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.