Problem
I have learned a great deal about the basics of SQL Server—how to create tables, views, stored procedures, etc. However, I haven’t investigated database schemas. I understand that a database table or view cannot exist without that object belonging to a schema, but how do schemas get created in the Microsoft SQL Server database management system (DBMS)? How do they get assigned to a table or view when I didn’t specify a schema during the table build? Please explain the basics of SQL Server schemas and their role in a SQL database.
Solution
This tutorial will cover some basics of SQL Server schemas: what they are, how to create a unique user-defined schema, and why. We will also cover some advantages and disadvantages of built-in versus user-created schemas. We will wrap it up by putting theory to practice by creating and altering a schema.
What are SQL Schemas?
A schema is a database object that acts as a container. Schemas logically group related objects into a single container that keeps them separate from other database objects in other containers (schemas). Schemas are database oriented. This allows for the same schema in a different database, and they will work independently.
The diagram below shows a database hierarchy that illustrates the distinction and function of schemas.
A simple breakdown of the flowchart, starting at the top:
- Each SQL Server instance (install) contains one or more databases.
- Each database contains one or more schemas.
- Each schema contains one or more objects, such as tables, views, etc.
Why Use SQL Server Schemas?
As we all know, two database objects cannot have the same name. One of the advantages of SQL Server schemas is the ability to break that rule. Well, kind of. For example, I have a table named employee that contains employee names, addresses, phone numbers, etc. For privacy reasons, I may want to create a view that only pulls the name and phone number from the employee table. In this case, I would be tempted to give my view the same name as the table it is referencing, the employee table. I would do this to know at a glance which table that view is pulling data from. Placing the view and the table in different schemas will allow me to name them the same. We will discuss this practice in more detail in the “Create, Alter, and Drop Schemas” section later.
Additional advantages of using SQL Server schemas include:
- Using the ALTER command, database objects can be moved from one schema to another.
- Ownership of schemas can be easily transferred using the ALTER command.
- A schema can contain multiple objects owned by different users or roles.
- Multiple users can share a common schema.
- Schemas provide an additional layer of security.
- Database users can be dropped without dropping their owned schemas.
- Schemas can be owned by users, roles, application roles, etc.
Above all else, the most significant advantage of SQL Server schemas is their level of security. Schemas can be assigned security permissions that provide database objects with a level of protection based on a user’s privileges.
SQL Server User vs. Owner vs. Database Roles
Before discussing schemas, users, and roles, let’s first clarify the difference between the three.
As mentioned previously, schemas are just containers. Users and roles set the permissions for that container:
- Users are the actual database users; how a person connects to a database.
- Roles are permissions a user may or may not have on a particular database object, such as a schema, table, stored procedure, etc.
- Every object in a database is owned by a user. The user that owns the database is aliased as “dbo”.
- Users are assigned (or are a member of) database roles.
- db_owner is not the same as dbo. dbo is a user, whereas db_owner is a role.
SQL Server Default Schemas
SQL Server installs several built-in logical schemas:
- dbo
- sys
- guest
- INFORMATION_SCHEMA
When creating a new object in SQL, such as tables, views, stored procedures, etc., if you do not specify a schema for that object, it will be placed in the default logical schema for that SQL instance. In most cases, that default is the “dbo” (Database Owner) schema.
You can also change the default schema for all or some of the users in a database, as in the sample T-SQL code below.
USE MyTestDB;
GO
ALTER USER [User1] WITH DEFAULT_SCHEMA = hr;
ALTER USER [User2] WITH DEFAULT_SCHEMA = sales;
ALTER USER [User3] WITH DEFAULT_SCHEMA = guest;
Now, when User1 creates a new database object and doesn’t specify a schema, that object will, by default, be added to the “hr” schema as long as that schema exists.
Create, Alter, and Drop Schemas
Aside from using the default schemas that come standard with a SQL instance install, you can also create your own schemas.
Below is the basic T-SQL syntax for creating a schema.
CREATE SCHEMA <name of the schema> -- create schema statement
Authorization = dbo;
GO
Remember, each schema must have an owner. We will use “dbo” as the schema owner in the code block above and the following examples.
Creating your own unique schemas will allow you to combine what usually would be two or more databases into one. For example, a bowling alley management system may have three different entities: bowling lanes, a restaurant, and a game room. As you can see, this could be three separate databases, one for each department. Using schemas to separate the departments allows us to put them into one database. We can create a set of schemas called bowl, rest, and game.
In this respect, we can list the employees in an employee table, separating the employees from each department. Thus, we will have three new tables for employees in the same database under different schemas. Here’s an example that can be run in SQL Server Management Studio (SSMS):
USE MyTestDB; -- database name
GO
CREATE SCHEMA bowl AUTHORIZATION dbo;
GO
CREATE SCHEMA rest AUTHORIZATION dbo;
GO
CREATE SCHEMA game AUTHORIZATION dbo;
GO
CREATE TABLE bowl.employees ( -- object name
colID INT IDENTITY
, firstName VARCHAR(20)
, lastName VARCHAR(20)
, empPhone VARCHAR(20)
, empSSN VARCHAR(11)
);
GO
CREATE TABLE rest.employees ( -- object name
colID INT IDENTITY
, firstName VARCHAR(20)
, lastName VARCHAR(20)
, empPhone VARCHAR(20)
, empSSN VARCHAR(11)
);
GO
CREATE TABLE game.employees ( -- object name
colID INT IDENTITY
, firstName VARCHAR(20)
, lastName VARCHAR(20)
, empPhone VARCHAR(20)
, empSSN VARCHAR(11)
);
GO
Of course, you can use a SQL JOIN statement to collect a list of all the employees from all three tables when needed. You can learn more about SQL Server JOINS here. You can also use UNION and/or UNION ALL to do the same thing.
Use the following generic data to populate the table (if you like) with these SQL statements:
INSERT INTO bowl.employees(firstName, lastName, empPhone, empSSN)
VALUES('Kelli', 'He', '580-123-4444', '111-22-3333')
, ('Christina', 'Rivera', '580-456-6789', '111-33-4444');
GO
INSERT INTO game.employees(firstName, lastName, empPhone, empSSN)
VALUES('Marcus', 'Perry', '580-123-4444', '111-22-3333')
, ('Madison', 'Butler', '580-456-6789', '111-33-4444');
GO
INSERT INTO rest.employees(firstName, lastName, empPhone, empSSN)
VALUES('Chase', 'Sanchez', '580-123-4444', '111-22-3333')
, ('Jose', 'Simmons', '580-456-6789', '111-33-4444');
GO
Use an ALTER statement to alter or change the name of a schema. Let’s say that the bowling alley owner wants to change the “bowl” schema name to “lanes”. First, create the new schema “lanes”.
USE MyTestDB;
GO
CREATE SCHEMA lanes AUTHORIZATION dbo;
GO
A schema does not get “renamed” in SQL; a new one is created, and the database objects are “transferred” from their current schema to the new one. In the following code block, we are transferring the bowl.employees table from the “bowl” schema to the “lanes” schema.
ALTER SCHEMA lanes TRANSFER bowl.employees;
GO
Since the old schema (bowl) still exists, we may either keep it in the database or remove it.
Below is the basic syntax for dropping a schema in SQL Server:
DROP SCHEMA <schema name>;
Since we no longer need the “bowl” schema, let’s drop it:
USE MyTestDB;
GO
DROP SCHEMA bowl;
GO
Creating a Stored Procedure Within a Schema
Creating a stored procedure in a specific schema is no more complicated than creating any other database object in a particular schema. In the following example, we are creating a stored procedure that uses the UNION statement to collect all employees from all three employee tables. First, I want to create a schema that holds all my stored procedures for my database.
CREATE SCHEMA mySPs AUTHORIZATION dbo;
GO
Now, I need to prefix my stored procedure name with the new schema name.
CREATE PROC mySPs.listAllEmployees
AS
SELECT * FROM lanes.employees
UNION
SELECT * FROM game.employees
UNION
SELECT * FROM rest.employees;
GO
If you’re unfamiliar, here’s a helpful link for more information about stored procedures.
Let’s run the stored procedure.
exec mysps.listAllEmployees;
Results:
Change the Schema Owner
In some situations, the owner of a schema needs to be changed. Maybe for security reasons. Let’s say that you don’t want certain users to have access to everything provided by the dbo schemas in your database. You could change the schema ownership from dbo to guest, which has limited abilities. In the example below, the schema owner for the “lanes” schema will be changed from dbo to guest.
USE MyTestDB -- database name
GO
ALTER AUTHORIZATION ON SCHEMA::lanes TO guest;
GO
If you’re unsure who the current owner is, you can run the following script to list the active schemas and their owners:
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
WHERE schema_id < 100
ORDER BY s.name;
GO
Results (yours may vary):
Next Steps
