What does schema mean?

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.

(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

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.

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
;)

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

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)

(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

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

(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