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.
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.
- 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 securable 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.
To get the Information of Schema for database objects
SELECT sys.objects.name [Object Name],
sys.schemas.name AS [Schema Name]
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
Object Name Schema Name
The Example of Database Schema
CREATE DATABASE my_db
-- Created Schema my_Employee
CREATE SCHEMA my_Employee
-- Created table named in EmpInfo on the my_Employee schema –
CREATE TABLE my_Employee.EmpInfo
EmpNo int Primary Key identity(1,1),
-- Data insertion
INSERT INTO my_Employee.Empinfo
-- Data Selection
-- Created another schema HR_Dept
CREATE SCHEMA HR_Dept
-- Transfer Objects between Schemas
ALTER SCHEMA HR_Dept
-- Assigning Permission to Schema
GRANT SELECT ON SCHEMA::HR_Dept TO Joydeep
Hope you like it.
Posted by: MR. JOYDEEP DAS