As a database beginner, you must be able to organize, manage and secure database objects; in this tutorial, I will show how to CREATE SCHEMA in SQL Server.
You can put the schema-related database objects in by creating a schema. First, I will explain what schema is and why it is created. Then, with syntax, you will learn how to create it in your current data.
Through an example, you will see how to create a different schema and then learn how to view the schemas in SSMS and through a query.
You will understand how to create different types of objects in a specific schema and how to access the objects in a specific schema.
CREATE SCHEMA in SQL Server
First, let’s understand ‘What is Schema?’ A schema is like a container that holds different kinds of database objects. Objects means views, tables, functions, stored procedures, etc.
Why schema? Because it helps organize these objects into logical groups, allowing us to manage and secure them more easily.
The syntax is given below.
CREATE SCHEMA schema_name [AUTHORIZATION owner_name]
Where,
- CREATE SCHEMA: It is the command to create a new schema.
- schema_name: Name of the schema that you want to create.
- AUTHORIZATION owner_name: After the keyword, specify the owner of the schema; if not specified, then it assigns the owner who executes the current statement.
Let’s take an example and see how to create a schema.
Suppose an e-commerce platform requires a database to store information related to products, orders, customers, and shipping if you store all of this information without schema or in one place without schema. So managing these objects will become messy.
Using the CREATE SCHEMA command, you can group the objects logically, like having separate schemas for Sales, Inventory, and UserManagement, improving manageability and clarity.
Run the query below to create a separate schema: Sales, Inventory, and UserManagement.
CREATE SCHEMA Sales AUTHORIZATION james;
CREATE SCHEMA Inventory;
CREATE SCHEMA UserManagement;
Executing the above query creates three schemas in the current database ‘ecommerce’, as you can see in the above output. The sales schema was created with owner James, and the Inventory and UserManagement schema was created without specifying the owner.
But they were created in the same database. If you want to access the created schemas through SSMS, go to Object Explorer, expand the database, for example, e-commerce, expand the Security folder, and finally expand the Schemas folder, where you will see all the schemas.
- Databasename–> Security–> Schemas
You can also view all the schemas in the current database using the query below.
SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA;
Look at the three newly created schemas: Sales, Inventory, and UserManagement. This is how to create schema in SQL Server.
Next, let’s see how to create objects in specific schemas. For that, you can follow the below syntax.
To create a table in the specific schema, use the syntax below.
CREATE TABLE schema_name.table_name();
So, as you can see, to create a table in a schema, you need to add the (schema_name.) before the table name.
For example, you can run the query below to create a SaleData table in the Sales schema.
CREATE TABLE Sales.SaleData (
SaleID INT,
SaleDate DateTime
);
When you execute the above query, it creates a new table, SaleData, under schema Sales.
Here, you need to specify the schema_name as a prefix to the table name. Remember to specify schema_name.table_name.
You can follow the syntax below to create a view in specific schemas.
CREATE VIEW SchemaName.ViewName AS
SELECT Column1, Column2, ...
FROM TableName;
For example, create a view SaleInfo in a specific schema called Sales, as shown in the query below.
CREATE VIEW Sales.SaleInfo AS
SELECT SaleID, SaleDate
FROM SaleData;
When you execute the above query, a new view named SaleInfo is created in the Sales schema.
This is how to create a view in a specific schema in SQL Server.
Using the syntax below, you can also create a stored procedure in a specific schema.
CREATE PROCEDURE SchemaName.ProcedureName
@Parameter1 DataType,
@Parameter2 DataType,
...
AS
BEGIN
-- SQL statements to execute
END;
Here, you also need to prefix the schema_name before the name of the store procedure, as you can see in the above syntax.
To retrieve the sale info, you can create a stored procedure based on the SaleID value.
CREATE STORED Sales.GetSaleIDDateTime (INT sale_id)
AS
BEGIN
SELECT SaleID, SaleDate FOMR Sales.SaleData
WHERE SaleID = sale_id;
END;
After executing the above query, it creates a new stored procedure named GetSaleIDDateTime in a schema Sales.
Next, using the given syntax, you can create a function in a particular schema.
CREATE FUNCTION SchemaName.FunctionName (@Parameter1 DataType, @Parameter2 DataType, ...)
RETURNS ReturnType
AS
BEGIN
-- Function logic
RETURN (@ReturnValue)
END;
After the keyword CREATE FUNCTION, specify the SchemaName in which you want to create a function, followed by FunctionName. Which means you need to prefix the function name with schema_name.
For example, you can create a function that returns the count of the SaleIDs.
CREATE FUNCTION Sales.GetSaleIDCount()
RETURNS INT
AS
BEGIN
DECLARE @Count INT;
SELECT @Count = COUNT(*) FROM Sales.SaleData;
RETURNS @Count;
END;
Here, before the name of GetSaleIDCount, the schema Sales is specified as a prefix to that function name.
Also, remember to access any objects in a specific schema, you need to prefix the object’s name with the schema name. Otherwise, it doesn’t find that schema and returns the error.
For example, the SaleData table is created in the schema Sales; executing the query below will get an error.
SELECT * FROM SaleData;
Look, it shows the error ‘Invalid object name ‘SaleData’ because it doesn’t find that table or object because that object exists in the schema Sales; in the query, the table name is not prefixed with the schema like Sales.SaleData.
Let’s fix the above query and rerun it.
SELECT * FROM Sales.SaleData;
This time, an empty table is returned because this table exists in schema Sales. Currently, the table is empty, so you see only the two columns, SaleID and SaleData.
You can access the views, functions, and stored procedures; before calling these objects, prefix them with the schema where these objects exist.
Remember, to create any objects within the schema, a user must have permission.
While creating a schema, choose an appropriate name that indicates the purpose of the objects contained within.
Use the schemes to enforce access control and secure sensitive data.
Using schema provides a clean structure, allowing us to manage and secure the data or objects easily.
Conclusion
You learned how to create schema in SQL Server using the CREATE SCHEMA statement. You also learned to view the created schema using the SQL Server Management Studio and through a query.
Afterwards, you created objects such as table, view, function and stored procedure in a specific schema.
Finally, you learned how to access the objects in a schema by prefixing the object’s name with the schema name.
You may like to read:
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.