|
Home > Archive > Microsoft SQL Server forum > August 2005 > What does schema mean?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
What does schema mean?
|
|
| wackyphill@yahoo.com 2005-08-26, 1:23 pm |
| I know in SQL Server the terms Database and Catalog are used
interchangably. But a table is also assigned a schema. As seen in the
INFORMATION_SCHEMA.Tables View. I don't get what this schema qualifier
is all about. Like if a table has a schema of dbo.
Can someone explain the relationship the schema has and what it is?
Thanks.
| |
| Erland Sommarskog 2005-08-26, 8:23 pm |
| (wackyphill@yahoo.com) writes:
> I know in SQL Server the terms Database and Catalog are used
> interchangably. But a table is also assigned a schema. As seen in the
> INFORMATION_SCHEMA.Tables View. I don't get what this schema qualifier
> is all about. Like if a table has a schema of dbo.
>
> Can someone explain the relationship the schema has and what it is?
See schema as a way of categorising the objects in a database. This can
be a good thing if several apps share a database, and while there is some
common set of data that all apps accesses, some applications also have
their own private tables. In this case it is a good idea to have a
separate schema for each application where they can have their private
tables, views, stored procedures etc to evade the disk for collisions.
Now, in SQL 2000, there is a serious restriction in that a schema is
tied to a user. In fact, it makes schema virtually useless. But this
changes in SQL 2005 where users have been separated from schemas.
Finally, the schema in INFROAMTION_SCHEMA is a slightly different
usage. Sometimes uses "schema" to refer some set of tables, possibly
all tables in the database. SQL 2000 even has a CREATE SCHEMA command
for creating several tables in one go.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
| wackyphill@yahoo.com 2005-08-29, 7:23 am |
| OK, so schema is roughly like a Group in Windows and Tables are
somewhat like Users?
I think I get it. Thanks for the help.
| |
| DA Morgan 2005-08-29, 11:23 am |
| wackyphill@yahoo.com wrote:
> OK, so schema is roughly like a Group in Windows and Tables are
> somewhat like Users?
>
> I think I get it. Thanks for the help.
If that is, indeed, the way SQL Server works do not try to apply
that understanding to any other database product. Whew!
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
| |
|
| >>
If that is, indeed, the way SQL Server works do not try to apply
that understanding to any other database product. Whew!
<<
In fact, under the hood both SQL Server and Oracle are more similar to
a discount store, and tables are just like aisles
;)
| |
| Serge Rielau 2005-08-29, 1:23 pm |
| AK wrote:
> If that is, indeed, the way SQL Server works do not try to apply
> that understanding to any other database product. Whew!
> <<
>
> In fact, under the hood both SQL Server and Oracle are more similar to
> a discount store, and tables are just like aisles
> ;)
>
*lol* Erland explained it actually I quite well I found and I think I
know what Phil meant by his analogy.
Anyway here is another way of looking at it:
Take a standard filesystem, but allow only one level of directories.
So you can have:
X/a.exe
and
Y/b.bat
but no:
X/Y/c.txt
When a user X logs on the default working directory is X.
When Y logs on his/her working directory is Y.
Now X and Y directories match X and Y schemas in SQL.
Some RDBMS do not allow you do switch your default working directory
without also switching a user. They may or may not allow you to create
directories for which no corresponding users exist.
I take a gamble here and assume that DB2's SQL dialect matches the
standard and hopefully matches what SQL Server 2005 may be doing.
Mappings should be obvious though:
CONNECT TO mydb USER X
=> connected to mydb
VALUES CURRENT_SCHEMA
=> X
VALUES CURRENT_USER
=> X
-- so far so good
SET SCHEMA = Y
VALUES CURRENT_SCHEMA
=> Y
VALUES CURRENT_USER
=> X
-- CD'ed to another schema/directory. We are still X though
CREATE TABLE T(c1 INT)
=> Table Y.T created (!)
SELECT * FROM T
=> Y.T selected because CURRENT_SCHEMA is Y
SET CURRENT_SCHEMA = X
SELECT * FROM T
=> Table X.T not found!
SELECT * FROM Y.T
=> Table Y.T selected
That's schema. Now let's crank up the volume and introduce PATH.
PATH is exactly what it is in your filesystem. I.e. an ordered
collection of schemas/directories which are being searched to find
objects (in SQL normally functions and procedures):
CREATE FUNCTION COOLSTUFF.FOO() .....
SELECT FOO() FROM Y.T
=> Function not found
SET PATH = CURRENT_PATH, COOLSTUFF
SELECT FOO() FROM Y.T
=> Tadah!
PATH <==> PATH
SCHEMA <==> DIRECTORY
USER <==> USER
CURRENT_SCHEMA <==> pwd (in Unix)
CURRENT_USER <==> whoami
SET SCHEMA <==> cd
SET USER <==> su
Hope any of that makes sense in TSQL lingo.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
| |
| DA Morgan 2005-08-29, 8:24 pm |
| AK wrote:
> If that is, indeed, the way SQL Server works do not try to apply
> that understanding to any other database product. Whew!
> <<
>
> In fact, under the hood both SQL Server and Oracle are more similar to
> a discount store, and tables are just like aisles
> ;)
But only one of them is a blue light special. ;-)
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
| |
| Erland Sommarskog 2005-08-29, 8:24 pm |
| (wackyphill@yahoo.com) writes:
> OK, so schema is roughly like a Group in Windows and Tables are
> somewhat like Users?
>
> I think I get it. Thanks for the help.
Given the confusion with users and schema in SQL 2000, this is maybe
not the best analogy. Serge's comparison with a file system was a good
one, and I offer a very similar, but real one: schema is the drive,
and the table is a path on that drive.
(Although on SQL 2005 you cannot really change your current schema like
you change directory in a file system.)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
| wackyphill@yahoo.com 2005-08-29, 8:24 pm |
| (Hopes to get it right this time)
OK, so schema is basically a scoping mechanism much like a namespace.
| |
| Erland Sommarskog 2005-08-29, 8:24 pm |
| (wackyphill@yahoo.com) writes:
> (Hopes to get it right this time)
> OK, so schema is basically a scoping mechanism much like a namespace.
Bingo!
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
| wackyphill@yahoo.com 2005-08-30, 11:25 am |
| Thanks much everyone for your assistance!
|
|
|
|
|