Overview
In this article, we will cover some of the basics of SQL Server schemas, what they are, how to create a unique user-defined schema, and why you should. We will also cover some of the advantages and disadvantages of built-in vs user-created schemas. We will wrap it up by putting theory to practice by first creating a schema and then altering that schema.
What are SQL Server Schemas
In its simplest form, 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 you to have the same schema in a different database and they will work independently of each other.
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, you may not have two database objects with 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 employees that contains the employee’s name, address, phone number, etc. I may want to create a view that only pulls the name and phone number from the employee’s table for privacy reasons. In this case, I would be tempted to give my view the same name as the table it is referencing, the employees’ table. I would do this so I 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.
- 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 that are 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, perhaps the biggest advantage of SQL Server schemas is the security level they provide. 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 we get too far into 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 users of the database, how a person logs in.
- Roles are permissions that 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 and db_owner is a role.
SQL Server Default Schemas
SQL Server installs with a default of 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 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; GO
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.
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.
The basic syntax for creating a schema.
CREATE SCHEMA <schema name> Authorization = dbo; GO
Remember, each schema must have an owner. In the code block above and subsequent examples, we will be using “dbo” as the schema owner.
Creating your own unique schemas will allow you to combine what normally 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 employees’ tables in the same database, but under different schemas. Here’s an example:
USE MyTestDB; GO CREATE SCHEMA bowl AUTHORIZATION dbo; GO CREATE SCHEMA rest AUTHORIZATION dbo; GO CREATE SCHEMA game AUTHORIZATION dbo; GO
Now that the schemas are created, in the code block below, we will create three tables, all named "employees" but within different schemas.
Remember, you can not have two or more tables with the same name. However, keep in mind that the name of the table includes the schema name.
CREATE TABLE bowl.employees ( colID INT IDENTITY , firstName VARCHAR(20) , lastName VARCHAR(20) , empPhone VARCHAR(20) , empSSN VARCHAR(11) ); GO CREATE TABLE rest.employees ( colID INT IDENTITY , firstName VARCHAR(20) , lastName VARCHAR(20) , empPhone VARCHAR(20) , empSSN VARCHAR(11) ); GO CREATE TABLE game.employees ( 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.
If you would like, you may populate the tables you just created with the following generic data.
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
If you need to alter or change the name of a schema, you can do so easily with an ALTER statement. Let’s say that the owner of our bowling alley wants to change the “bowl” schema name to “lanes”. First, we must create the new schema “lanes”.
USE MyTestDB;
USE MyTestDB; GO CREATE SCHEMA lanes AUTHORIZATION dbo; GO
In SQL, we don’t really “rename” a schema. We create a new one and “transfer” our database objects from their current schema to the new one. In the following code block, we are transferring 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.
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 certain schema. In our following example, we are creating a stored procedure that uses the UNION statement to collect all employees from all three of our 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, all I need to do is 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 not familiar with stored procedures, here’s a helpful link for more information about Stored Procedures.
Run the stored procedure.
exec mysps.listAllEmployees;
Results:
Change the Schema Owner
In some situations, you may find that you need to change the owner of a schema. You may need to do this 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 may want to change the schema ownership from dbo to guest, which has limited abilities. In the sample below, we want to change the schema owner for the “lanes” schema from dbo to guest.
Syntax to change schema owner.
USE MyTestDB GO ALTER AUTHORIZATION ON SCHEMA::lanes TO guest; GO
If you’re not sure 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
Results: (Your result set may vary)
Moving on, the next step is to learn how to create a database table.
It sounds simple enough, and really it is.
However, SQL offers two methods of accomplishing this.
Either way, it's your choice.
