What good are SQL Server schemas?

Mystere_Man_61164 0
Viewed 125960

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?

theory sql-server-2005 schema
12 Answers

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_7400

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.

Joel_Coehoorn_30430

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.

user_5444170

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_11283360

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.

doofledorfer_316410

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

user_304700

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_21113130
Related