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?

Holds

Answer 1

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.

Aspirant

The possibility to assign permissions to a schema makes it worth it from an administration perspective. – Hyperbola

Couldn't you use a separate database? – Grumous

This schema permissioning sounds good on paper... but rarely ever used properly. To me it's not worth the trouble. – Grumous

@samyi what if you're restricted to the number of databases that you can have (like using a cloud sql server hosting service...AppHarbor for example)? – Matusow

But if that's the case, couldn't you achieve the same using naming conventions? I'm not suggesting there NOT a use case for it; it is, more often than not, an addition by subtraction. – Grumous

dba.stackexchange.com/questions/11101/… – Chilblain

Why wouldn't you use a separate database for that? Is there anything you can accomplish with a schema that you can't do with a database? – Krantz

@Ajedi32, yeah... with schemas you can get a single atomic commit in a single transaction log and back all of your data up in one shot versus having two databases that are not in sync as well as fighting distributed transactions. – Eyla

Worth mentioning that schemas are also naming containers. Same object name in two different schemas are two distinct objects with no collision. – Austronesia

Answer 2

Just like Namespace of C# codes.

Undertenant

Unfortunately however, schemas and .NET namespaces don't play very well together from an ORM perspective (namely Entity Framework). Tables in the MyDatabase.MySchema schema don't magically map to entity classes in the MyProject.MyDatabase.MySchema namespace. It's also worth noting that any dot-notation (.) hacks in the table names will end up as underscores (_) in the class names. Just food for unfortunate thought. – Noella

Good analogy for teaching. I don't see it to be taken 100% literally... (those caveats sound minor in practice) – Northman

Personally, I wish they were more like .NET namespaces, such that one could nest an arbitrary number of schemas (as you can with namespaces) for organizational purposes. That, and I wish they would map better in EF. – Noella

They are not like namespaces in any language I can think of. – Austronesia

Answer 3

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.

Squamulose

Answer 4

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.

Obturate

Answer 5

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.

Aquilar

Answer 6

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.

Dorathydorca

With Oracle, since 1 instance = 1 database = 1 licence to pay, people use shemas to avoid creating another db and paying for another licence. In SQl Server, 1 server can handle plenty of databases. – Haiti

Answer 7

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.

Grumous

I don't think that Brent is arguing that "schemas are bad", just that using non-default schemas is far more complex than just using the default schema. The rest of your summation is accurate. – Isolating

"If [schemas are] used right, they let you segregate permissions by groups of objects." ~ brentozar.com/archive/2010/05/why-use-schemas – Dorathydorca

the post has it backwards, naming conventions are schemas with 💄 – Swashbuckling

Answer 8

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.

Guyenne

Answer 9

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.

Argilliferous

Answer 10

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.

Wage

This is no longer true since there have been two new versions since 2000 – Aquilar

Answer 11

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

Ferine

-1 It's better to give devs seperate whole copies of the database to play with on their own machine. Otherwise they could only safely change what is within their schema. It complicates promotion to live, and also complicates schema seperation for other reasons described by other answers. – Acheron

I can't speak for the application you're working on ... but your development should mirror production as much as possible. Having schemas in dev and not in prod can be problematic. – Grumous

Answer 12

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.

Paralyse