Schemas in SQL Server 2005 – Definition of Schema and Use of Schema:

By introducing Schemas’ concept and making User and Schema separate, Microsoft has made drastic change in security model of Microsoft SQL Server 2005. Before Microsoft SQL Server 2005 objects were owned by User or by Schema Object but in Microsoft SQL Server 2005 Microsoft separated Schema and User so now no more objects are owned by user.

Schema is nothing but just one logical name which will going to hold your object. If you are working with many objects than this is the time to wake up and start using powerful feature of SQL Server 2005 “Schemas”. AdventureWorks database which ships with Microsoft SQL Server 2005, is a good example of Schema use.

In AdventureWorks database, you can find many different Schemas like Sales, HumanResource, Person, Production, Purchasing etc. Each schema has meaningful name and it has hold the related object. Like “Sales” schema used to hold all the objects related to sales e.g. “Sales.Customer”.

The schema name is the third part in four part object name. for example

SQLServ.Adventureworks.Sales.Customer

First part in above object is server name, second part is database name, third part is schema name (sales) and last one fourth parts is object name (Customer).

You can view all the schemas available in your database by following query:

--list all the schemas of AdventureWorks database

use adventureworks

select * from sys.schemas

GO

Now let me show you how you can create your own schema in your database to maintain your objects.

--Creating Schema

CREATE SCHEMA Testing

GO

--Creating table which will be hold by schema "Testing"

CREATE TABLE Testing.Test

(

ID INT NOT NULL

)

--Insert data in table with schema name

INSERT INTO Testing.Test

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4

--returning dataset from table with schema name

select * from testing.test

--if you will not give schema name before table name

--you will be greeted with error

--Invalid object name 'test'.

select * from test

After seeing above error, you might think that it would good if I would not created object with schema, I could use object name alone. But let me tell you one thing if you don’t even specify the schema name while creating the object, it falls under default schema which is “DBO”. If you will create all your objects under “DBO” than it would difficult to handle when you will have large DB. So, it is good practice to use schema name always before object name.

Sometime you wish to delete some schema so before doing that you have to transfer all the object from that schema to another schema. How to do that? We will do it by TRANSFER option in ALTER SCHEMA.

--transfering Test object from Testing schema to DBO

ALTER SCHEMA dbo

TRANSFER Testing.Test

GO

--you will not see any error this time

--if you will run below query.

select * from test

--After making sure that your object ("Test") is transferred

--you can drop the schema

DROP SCHEMA Testing

Hope you have enjoyed SCHEMA. Do give your feedback for this article.

Reference: Ritesh Shah