SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Schema vs. tables - lowly newbie question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chutestrate
Starting Member

17 Posts

Posted - 02/14/2013 :  08:03:54  Show Profile  Reply with Quote
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

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

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 02/14/2013 :  08:14:13  Show Profile  Reply with Quote
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.
Go to Top of Page

chutestrate
Starting Member

17 Posts

Posted - 02/14/2013 :  09:22:13  Show Profile  Reply with Quote
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?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 02/14/2013 :  09:30:46  Show Profile  Reply with Quote
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.
Go to Top of Page

chutestrate
Starting Member

17 Posts

Posted - 02/14/2013 :  11:11:37  Show Profile  Reply with Quote
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?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/14/2013 :  11:38:21  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 02/14/2013 :  11:52:43  Show Profile  Reply with Quote
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.

Edited by - James K on 02/14/2013 11:53:48
Go to Top of Page

chutestrate
Starting Member

17 Posts

Posted - 02/14/2013 :  12:18:48  Show Profile  Reply with Quote
so tables are contained in schemas which are contained in databases which are contained in servers. that's the way it works?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/14/2013 :  12:32:56  Show Profile  Reply with Quote
quote:
Originally posted by chutestrate

so tables are contained in schemas which are contained in databases which are contained in servers. that's the way it works?

Yup.
Go to Top of Page

chutestrate
Starting Member

17 Posts

Posted - 02/14/2013 :  12:48:46  Show Profile  Reply with Quote
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?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/14/2013 :  12:53:14  Show Profile  Reply with Quote
quote:
Originally posted by chutestrate

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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 02/14/2013 :  13:51:35  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

chutestrate
Starting Member

17 Posts

Posted - 02/14/2013 :  17:13:49  Show Profile  Reply with Quote
Maybe I shouldn't ask, but what is a database role, and why would they be better for managing permissions?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 02/14/2013 :  18:45:02  Show Profile  Reply with Quote
You should ask :)

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.

Role is one of those things that Microsoft refers to as Principals. The security model is categorized into Principals and Securables - see picture on this page http://msdn.microsoft.com/en-us/library/ms191465.aspx

That is my vague and imprecise definition. Google for it and you will find good articles and descriptions.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000