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
In part one, we learned the basics of data, database, database management system, and types of DBMS and SQL.
- 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 represent the design of a database at Physical Level, i.e., how the data stored in blocks of storage is described.
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 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,
CREATE SCHEMA Schema_Name
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.”
We can drop a created schema as similar as dropping a database or a table.
DROP SCHEMA Schema_Name
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.
CREATE TABLE SCHEMA_NAME.TABLE_NAME (COLUMN_1 DATATYPE,COLUMN_2 DATATYPE);
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.
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.