By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,883 Members | 955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,883 IT Pros & Developers. It's quick & easy.

What does schema mean?

P: n/a
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.

Aug 26 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
(wa********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 26 '05 #2

P: n/a
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.

Aug 29 '05 #3

P: n/a
wa********@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
da******@x.washington.edu
(replace x with u to respond)
Aug 29 '05 #4

P: n/a
AK
>>
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
;)

Aug 29 '05 #5

P: n/a
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
Aug 29 '05 #6

P: n/a
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
da******@x.washington.edu
(replace x with u to respond)
Aug 29 '05 #7

P: n/a
(wa********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 29 '05 #8

P: n/a
(Hopes to get it right this time)
OK, so schema is basically a scoping mechanism much like a namespace.

Aug 29 '05 #9

P: n/a
(wa********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 29 '05 #10

P: n/a
Thanks much everyone for your assistance!

Aug 30 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.