Thursday 17 May 2012

Database SCHEMA


From SQL Server 2005 the DATABASE SCHEMA is introduced by Microsoft. Before understanding the database schema we must review the SQL Server 2000 to understand it properly.

Problem with earlier version of SQL Server (Before SQL Server 2005)

In SQL Server 2000 the schema is owned by, and was inextricably linked to that means a user creates a table in the database, that user cannot be deleted without deleting the table or first transferring it to another user.

How Microsoft helps us to solving this problem

To solve this problem Microsoft is introducing database schema from SQL Server 2005.
A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects. We can assign user login permissions to a single schema so that the user can only access the objects they are authorized to access. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

Default Schema
Users can be defined with a default schema. The default schema is the first schema that is searched when it resolves the names of objects it references.
The default schema for a user can be defined by using the DEFAULT_SCHEMA option of CREATE USER or ALTER USER. If no default schema is defined for a user account, SQL Server will assume "dbo" is the default schema. It is important note that if the user is authenticated by SQL Server as a member of a group in the Windows operating system, no default schema will be associated with the user. If the user creates an object, a new schema will be created and named the same as the user, and the object will be associated with that user schema.
Some properties of database schema
     1.     Ownership of schema and schema-scoped securable is transferable.
  1. Objects can be moved between schemas.
  2. A single schema can contain objects owned by multiple database users.
  3. Multiple database users can share a single default schema.
  4. Permissions on schemas and schema-contained securable can be managed with greater precision than in earlier releases.
  5. A schema can be owned by any database principal. This includes roles and application roles.
  6. A database user can be dropped without dropping objects in a corresponding schema.
To get the Information of Schema for database objects

SELECT  sys.objects.name [Object Name],
        sys.schemas.name AS [Schema Name]
FROM    sys.objects
        INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
Object Name                Schema Name
sysrscols                      sys
sysrowsets                   sys
Tbl_1                            dbo
Tbl_2                            dbo


The Example of Database Schema

USE master
GO
CREATE DATABASE my_db
GO
USE my_db
GO
-- Created Schema my_Employee
CREATE SCHEMA my_Employee
GO
-- Created table named in EmpInfo on the my_Employee schema –
CREATE TABLE my_Employee.EmpInfo
    (
      EmpNo int Primary Key identity(1,1),
      EmpName varchar(20)
    )

-- Data insertion

INSERT INTO my_Employee.Empinfo
Values ('Joydeep'),('Tuhin'),('Sangram')

-- Data Selection
SELECT *
FROM   my_Employee.Empinfo

-- Created another schema HR_Dept
CREATE SCHEMA HR_Dept

-- Transfer Objects between Schemas
ALTER SCHEMA HR_Dept
TRANSFER my_Employee.Empinfo

-- Assigning Permission to Schema
GRANT SELECT ON SCHEMA::HR_Dept TO Joydeep


  


Hope you like it.


Posted by: MR. JOYDEEP DAS

2 comments:

  1. The concept of a database schema has existed within SQL Server since at least SQL Server 6.0. It may have existed in SQL Server 4.21... but it's been a long time, I honestly don't recall.

    What's special about the schema in SQL Server 2005 is decoupling users and schemas.

    ReplyDelete