What is a Schema in SQL Server 2005?

By on February 21, 2009 in SQL Server

What is a Schema?

In Microsoft SQL Server 2005, a schema is a collection of objects adhering to the ANSI SQL-92 standard.

The ANSI SQL-92 standard defines a schema as a collection of database objects that are owned by a single principle and form a single namespace.

All objects within a schema must be uniquely named and a schema must be uniquely named in the database catalog. SQL Server 2005 breaks the link between users and schemas, users do not own objects. Schemas own objects and principles own schemas.

A schema can be owned by either a primary or secondary principle, with the term “principle” meaning any SQL Server entity that can access securable objects.

Principle types that can own schemas:

  • Primary
    • SQL Server Login
    • Database User
    • Windows Login
  • Secondary
    • SQL Server Roles
    • Windows Groups
    • Default Schemas

Users can now have a default schema assigned using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER commands. If no default schema is supplied for a user then DBO will be used as the default schema.

——————————————————————————–

SQL Server 2005 Schema Objects

  • Tables
  • Indexes
  • Views
  • Synonyms
  • Stored Procedures
  • Triggers
  • Functions
  • Assemblies
  • Types
  • Defaults

22 Responses to What is a Schema in SQL Server 2005?

  1. Rashmi June 12, 2009 at 12:25 pm #

    Thanks!! This makes the purpose of schema in sql server 2005+ very clear.

    Reply
  2. josphine May 31, 2010 at 4:42 am #

    thanks it is very useful…

    Reply
  3. Koteswar Rao June 3, 2010 at 8:01 am #

    Thanks …it’s very clear……….

    Reply
  4. kinjal panchal June 10, 2010 at 7:10 am #

    its really nice article thnx a lot ….its easy to understand wht is schema…it is really helpful…

    Reply
  5. Anuradha July 2, 2010 at 5:13 am #

    Commemorate answer…….Thanks!!!!!!!!

    Reply
  6. sunil August 6, 2010 at 6:16 am #

    really helpful

    Reply
  7. chana August 13, 2010 at 4:03 am #

    Good.

    Reply
  8. claude September 1, 2010 at 12:50 am #

    Simple and very clear, keep this up

    Reply
  9. JAADU October 3, 2010 at 3:18 am #

    GOOD AND CLEAR

    Reply
  10. Naresh October 26, 2010 at 9:43 pm #

    It’s very nice and clear and I need transaction management information also.

    Reply
  11. Naresh October 26, 2010 at 9:44 pm #

    plz provide article on this topic also

    Reply
  12. Ardath Reinwald January 17, 2011 at 6:11 pm #

    Hi, I found your website @ yahoo and i’ve read a few of your different posts. I love what u post! I just added you to my Google Information Reader ;) . Keep up the great work . Stay up for see more from you within the future.

    Reply
  13. suresh January 28, 2011 at 1:20 am #

    thankyou for explian schemas

    Reply
  14. Avanish kumar March 10, 2011 at 11:31 pm #

    i prefer it so much

    Reply
  15. rajesh chauhan April 16, 2011 at 3:29 am #

    simple ‘n’ good

    Reply
  16. RAjKumar U April 18, 2011 at 11:25 pm #

    Good and try to write these sort of articles more which will help us alot. :)

    Reply
  17. Dhamo April 23, 2011 at 10:08 am #

    Simple & elegant ….

    Reply
  18. mayur patil April 27, 2011 at 11:11 pm #

    Thanks really meaningful..

    Reply
  19. Vikrant Sharma May 25, 2011 at 11:49 pm #

    very helpful , Thanks!

    Reply
  20. Mahender July 15, 2011 at 2:52 am #

    Thanx.

    this is very usefull and very simple way… thanx a lot..

    Reply
  21. M. Sandal July 15, 2011 at 2:57 am #

    This is useful tip thanks..

    Reply

Leave a Reply