Rajendra Gupta
Table properties

A Walkthrough of SQL Schema

October 9, 2019 by

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 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, database objects 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:

Default schema of a user

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:

Verify default SQL Schema for a current database user .

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:

In the following screenshot, we can see master database schema and their owners:

Retrieve all schema and their owners in a database

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:

Retrieve all schema and their owners in a database

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:

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:

CREATE TABLE statement

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:

Create a new login

Provide the following inputs on the general page:

  1. Enter a SQL user name
  2. Choose SQL Server authentication method and enter a password for the user

Provide the name, password of a user

Navigate to the User Mapping page and put a check on the AdventureWorks database:

Choose a database and its permissions

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:

Map the schema

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]:

Verify the user and SQL schema mapping

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]:

Create a new table without specifying schema name

Alternatively, we can run the script by specifying a schema name.

Script to create a table in [Person] schema:

Script to create a table in the dbo schema:

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:

View schema in SSMS

Right-click on Schemas and click to New Schema:

New Schema

Specify a new schema name and schema owner. You can search the schema owner from existing logins:

Schema name and owner

Instead of creating the schema, click on generate scripts to give you an equivalent T-SQL statement:

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:

Verify new schema

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:

  1. Right-click on the specific table name and choose Design option:

    Click on Design menu

  2. 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:

    Table designer window

    It opens the table properties. It shows the table schema, server name, identity column, lock escalation, and filegroup information:

    Table properties

  3. Click on SQL Schema, and it opens the available scheme in the database:

    Select the required schema

  4. 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

  5. Click on Yes to proceed:

    Warning

  6. Close the table designer and save the changes after clicking on Yes:

    Save the changes

  7. Refresh the tables in the database, and we can see the schema of this table changes from dbo to sales:

    Verify new schema of the table

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:

Error = Invalid object name

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:

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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
486 Views