This article gives an overview of SQL Schema and its usage in SQL Server.
Introduction to Schema
A SQL database contains multiple objects such as tables, views, stored procedures, functions, indexes, triggers. We define SQL Schema as a logical collection of database objects. A user owns that owns the schema is known as schema owner. It is a useful mechanism to segregate database objects for different applications, access rights, managing the security administration of databases. We do not have any restrictions on the number of objects in a schema.
Starting from SQL Server 2005, we have different meanings of user and schema. Now, the database object owner is a schema, and we define schema owners. We can have a single or multiple schema owners. It provides the following benefits:
- We can quickly transfer ownership of a SQL schema to another user
- We can share a schema among multiple users
- It allows you to move database objects among the schemas
- We get more control over database objects access and security
We define default SQL Schema for a database user in the create database user window:
If we do not define any default schema for a user, SQL Server assumes dbo as the default schema. We can verify the default schema for a user using the following system function:
|
1 |
SELECT SCHEMA_NAME(); |
.
Retrieve all schema and their owners in a database
We can query sys.schemas system table to find out schema in a database and their owners:
|
1 2 3 4 5 6 |
SELECT s.name AS schema_name, s.schema_id, u.name AS schema_owner FROM sys.schemas s INNER JOIN sys.sysusers u ON u.uid = s.principal_id ORDER BY s.name; |
In the following screenshot, we can see master database schema and their owners:
SQL Schema is a database entity, and you might get a different result of the query in a user database. For example, the AdventureWorks database shows the following schema information:
Note: SQL Server does not allow users to create objects in sys and INFORMATION_SCHEMA. It is used for storing internal system objects.
Let’s create a new table in the AdventureWorks database using the following query:
|
1 2 3 4 |
CREATE TABLE DemoSchema (ID INT IDENTITY(1, 1), Name VARCHAR(20) ); |
We did not specify any schema in the CREATE TABLE statement. It automatically uses dbo schema for the table because the current user default schema is dbo:
Specify default SQL schema while creating a new login
Let’s create a new SQL login and database user with a default schema other than dbo.
Go to Security and create a new login:
Provide the following inputs on the general page:
- Enter a SQL user name
- Choose SQL Server authentication method and enter a password for the user
Navigate to the User Mapping page and put a check on the AdventureWorks database:
Click on eclipse for default schema and browse available schema in the database. Let’s select schema [Person] as a default schema for this user:
Click OK and return to the user-mapping page. We can see that the new login is having access to the AdventureWorks database and its default schema is [Person]:
Click OK, and it creates the SQL login, database user in the AdventureWorks database. Connect to SQL instance with this login. Execute the create table statement without specifying a SQL Schema. We can see it creates the table in the default schema for the user, i.e. [Person]:
Alternatively, we can run the script by specifying a schema name.
Script to create a table in [Person] schema:
|
1 2 3 4 |
CREATE TABLE [person].[DemoSchema] (ID INT IDENTITY(1, 1), Name VARCHAR(20) ); |
Script to create a table in the dbo schema:
|
1 2 3 4 |
CREATE TABLE [dbo].[DemoSchema] (ID INT IDENTITY(1, 1), Name VARCHAR(20) ); |
Create a new SQL Schema in SQL Server
We can create schemas as per our requirements. We can create a schema in a specific database. Let’s create a new schema in the AdventureWorks database.
Expand the AdventureWorks database and go to Security:
Right-click on Schemas and click to New Schema:
Specify a new schema name and schema owner. You can search the schema owner from existing logins:
Instead of creating the schema, click on generate scripts to give you an equivalent T-SQL statement:
|
1 2 3 4 |
USE [AdventureWorks] GO CREATE SCHEMA [Customer] AUTHORIZATION [Demouser] GO |
In this syntax, we can see that:
- [Customer] is the new schema name that we want to create in the AdventureWorks database
- We specify a schema owner using the AUTHORIZATION keyword in the CREATE SCHEMA command
Execute the command and refresh the schema list (right-click on schemas and refresh). You can see a new schema [Customer] on this list:
Note: Earlier, we created a demo user with a default schema [Person]. The demo user is the schema owner of the customer schema as well. If we connect to SQL instance using the demo user and create objects without specifying a schema name, it still takes default schema as [Person].
Change SQL schema of an existing object in SQL Server
We can transfer objects to different schemas, as well. For example, let’s migrate a table from [dbo] owner to sales schema in the AdventureWorks database following these steps:
-
Right-click on the specific table name and choose Design option:
-
It opens the table designer. We can change table properties such as column data types, default values, schema using this designer. Click on Properties as shown in the following image:
It opens the table properties. It shows the table schema, server name, identity column, lock escalation, and filegroup information:
-
Click on SQL Schema, and it opens the available scheme in the database:
-
Select the required schema [Sales], and it gives the warning:
Changing the schema of this object will result in all current permissions on this object being dropped
-
Click on Yes to proceed:
-
Close the table designer and save the changes after clicking on Yes:
-
Refresh the tables in the database, and we can see the schema of this table changes from dbo to sales:
By default, SQL Server searches for the object in the default schema and dbo schema. If the object belongs to other than the default and dbo schema, we require to specify schema name while accessing the object. You get an error message if the object does not exist in the default or dbo schema:
We can specify a schema name in the following format to access an object.
In this command, [sales] is a schema and [Demoschema] is a table:
|
1 2 |
SELECT * FROM sales.DemoSchema; |
Conclusion
A SQL schema is a useful database concept. It helps us to create a logical grouping of objects such as tables, stored procedures, and functions.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023