SQL Standard

SQL Server Create Schema

In SQL Server, a schema refers to a collection of database objects (such as tables, views, and stored procedures) which are grouped based on their function. Think of a schema as a logical container that stores the related database objects, allowing you to view and manage them as a single unit.

Each schema in an SQL Server database has a unique name, and each database object belongs to a single schema. This means that you can have multiple objects in a database with the same name as long as they belong to different schemas.

The purpose of this tutorial is to guide you through various methods and techniques to initialize a new database schema in SQL Server.

By default, SQL Server provides schemas such as dbo, sys, and INFORMATION_SCHEMA. Once you create a new database, it uses the dbo schema by default. Similarly, creating a new user in SQL Server, the user takes the dbo as the default schema.

NOTE: The system protects the sys and INFORMATION_SCHEMA schemas. The SQL Server prevents you from directly modifying the values in the schemas.

SQL Server CREATE SCHEMA Statement

We can use the CREATE SCHEMA T-SQL command to create a new schema in SQL Server.


 CREATE SCHEMA schema_name_clause [  [ ...n ] ]  
    | AUTHORIZATION owner_name  
    | schema_name AUTHORIZATION owner_name  

In this case, we start by setting the schema name followed by the owner of the schema using the AUTHORIZATION keyword.

NOTE: Schema creation is an atomic operation. This means that the clause fails without affecting the database if an error is encountered in the statement.

SQL Server Create Schema – Transact-SQL

Here is an example demonstration of schema creation in SQL Server using the CREATE SCHEMA clause:

CREATE schema local_db_schema;

SQL Server Create Schema – SSMS

You can also create a schema using the SQL Server Management Studio. First, open the Object Explorer and locate the database on which you wish to create a new schema.

Next, expand the database values and navigate to Security -> Schemas.

Right-click the Schemas folder and select “New Schema.”

This launches a schema creation window. Set the name of the schema and select the owner. You can choose an existing user in the server or use the default dbo user.

Once you select the schema owner, click OK to initialize a new schema. You should see the created schema in the list as follows:

Once you create a new schema, you can create new objects in it. For example, to add a table to the local_db_schema, run the following command:

CREATE TABLE local_db.local_db_schema.databases(
    server_name VARCHAR(50),
    server_address VARCHAR(255) NOT NULL,
    compression_method VARCHAR(100) DEFAULT 'none',
    size_on_disk FLOAT NOT NULL,
    size_compressed FLOAT,
    total_records INT NOT NULL,
    init_date DATE

The previous query creates a table called databases in the local_db_schema schema of the local_db database.


This post covered the fundamentals of working with the CREATE SCHEMA statement in SQL Server. This allows you to initialize new schemas in a given database.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list