Schema in SQL Server

Schema introduced in SQL SERVER 2005, it is basically a container not more than that OR we can say, Schema is used to group objects (such as table, views and stored procedures) logically.

Schema is a collection of objects adhering to the ANSI SQL-92 standard.

All objects within a schema must be uniquely named and a schema must be uniquely named in the database catalog. There is difference between “user” and “schema”:-

·         User do not own objects where as Schema owns objects.

·         Schemas can be created and altered in a database, and users can be granted access to a schema.

·         A schema can be owned by any user, and schema ownership is transferable.

With the help of “CREATE SCHEMA” and “ALTER SCHEMA”, we can create and alter the schema. The default schema for a user can be defined by using the DEFAULT_SCHEMA option of the CREATE USER or ALTER USER commands. If no default schema is defined then SQL SERVER takes, ‘dbo’ as a default schema.   schema_id and principle_id of dbo is 1.

With the help of “DROP” command, we can Removes a schema from the database. Point should be noted, the schema that is being dropped must not contain any objects. If the schema contains objects, the DROP statement fails.

We can get the details of schema in sys.schemas object.

Basically there are two type of schema :- 1) built-in schema.   2) User defined schema.

One side note:- At the time of installation of SQL SERVER, we got few schemas by default. One of the schemas is named sys. (The sys schema contains a list of some of the objects that exist in your database system) and another is called dbo.