locked
What 's the database schema 's usage? RRS feed

  • Question

  •  

    database schema seems just a tag ?

     

    I have found some materials about schema, they said  ,schema is seperated from owner.

     

    But  , no materials are talking about what things schema can do / how to programming with schema.

    Sunday, June 1, 2008 12:47 PM

Answers

  • A schema is nothing more than a named, logical container in which you can create database objects. A new schema is created using the CREATE SCHEMA DDL statement. For instance, you might create a schema for sales-related data by using the following T-SQL:

    CREATE SCHEMA Sales
    GO

    Once created, you can add objects, such as tables or stored procedures, to the schema. To do so, you must prefix them with the schema name:

    CREATE TABLE Sales.SalesPeople
    (
        SalesPersonId INT,
        SalesPersonName VARCHAR(50)
    )
    GO
    

    Just as you might expect, you can access an object by referencing it by schema name:

    SELECT *
    FROM Sales.SalesPeople
    GO
    

    In SQL Server 2005, every object participates in a schema, even if you don't explicitly specify one. Creating an object without specifying the schema will cause it to be created in your default schema. A default schema can be assigned to a user when the user is created, but if it is not specified, the dbo schema will be used. Since any given database user might belong to a different default schema, it's a good idea to always scope tables by schema name. Even if your table is in the dbo schema, you should use two-part naming, such as the following:

    SELECT *
    FROM dbo.myTable
    GO
    

    You can also transfer objects between schemas. That's done by using the TRANSFER option of ALTER SCHEMA. To transfer the SalesPeople table from the Sales schema into the dbo schema, the following T-SQL would be used:

    ALTER SCHEMA dbo
    TRANSFER Sales.SalesPeople
    GO
    

     

    Schemas are truly great from an organizational point of view. Another great benefit of schemas is that you no longer need to worry about assigning users' rights to objects on a granular basis. Rather than create roles for every combination of users, or having to maintain permissions for groups of objects on a per-user basis, schemas allow you to assign rights in blocks. Once assigned schema-level permission, a user will have that right for any object in the schema.

     

    After all, support for schemas can help solve a surprisingly large number of issues that have faced DBAs in past versions of SQL Server. Database consolidation and security management are easier, organization is better defined and even build scripts are easier to create. Schemas may not fix all of your problems as a DBA, but they will make things a lot more straightforward in many cases.

     

    For more detailed information, here are some links I could find for you:

    Hope this helps for you.

    Dannol

    Monday, June 2, 2008 3:05 AM
    Answerer

All replies

  • A schema is nothing more than a named, logical container in which you can create database objects. A new schema is created using the CREATE SCHEMA DDL statement. For instance, you might create a schema for sales-related data by using the following T-SQL:

    CREATE SCHEMA Sales
    GO

    Once created, you can add objects, such as tables or stored procedures, to the schema. To do so, you must prefix them with the schema name:

    CREATE TABLE Sales.SalesPeople
    (
        SalesPersonId INT,
        SalesPersonName VARCHAR(50)
    )
    GO
    

    Just as you might expect, you can access an object by referencing it by schema name:

    SELECT *
    FROM Sales.SalesPeople
    GO
    

    In SQL Server 2005, every object participates in a schema, even if you don't explicitly specify one. Creating an object without specifying the schema will cause it to be created in your default schema. A default schema can be assigned to a user when the user is created, but if it is not specified, the dbo schema will be used. Since any given database user might belong to a different default schema, it's a good idea to always scope tables by schema name. Even if your table is in the dbo schema, you should use two-part naming, such as the following:

    SELECT *
    FROM dbo.myTable
    GO
    

    You can also transfer objects between schemas. That's done by using the TRANSFER option of ALTER SCHEMA. To transfer the SalesPeople table from the Sales schema into the dbo schema, the following T-SQL would be used:

    ALTER SCHEMA dbo
    TRANSFER Sales.SalesPeople
    GO
    

     

    Schemas are truly great from an organizational point of view. Another great benefit of schemas is that you no longer need to worry about assigning users' rights to objects on a granular basis. Rather than create roles for every combination of users, or having to maintain permissions for groups of objects on a per-user basis, schemas allow you to assign rights in blocks. Once assigned schema-level permission, a user will have that right for any object in the schema.

     

    After all, support for schemas can help solve a surprisingly large number of issues that have faced DBAs in past versions of SQL Server. Database consolidation and security management are easier, organization is better defined and even build scripts are easier to create. Schemas may not fix all of your problems as a DBA, but they will make things a lot more straightforward in many cases.

     

    For more detailed information, here are some links I could find for you:

    Hope this helps for you.

    Dannol

    Monday, June 2, 2008 3:05 AM
    Answerer
  •  gem_pplus wrote:

     

    database schema seems just a tag ?

     

    I have found some materials about schema, they said  ,schema is seperated from owner.

     

    But  , no materials are talking about what things schema can do / how to programming with schema.

     

    In addition to Dannol very detailed explanation...

     

    In SQL Server 2000 , the object was owned by a database user and hence User and Objects are tightly coupled. If you want to remove user, you have to change the ownership of those objects to some other user and managing objects /user /security become very difficult in 2000. In SQL Server 2005  schema was introduced. Its nothing but a namespace or a container which owns the objects. Every user has default schema. so the objects - user coupling is gone in this case. Management of objects/user/security simplify in this case.

     

    Madhu

    Monday, June 2, 2008 3:26 AM
  •  

    very thanks to Dannol Liu and Madhu K Nair !
    Thursday, June 5, 2008 11:55 AM