What good are SQL Server schemas

Question

I m no beginner to using SQL databases  and in particular SQL Server   However  I ve been primarily a SQL 2000 guy and I ve always been confused by schemas in 2005    Yes  I know the basic definition of a schema  but what are they really used for in a typical SQL Server deployment   I ve always just used the default schema   Why would I want to create specialized schemas   Why would I assign any of the built-in schemas   EDIT  To clarify  I guess I m looking for the benefits of schemas   If you re only going to use it as a security scheme  it seems like database roles already filled that   er   um   role   And using it a as a namespace specifier seems to have been something you could have done with ownership  dbo versus user  etc      I guess what I m getting at is  what do Schemas do that you couldn t do with owners and roles   What are their specifc benefits

User · Answer

At an ORACLE shop I worked at for many years, schemas were used to encapsulate procedures (and packages) that applied to different front-end applications. A different 'API' schema for each application often made sense as the use cases, users, and system requirements were quite different. For example, one 'API' schema was for a development/configuration application only to be used by developers. Another 'API' schema was for accessing the client data via views and procedures (searches). Another 'API' schema encapsulated code that was used for synchronizing development/configuration and client data with an application that had it's own database. Some of these 'API' schemas, under the covers, would still share common procedures and functions with eachother (via other 'COMMON' schemas) where it made sense.

I will say that not having a schema is probably not the end of the world, though it can be very helpful. Really, it is the lack of packages in SQL Server that really creates problems in my mind... but that is a different topic.

User · Answer

Here a good implementation example of using schemas with SQL Server  We had several ms-access applications  We wanted to convert those to a ASP NET App portal  Every ms-access application is written as an App for that portal  Every ms-access application has its own database tables  Some of those are related  we put those in the common dbo schema of SQL Server  The rest gets its own schemas  That way if we want to know what tables belong to an App on the ASP NET app portal that can easily be navigated  visualised and maintained

User · Answer

In SQL Server 2000  objects created were linked to that particular user  like if a user  say  Sam creates an object  say  Employees  that table would appear like  Sam Employees  What  about if Sam is leaving the compnay or moves to so other business area  As soon you delete  the user Sam   what would happen to Sam Employees table  Probably  you would have to change  the ownership first from Sam Employees to dbo Employess  Schema provides a solution to  overcome this problem  Sam can create all his object within a schemam such as Emp Schema  Now  if he creates an object Employees within Emp Schema then the object would be  referred to as Emp Schema Employees  Even if the user account Sam needs to be deleted  the  schema would not be affected

User · Answer

If you keep your schema discrete then you can scale an application by deploying a given schema to a new DB server    This assumes you have an application or system which is big enough to have distinct functionality    An example  consider a system that performs logging   All logging tables and SPs are in the  logging  schema   Logging is a good example because it is rare  if ever  that other functionality in the system would overlap  that is join to  objects in the logging schema   A hint for using this technique -- have a different connection string for each schema in your application   system   Then you deploy the schema elements to a new server and change your connection string when you need to scale

User · Answer

Just like Namespace of C  codes

User · Answer

They can also provide a kind of naming collision protection for plugin data   For example  the new Change Data Capture feature in SQL Server 2008 puts the tables it uses in a separate cdc schema   This way  they don t have to worry about a naming conflict between a CDC table and a real table used in the database  and for that matter can deliberately shadow the names of the real tables

User · Answer

I think schemas are like a lot of new features  whether to SQL Server or any other software tool   You need to carefully evaluate whether the benefit of adding it to your development kit offsets the loss of simplicity in design and implementation   It looks to me like schemas are roughly equivalent to optional namespaces  If you re in a situation where object names are colliding and the granularity of permissions is not fine enough  here s a tool   I d be inclined to say there might be design issues that should be dealt with at a more fundamental level first    The problem can be that  if it s there  some developers will start casually using it for short-term benefit  and once it s in there it can become kudzu

User · Answer

development - each of our devs get their own schema as a sandbox to play in

User · Answer

I don t see the benefit in aliasing out users tied to Schemas  Here is why      Most people connect their user accounts to databases via roles initially  As soon as you assign a user to either the sysadmin  or the database role db owner  in any form  that account is either aliased to the  dbo  user account  or has full permissions on a database  Once that occurs  no matter how you assign yourself to a scheme beyond your default schema  which has the same name as your user account   those dbo rights are assigned to those object you create under your user and schema  Its kinda pointless     and just a namespace and confuses true ownership on those objects  Its poor design if you ask me    whomever designed it   What they should have done is created  Groups   and thrown out schemas and role and just allow you to tier groups of groups in any combination you like  then at each tier tell the system if permissions are inherited  denied  or overwritten with custom ones  This would have been so much more intuitive and allowed DBA s to better control who the real owners are on those objects  Right now its implied in most cases the dbo default SQL Server user has those rights    not the user

User · Answer

Schemas logically group tables  procedures  views together  All employee-related objects in the employee schema  etc   You can also give permissions to just one schema  so that users can only see the schema they have access to and nothing else

User · Answer

I tend to agree with Brent on this one     see this discussion here  http   www brentozar com archive 2010 05 why-use-schemas   In short    schemas aren t terribly useful except for very specific use cases  Makes things messy  Do not use them if you can help it  And try to obey the K eep  I t  S imple  S tupid  rule

User · Answer

I know it s an old thread  but I just looked into schemas myself and think the following could be another good candidate for schema usage   In a Datawarehouse  with data coming from different sources  you can use a different schema for each source  and then e g  control access based on the schemas  Also avoids the possible naming collisions between the various source  as another poster replied above

[sql-server-2005] What good are SQL Server schemas?

Examples related to sql-server-2005

Examples related to schema

Examples related to theory