SQL Server 2005 Schema, Schema Properties, Schema T-SQL

What is Schema in SQL Server 2005? Explain its properties with example?

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.

Properties

• Ownership of schemas and schema-scoped securables is transferable.


• Objects can be moved between schemas


• A single schema can contain objects owned by multiple database users.


• Multiple database users can share a single default schema.


• Permissions on schemas and schema-contained securables can be managed with greater precision than in earlier releases.


• A schema can be owned by any database principal. This includes roles and application roles.


• A database user can be dropped without dropping objects in a corresponding schema.

 

 

Create database SQL2k5

Use SQL2k5

 

-- Created Schema Employee --

Create Schema Employee

 

-- Created table in Employee schema --

Create Table Employee.EmpInfo

(

EmpNo int Primary Key identity(1,1),

EmpName varchar(20)

)

 

-- data insertion --

 

Insert Into Employee.Empinfo Values('Jshah-3')

 

-- Data Selection --

Select * From Employee.Empinfo

 

-- Created another schema HR --

Create Schema HR

 

-- Transfer Objects between Schemas --

ALTER SCHEMA HR

TRANSFER Employee.Empinfo

 

-- Assigning Permission to Schema --

GRANT SELECT ON SCHEMA::HR TO Jshah