SCHEMAS in SQL Server -MS SQL Server – Zero to Hero Query Master – Part 3

Introduction

This is part 3 of “MS SQL Server- Zero to Hero” and in this article, we will be discussing about the SCHEMAS in SQL SERVER. Before getting into this article, please consider to visit previous articles in this series from below,

• MS SQL Server – Zero to Hero Query Master – Part 1
• MS SQL Server – Zero to Hero Query Master – Part 2

A glimpse of previous articles

Part 1

In part one, we learned the basics of data, database, database management system, and types of DBMS and SQL.

Part 2

• We learned to create a database and maintain it using SQL statements.
• Best practice methods were also mentioned.

What is SCHEMA?.

In simple terms, Schemas are like folders within a database and are mainly used to group logical objects together. It is a way of categorizing the objects like Tables, Stored Procedures, Views, Functions, Triggers, Indexes in a database. It defines how the data is organized and how the relations among them are associated. The SCHEMA concept is introduced in SQL SERVER 2005 by Microsoft.

A schema always belongs to one particular database, and the schema is associated with a usernames, which is known as SCHEMA OWNER. A database may have one or more SCHEMAS.

Predefined SCHEMAS in SQL SERVER

SQL Server provides predefined SCHEMAS for newly created tables, which is dbo. The dbo SCHEMA is owned by dbo user account. First thing first, what’s dbo stands for?. dbo acronym standing for Database Owner.

When you create a new table without specifying a SCHEMA, the table will dbo as its default schema.

Types of Database SCHEMAS

• Physical Schema
• Logical Schema
• View Schema

Physical Schema

Physical Schema represent the design of a database at Physical Level, i.e., how the data stored in blocks of storage is described.

Logical Schema

Logical Schema represent the design of a database at Logical Level, i.e., data can be described as certain types of data records get stored in data structures. programmer and Database administrators works at this level.

View Schema

View Schema represents the design of database at View Level. i.e., this is generally describing end user interaction with database system.

Benefits of SQL SCHEMAS

• A SCHEMA can be very efficient object projection tool. A Database Administrator can maintain control access to an object.
• As stated before, SCHEMA will be efficient way to manage the logical group of database objects within a database.
• Since we group the objects, it will be easy to maintain the database.
• The ownership of a schema is transferable and hence the database objects can be moved among schemas.
• A SCHEMA can be shared among multiple users.

These are the some of the main benefits of using SCHEMAS in Databases.

List all SCHEMAS in Current Database

First method to see available SCHEMAS in a particular database in SSMS is by viewing under the SECURITY->SCHEMAS. There it will list all the SCHEMAS in that database.

The second method is listing using T-SQL, we can get the complete list from the table sys.schemas.

SELECT *  FROM sys.schemas 
ORDER BY NAME ASC 

When the above statement is ran is query window, we get the list of schemas as below,

The principal_id result column holds the Schema user Id. When we Join with sys.sysusers table, we can get the Schema Owner Name.

SELECT 
    SS.name AS SchemaName, 
    SU.name AS SchemaOwner
FROM 
    sys.schemas AS SS 
INNER JOIN sys.sysusers AS SU ON SU.uid = SS.principal_id
ORDER BY SS.name;

When the above T-SQL is ran in the Query window, we will get the Schema Name and corresponding Schema Owner as the result columns as shown,

Create SCHEMA using T-SQL

We can create a SCHEMA using T-SQL statement,

Syntax

CREATE SCHEMA Schema_Name

Example

CREATE SCHEMA TestSchemaName

When we run the above T-SQL Statement in Query window, a new schema will be created and we can view that by using any of the methods mentioned in the previous section.

So, what happens when we try to create a schema which is already exists,?. Let’s try that to, once again if we run the Create Schema T-SQL, we encounter the error as mentioned below,

SQL Server throws an error message as “There is already an object named ‘TestSchemaName’ in the database.”

Drop SCHEMA

We can drop a created schema as similar as dropping a database or a table.

Syntax

DROP SCHEMA Schema_Name 

Example

DROP SCHEMA TestSchemaName

When the above T-SQL statement is ran in query window, the created Schema will be dropped or deleted permanently.

As for now we have not created any objects under the created schema, that’s why we are able to delete the SCHEMA. Lets create association for the SCHEMA, i.e., lets create an object for SCHEMA. Now I am again running the Create SCHEMA T-SQL statement and the schema will be created. Now I am going to create a table as object.

Syntax

CREATE TABLE SCHEMA_NAME.TABLE_NAME (COLUMN_1 DATATYPE,COLUMN_2 DATATYPE);

Example

CREATE TABLE TestSchemaName.TestTable(ID INT, Names NVARCHAR(10))

Now I am drop the schema TestSchemaName. Let’s see what happens once the DROP SCHEMA Statement is ran,

We cannot delete or drop the SCHEMA, since an object is associated with our SCHEMA TestSchemaName, it throws an error message as “Cannot drop schema ‘TestSchemaName’ because it is being referenced by object ‘TestTable’.” It’s clear that we cannot drop a schema when it has associated object(s) in database.

GitHub – The above scripts are in my repo, please feel free to refer those from here.

Conclusion

In this article, I tried to explain about the SCHEMA in SQL SERVER as simple as I could and the work sample T-SQL Statements are in my GitHub repo, please use it if required. In my next article, I will explain the table creation and giving access rights for schemas. Please share your Feedback in the comment section and share this article with your networks.