Underused SQL Server features: Schemas
In today’s article we will review the importance of one of SQL Server’s most underused features: schemas. We’ll cover what they are, and how to get started with them right away on an existing database.
Introduction
If you ever worked in a project where a SQL Server database was being used, chances are you’re familiar with this image:
From the beginning of my career, I was so over-exposed to this that I almost ended up normalizing it. “dbo” is SQL Server’s default schema. This means it is simply the schema SQL Server assigns to an object when being created if no other schema is specified. That doesn’t sound like a good reason to have it be the schema for all database objects we create throughout the development of our applications. We can do better, let’s take a look.
What is a schema, anyway?
The definition of a schema, straight out from Microsoft’s documentation, is the following:
A schema is a named container for database objects, which allows you to group objects into separate namespaces.
So the main intent of schemas is to provide a means of grouping related objects together.
In addition, as a secondary advantage, schemas provide a way of managing security rules for all objects contained within them. Whatever security rule you apply to a given schema, it will be automatically inherited by the child objects, unless you specifically change a given rule in one of the objects, thus overriding the inherited rules.
How can I get started?
If you come across something like this in your database:
That probably means this is an opportunity to use schemas for what they have been created, and remove those prefixes from the object names.
In the given example, there are clearly two separate groups of objects, the first one corresponding to the administrative operations of a club, and the second one corresponding to the club itself, as perceived by the public.
In this case, we can begin by creating two new schemas, called Administration and Club, respectively. For that end, we can use the CREATE SCHEMA T-SQL statement. Note that, during this article, all T-SQL commands are shown as issued in an instance of sqlcmd. If you would like to know more about sqlcmd, visit the documentation page.
1> CREATE SCHEMA Administration;
2> CREATE SCHEMA Club;
3> GO
Now that the schemas exist, we can transfer existing tables to them using ALTER SCHEMA.
1> ALTER SCHEMA Administration TRANSFER Administration_Employees;
2> ALTER SCHEMA Administration TRANSFER Administration_Offices;
3> ALTER SCHEMA Club TRANSFER Club_Activities;
4> ALTER SCHEMA Club TRANSFER Club_Members;
5> GO
After doing the transfers, in SSMS (SQL Server Management Studio) you should see something like the following:
Now that the tables are in their corresponding schemas, the prefix has become redundant, so we should rename the tables to remove the prefixes. For that purpose, we can make use of the sp_rename stored procedure, included as part of the base functionality of SQL Server.
1> EXEC sp_rename ‘Administration.Administration_Employees’, ‘Employees’;
2> EXEC sp_rename ‘Administration.Administration_Offices’, ‘Offices’;
3> EXEC sp_rename ‘Club.Club_Activities’, ‘Activities’;
4> EXEC sp_rename ‘Club.Club_Members’, ‘Members’;
5> GO
You will receive warnings indicating that changing any part of an object name could break scripts and stored procedures. This is a true danger and one that will materialize for any stored T-SQL code that references the old name of the table. This happens not only when renaming an object, but also when transferring it from one schema to another. We will deal with this issue in an upcoming article in this series, but if you want to know right away the implications of this and possible solutions, you can check this document out.
After completing the renaming, you will be left with the following:
Great, I have schemas. Now what?
So far we discussed what schemas are, what they’re used for and how to create them and bring existing objects to them. Now let’s take a minute to observe and appreciate what benefits can be drawn from the new structure derived from these changes, with a concrete example.
Suppose a new application is written which is meant to manage the administration part of the house, without regard to any of the data related to the operations of the club (for example, an application that managers can use to coordinate employees efforts across the different office buildings). Such an application will need to have a role associated with the security principal being used by it, and the role will need access to all the tables regarding administration, but no access whatsoever to the tables related to the club as such. In order to create this role, we can do the following:
1> CREATE ROLE Managers;
2> GRANT INSERT, SELECT, UPDATE, DELETE ON SCHEMA :: Administration TO Managers;
3> GO
Now every security principal you add as a member to the Managers role will be able to CRUD all tables of the Administration schema. Of course, this might not be what you want, and, if that is the case, you can always explicitly deny a specific permission on a specific object in the schema, and this rule will override the general grant being applied here.
Take a moment to understand why this is important. Without schemas, instead of one single GRANT you should have executed one GRANT for each object related to administration. With schemas, you can manage all administration-related objects as a whole.
Conclusions
If you aim for maintainability, you want to have a way to group objects with more than a naming convention. It will make a great difference, especially in those databases involving a great amount of objects.
I encourage you to go and try this right away in one of your own projects, so you can explore the benefits of schemas yourself.
Nahuel Prieto — Sr. Software Specialist @ Spark Digital
