September 17, 2010 at 12:10 am
what is sqlschema in sqlserver?
September 17, 2010 at 12:30 am
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
September 17, 2010 at 2:59 am
Schema is container of database objects aka Securables like Entity,View, Stored procedure etc. It is similar to namespace in programming languages. It can also be correlated with a bank locker, where you can put all your securables like Jewellery, Costly Items etc.
Eg. Person.Address entity. Here Person refers to Schema and Address refers to entity.
A Schema can have single owner at a time. Ownership of Schema can be transferred to another user using Authorization. Schema and user are differentiated from SQL Server 2005 onwards. A single schema can contain objects of more than one user. A user can have ownership of more than one schema. Securables can be moved across schemas. A user can have one default schema associated with him.
Advantages of Schema:
* Brings lot of logical grouping to the database objects.
* More Structured way to manage the Permissions of database objects
September 17, 2010 at 3:39 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply