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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: