ooking 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

In your example of HumanResources.Employee, HumanResources is the schema and Employee is the table. It may be useful to think of a database server, a database, a schema, and a table (or other objects) being in a hierarchy. A database server can contain zero or more databases. Each database can contain one or more schemas. And each schema can contain zero or more tables and other objects such as views, stored procedures etc.

In fact, the four part naming convention that SQL Server supports is a reflection of this four-level hierarchy (http://www.mssqltips.com/sqlservertip/1095/sql-server-four-part-naming/) So HumanResources.Employee could also be referred to as AdventureWorks.HumanResources.Employee or YourServerName.AdventureWorks.HumanResources.Employee. You wouldn't need to specify the database (unless you are trying to refer to the table from another database on the same server) or the servername (unless you were trying to access the table from another server which is linked to this server).

You can look at the schemas you have in your database in object explorer under security.

Thank you. I did see that section last night. I'm having a hard time forming my questions. I follow your explanation of server, database, table. To me that is the schema. How does schema fit into the 4 part hierarchy?

People use the word "schema" to refer to the tables and design of a database. "If it requires a change in the database schema, I am going to have to check with the big boss" etc. In that context they are referring to changes to the tables, views, etc.

However, the name "schema" used in the context that we are discussing is different. Forget for a moment that the word we use is schema. Let us assume that it is ObjectContainer. Now think of the structure of a system as being [Server] -> [Database] -> [ObjectContainer] -> [Tables,Views,StoredProcs etc].

Even when you create a table without specifying the name of an [ObjectContainer], it is put into an [ObjectContainer] by default. The default [ObjectContainer] is dbo. In fact, many databases may not have any other [ObjectContainer] other than dbo.

Assuming all of that makes sense, Microsoft chose to call the [ObjectContainer] as schema. And, as I said earlier, it is not used in the same meaning as the word "schema" that we use when we want to refer to the structure of objects in a database.

Hope that is a little bit clearer. If not please ask - I am sure people who can explain it better will step in and clarify.

It does make more sense I think. My boss is telling me that it's just a name space, and the book definition is stating it's more. I'm just getting started with this, and it's a bit confusing. So the schema isn't part of the table structure at all?

It depends on how you look at it. A Schema can be thought of as a namespace and that is fine. However, there are other things associated with a Schema (like permissions), so if you are being pedantic about it, it is more than just a namespace.

I think it is more than just a namespace - not really equivalent to the namespaces in used in CLR/.Net or XML namespaces. Database schemas are true containers - for example, you can grant or deny privileges on a schema, a schema is owned by a principal etc.

Editing: I see I am repeating what Lamprey said. Had typed this and forgot to hit submit.

And if permissions need to be assigned you wouldn't do it at the database level, you would start at the schema level and go down if necessary?

Depends. There are trade offs. It is ovbiously easier to grant permissions at a high level. But, that comes at a cost of perhaps over-granting. You can also grant permissions on an object level. Depends on how tightly you want/need to control your permissions.

Generally speaking, database roles are better suited for managing permissions. You can use built-in roles or create your own. Roles cannot contain schemas, only users and other roles, but you can apply fine-grained permissions Lamprey mentions to any set of objects, even in multiple schemas, and they're independent of the schema.

It is a way of grouping users (or other roles, or windows groups or logins) who need to do similar activities. There are some roles that have been predefined, so called fixed roles. There can be user-defined roles as well. Either type can be at the server level or database level. And there are application level roles.