What are Schemas

Schemas are introduced new in SQL 2005,but do we see it is really a new feature which is not available in SQL 2000 ? Answer would be partially No , in turn the way
objects were referred in a select is made more organised by means of schemas.Lets take in a example to understand this better.

In SQL 2000 , lets assume there exists a User called 'Robert' exists in pubs database and he creates a object named author_report in database 'pubs' so the fully qualified select would be like:

select * from pubs.Robert.author_report

So what happens if Robert leaves the company, we many need to remove his ID from SQL Server and this object owner has to be changed before dropping the user which becomes paint to do everytime and everytime you may need to change your Front End Code also if the object is directly referred

To overcome this there is a new layer called schema is introduced , so now the select statement would be like this.
select * from pubs.Report.author_report

Here "report" is a schema name and not a user name, in turn Robert would be mapped to what is called as Schema.So objects created by Robert would be with
Schema name Report and not user name Robert,so if RObert leaves the company just the user needs to be dropped and no other changes are required which sounds really cool.

So each users will have a default schema mapped,specified when a user is created.By default it is "sys".Schema can be created by using CREATE SCHEMA statement

To Move object from one schema to another, in our case transfer Authors_Report from Report to default schema "sys"
ALTER SCHEMA sys TRANSFER Report.Authors_Report

To Map Database User to a schema other than sys
CREATE USER Robert FOR LOGIN Robert WITH DEFAULT_SCHEMA = Report

System View
sys.schemas - Contains a row for each database schema