madhubaabu@yahoo.com

A schema is nothing more than a named, logical container in which you can create database objects. A new schema is created using the CREATE SCHEMA DDL statement. Beginning in SQL Server 2005, each object belongs to a database schema. A database schema is a distinct namespace that is separate from a database user. 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.

SQL Server 2005 has given us the ability to create schemas independent of the user, and by this we achieve separation of schemas and users. This gives us some considerable advantages:

1. The first benefit is the ability to subdivide your database into logic of interest, rather as the Erwin modeling tool lets you create subject areas.
2. You can provide better security at Schema level.
3. Dropping database users is greatly simplified; dropping a database user does not require the renaming of objects contained by that user’s schema. Thus it is no longer necessary to revise and test applications that refer explicitly to schema-contained objects after dropping the user that created them
4. Multiple users can share a single default schema for uniform name resolution.
5. Shared default schemas allow developers to store shared objects in a schema created specifically for a specific application, rather than in the DBO schema
6. Permissions on schemas and schema-contained objects can be managed with a higher degree of granularity than in earlier releases.
7. Fully qualified name of object in SQL Server 2000 and earlier looks like
   [Server].[DatabaseName].[OwnerName].[ObjectName]
8. SQL Server 2005’s fully qualified name for object looks like below
   [Server].[DatabaseName].[Schema].[ObjectName]
9. You can able to transfer Schema ownership

Creating schema
CREATE SCHEMA Marketing
GO

Adding table object

CREATE TABLE Marketing.Leads

(
    LeadId INT,
    LeadDescription NVARCHAR(50)
)
GO

Accessing table object

SELECT * FROM Marketing.Leads
GO

After you have created schemas, your database looks like below