| Author |
Topic |
|
chutestrate
Starting Member
17 Posts |
 Posted - 2013-02-14 : 08:03:54
|
| 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 databaseName Schema ObjectHumanResources.Employee HumanResources EmployeeSales.SalesOrderDetail Sales SalesOrderDetailPerson.Address Person Address |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-14 : 08:14:13
|
| 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. |
 |
|
|
chutestrate
Starting Member
17 Posts |
Posted - 2013-02-14 : 09:22:13
|
| 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? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-14 : 09:30:46
|
| 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. |
|
|
|
chutestrate
Starting Member
17 Posts |
Posted - 2013-02-14 : 11:11:37
|
| 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? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-14 : 11:38:21
|
| 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. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-14 : 11:52:43
|
| 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. |
|
|
|
chutestrate
Starting Member
17 Posts |
Posted - 2013-02-14 : 12:18:48
|
| so tables are contained in schemas which are contained in databases which are contained in servers. that's the way it works? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-14 : 12:32:56
|
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. |
|
|
|
chutestrate
Starting Member
17 Posts |
Posted - 2013-02-14 : 12:48:46
|
| 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? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-14 : 12:53:14
|
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. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-02-14 : 13:51:35
|
| 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. |
|
|
|
chutestrate
Starting Member
17 Posts |
Posted - 2013-02-14 : 17:13:49
|
| Maybe I shouldn't ask, but what is a database role, and why would they be better for managing permissions? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-14 : 18:45:02
|
| 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.aspxThat is my vague and imprecise definition. Google for it and you will find good articles and descriptions. |
|
|
|
|
Schema vs. tables - lowly newbie question