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
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
select * from sys.schemas
Now let me show you how you can create your own schema in your database to maintain your objects.
CREATE SCHEMA Testing
--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
--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
--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