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.
Syntax:
{
schema_name
| 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:
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:
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
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.
Conclusion
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.
