Sql Schema

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
Labels: