none
When/Why create Schema? Schema v. Databases?

    Question

  • I noticed that http://msdn.microsoft.com/en-us/library/jj851212(v=vs.103).aspx creates a new schema for their example.

    Is that a necessary step for this example?

    I've been using the default schema of dbo and just creating new databases for each new project. What is the difference between creating a new database for a project and creating a new schema? Might I use the same schema in multiple databases where each database is a sub-project?

    When else would I want to create a new schema?

    Thanks

    Siegfried


    siegfried heintze

    Sunday, December 30, 2012 7:07 PM

Answers

  • A schema is unique to a database. You may have a schema called Rudolph in two databases, but they are two distinct schemas.

    You can view a schema as a namespace. It permits different groups to working on the same database to use the same name for different objects without clashes. Schema can also be used for security boundaries, since permissions can be granted per schema.

    The difference between creating a database or a schema for a project is miles wide. If you first develop a web shop for client X, and the next day devlop a POS system for client Y, they should of course be different database. But if you add invoicing to that first client's web shop, you might consider a new schema.

    But it is not really a choice between a new database or a new schema. It's first a question a choice of whether to add a new database or put things in an existing database. Then if you make the latter decision, it's time to consider whether to use a new schema. And if you go for a new database, you have the choice whether to use dbo throughout, or use distinct schemas.

    The system I work with have 1500 tables and 7500 stored procedures. All in dbo. That is not optimal, but the system is older then owner/schema separation in SQL Server, so for us there was no choice, and it is too much work to change it now.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 30, 2012 8:18 PM

All replies

  • Actually you need to know about database schema:

    A principal is any entity or object that has access to SQL Server resources, for example a user, role or a group...http://blog.sqlauthority.com/2009/09/07/sql-server-importance-of-database-schemas-in-sql-server/

    Schema versus Database

    http://www.sqlservercentral.com/Forums/Topic1038190-146-1.aspx


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/



    Sunday, December 30, 2012 7:17 PM
  • A schema is unique to a database. You may have a schema called Rudolph in two databases, but they are two distinct schemas.

    You can view a schema as a namespace. It permits different groups to working on the same database to use the same name for different objects without clashes. Schema can also be used for security boundaries, since permissions can be granted per schema.

    The difference between creating a database or a schema for a project is miles wide. If you first develop a web shop for client X, and the next day devlop a POS system for client Y, they should of course be different database. But if you add invoicing to that first client's web shop, you might consider a new schema.

    But it is not really a choice between a new database or a new schema. It's first a question a choice of whether to add a new database or put things in an existing database. Then if you make the latter decision, it's time to consider whether to use a new schema. And if you go for a new database, you have the choice whether to use dbo throughout, or use distinct schemas.

    The system I work with have 1500 tables and 7500 stored procedures. All in dbo. That is not optimal, but the system is older then owner/schema separation in SQL Server, so for us there was no choice, and it is too much work to change it now.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 30, 2012 8:18 PM
  • A similar thread -

    SQL Server "database" vs. Oracle "schema"


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Sunday, December 30, 2012 8:48 PM
  • One important consideration in the choice of whether when you are developing a new project and considering whether it should go in a new database or a new schema is whether or not it needs relational integrity with the existing data.

    If you need foreign key relationships between the new project and an existing project , then, generally, the new project should be in the existing database, probably in a new schema.  This is because you can define foreign keys from a table in one schema to a table in another schema, but not from a table in one database to a table in another database.

    On the other hand, having the two projects in different databases also has advantages.  If you have two databases you can backup (and restore when needed) the two databases separately.  Also, if your system is overloaded and you want to move one of the projects off to another system (or the cloud) while leaving the first system where it is, that will be far easier if the two projects are in separate databases.

    Tom

    Sunday, December 30, 2012 11:19 PM
  • SQL Server database schema is a "container" of objects such as tables, views, sequences and stored procedures.  Every item in sys.objects has a schema_id assigned. "dbo" schema is the default.

    Non-schema objects like users, roles cannot be "placed" into a schema "container".


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Sunday, December 30, 2012 11:57 PM
  • See this

    http://blog.sqlauthority.com/2009/09/07/sql-server-importance-of-database-schemas-in-sql-server/

    for the concepts of both.


    Many Thanks & Best Regards, Hua Min

    Monday, December 31, 2012 1:17 AM