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.

2. Objects can be moved between schemas.
3. A single schema can contain objects owned by multiple database users.
4. Multiple database users can share a single default schema.
5. Permissions on schemas and schema-contained securable can be managed with greater precision than in earlier releases.
6. A schema can be owned by any database principal. This includes roles and application roles.
7. 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