Understanding the Importance of Database Schema in SQL Server

A schema is a container for objects such as tables, views, stored procedures, functions, etc., to facilitate management and ownership of a database object. In Microsoft SQL Server, the default schema is dbo, wherein any object created will be referenced to a dbo schema. There can be more than one schema created for a database in SQL Server. When a database is created, by default we will get to see the schemas as shown in the screenshot.

Let’s take a look at the example for differentiating the default schema and the created schema.

CASE 1: Creation of default dbo schema

Say, if we are creating a database with the name SQLArena and create an object say SQLArena_Test, then the object will reference to a dbo schema by default.

Create database [SQLArena]

Create table SQLArena_Test(id int null)

From the created table, the id is the value which will be referenced to the dbo schema by default

CASE 2: Create a new database schema (Schema_Name: Trash)

Say, if we are creating a new database schema in already created database – SQLArena, then we will have to create the table with the prefix as the <Schema_Name> along with the object name to reference to the respective schema created. We can create the same table SQLArena_Test with a new database schema as below.

Create table <Schema_Name>.SQLArena_Test(id int null)

Example:

Create table Trash.SQLArena_Test(id int null)

From the above two cases, don’t be confused while fetching the column of the same table. Now, while fetching for the ‘id’ column in SQLArena database, by default the ‘dbo’ schema will be fetched rather than the ‘Trash’ schema.

GUI to guide you through creating a new schema.

1. Expand the respective database from the object explorer.

2. Expand “Security” under the selected database.