locked
Schema RRS feed

  • Question

  • Dear friends,

    I am looking for a clear explanation of the schemas and dbo schema.
    It's hard to find anything understandable.
    Thank you very much.

    Anna

    Sunday, November 4, 2018 7:10 PM

Answers

  • Hi Anna,

     

    A schema is defined as a collection of database objects that are owned by a single user and form a single namespace.

     

    A namespace is a set of objects that can’t have duplicate names. For example, two tables can have the same name only if they are in separate schemas, so no two tables in the same schema can have the same name. You can think of a schema as a container of objects.

     

    The dbo schema is the default schema for a newly created database. When you create a user, you can specify a default schema if you want, but the default for the default schema is the dbo schema. The dbo schema is owned by the dbo user account.

     

    For detailed information about dbo schema, please refer to:

     

    The dbo Schema

     

    For a simple schema example: You creates a schema called test, which is user UserA’s default schema. Then you GRANT CREATE TABLE TO UserA. Supposed that UserA creates a table:

     

    CREATE TABLE Adv (id int)

     

    This table is placed in UserA’s default schema, which may be the schema "test". If another user which is not with test as default schema wants to retrieve data from this table, he can issue this statement(specify the schema):

     

    SELECT id FROM test.Adv

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, November 5, 2018 2:47 AM
  • I think that a schema is a container for  the database objects .You may have one or multiple schemas in a database. The user is only associated with the schema of the same name and often the terms will be used interchangeably. Basically, any user who creates an object has just created it in her own schema unless she specifically instructs it to be created in another one. So, based on a user's privileges within the database, the user has control over objects that are created, manipulated, and deleted

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Monday, November 5, 2018 6:17 AM

All replies

  • Hi Anna,

     

    A schema is defined as a collection of database objects that are owned by a single user and form a single namespace.

     

    A namespace is a set of objects that can’t have duplicate names. For example, two tables can have the same name only if they are in separate schemas, so no two tables in the same schema can have the same name. You can think of a schema as a container of objects.

     

    The dbo schema is the default schema for a newly created database. When you create a user, you can specify a default schema if you want, but the default for the default schema is the dbo schema. The dbo schema is owned by the dbo user account.

     

    For detailed information about dbo schema, please refer to:

     

    The dbo Schema

     

    For a simple schema example: You creates a schema called test, which is user UserA’s default schema. Then you GRANT CREATE TABLE TO UserA. Supposed that UserA creates a table:

     

    CREATE TABLE Adv (id int)

     

    This table is placed in UserA’s default schema, which may be the schema "test". If another user which is not with test as default schema wants to retrieve data from this table, he can issue this statement(specify the schema):

     

    SELECT id FROM test.Adv

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, November 5, 2018 2:47 AM
  • I think that a schema is a container for  the database objects .You may have one or multiple schemas in a database. The user is only associated with the schema of the same name and often the terms will be used interchangeably. Basically, any user who creates an object has just created it in her own schema unless she specifically instructs it to be created in another one. So, based on a user's privileges within the database, the user has control over objects that are created, manipulated, and deleted

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Monday, November 5, 2018 6:17 AM