Posted by Serge Rielau on 08/29/05 20:12

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

[Back to original message]