Results 1 to 8 of 8

Thread: Sql server Info

  1. #1
    Join Date
    Jul 2004
    Posts
    76

    Unanswered: Sql server Info

    Hi All,

    What is similar to "Schema" and "Table space" in SQL Server?

    What is the physical organization/structure of SQL Server?

    Thanks in advance,
    Jai

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A schema is a schema, regardless of the database engine. What Oracle chooses to call a schema (for the purposes of the schema views) is represented in SQL by the INFORMATION_SCHEMA views. Again, this isn't a perfect fit, but its pretty close in most cases.

    An Oracle "table space" is roughly analagous to a SQL Server user, but it is often implemented as a different database on the same server (instance). This isn't a perfect match, so you'll have to consider exactly what you mean/how you are using the table space to decide what is the best fit for your needs.

    -PatP

  3. #3
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Quote Originally Posted by Pat Phelan
    A schema is a schema, regardless of the database engine. What Oracle chooses to call a schema (for the purposes of the schema views) is represented in SQL by the INFORMATION_SCHEMA views. Again, this isn't a perfect fit, but its pretty close in most cases.

    An Oracle "table space" is roughly analagous to a SQL Server user, but it is often implemented as a different database on the same server (instance). This isn't a perfect match, so you'll have to consider exactly what you mean/how you are using the table space to decide what is the best fit for your needs.

    -PatP
    I would say a tablespace is more like a file group in SQL Server but with tons more control like being able to assign a user a default tablespace, etc

  4. #4
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Here is how I would describe it:

    SQL Server:
    Files
    file groups
    database
    db owner,users
    objects

    Oracle:
    Files
    tablespace(s)
    user
    objects

  5. #5
    Join Date
    Jul 2004
    Posts
    76
    Thanks a lot. Really ground info which I was looking for such a long time.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    An Oracle "Schema" is roughly equivalent to object ownership in SQL Server.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...and tablespace is more like a placement of a filegroup within the data device.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    SQL server doesnt use tablespaces like oracle does.
    data management comes from a fixed extent size(64k) based off of eight 8k pages. but, if you want to compare apples to apples, it goes to say that SQL has always had a data management architecture that Locally managed tablespaces mimics. none of that next, initial, pctfree, pctused stuff for us.
    The only process that even comes close to Tablespaces (in SQL Server)is filegroups and their purpose is to allow you to logically break the db up so as to facilitate a faster backup. filegroups afford very little in the way of control that tablespaces do.
    as far as schema is concerned. SQL Server has a ruduced level of functonality when it comes to schema (compared to 9i).
    in essense, the schema is just used to define object reference during object access
    Code:
    ex select * from schema1.table1
    however, if you check out SQL 2005... they are implementing synonyms and other components that direct SQL closer to an oracle model.

    PS
    An Oracle "table space" is roughly analagous to a SQL Server user, but it is often implemented as a different database on the same server (instance). This isn't a perfect match, so you'll have to consider exactly what you mean/how you are using the table space to decide what is the best fit for your needs ... Pat
    NOPE

    in oracle there is no such thing as a "Table Space".. there are tablespaces however, which are logical collections of datafiles. they are locations where default data goes. the system tablespace is where all data dictionary tables exist, the temp tablespace is where temporary operations go, etc....
    you create tablespaces to stoer objects and to keep objects away from areas of traffic, storage, and of like definitions.
    ex put tables and related indexes in a tablespace on a separate disk array.
    create a user tablespace and an temporary tablespace and redirect all user profiles to use those as the defaults instead of system,,,,,

    here is a simple nuts an bolts explanation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •