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.
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.
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:
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:
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.
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:
SQL Server installs several built-in logical schemas:
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.
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 -- 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 ;
Since we no longer need the "bowl" schema, let's drop it:
USE MyTestDB; GO DROP SCHEMA bowl; GO
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;
created stored procedure" width="452" height="205" />
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):
Aubrey Love is a self-taught DBA with more than six years of experience designing, creating, and monitoring SQL Server databases as a DBA/Business Intelligence Specialist. Certificates include MCSA, A+, Linux+, and Google Map Tools with 40+ years in the computer industry. Aubrey first started working on PC�s when they were introduced to the public in the late 70's.
This author pledges the content of this article is based on professional experience and not AI generated.
Article Last Updated: 2022-12-12
