Schemas are a very useful tool for managing the objects in your database. From security through process, through placement, schemas provide you with another tool to control how your database behaves.
Schemas
The tables created so far in this blog series have all been attached to a schema, dbo. Depending on how you login to the server and the security settings for your user, this is usually the default schema. A schema is simply a container in which you place objects. Once placed there, a schema is a method for managing the objects it contains. Schemas give you a simple way to control placement of the objects on filegroups. Schemas are a very easy way to manage security. The use of schemas becomes extremely important as your database becomes more and more complex. Any database you create following this blog series will be fairly simple in order to focus on concepts related to the fundamentals scope of the series. For this blog, I won’t spend lots of time dealing with complex business models. However, because you will have to deal with complex business models, it’s best to have an understanding of the tools you have to help deal with them.
Create a Schema
Creating a schema is extremely simple. Using the SSMS GUI, navigate through the Object Explorer window to a database. Expand the database and then expand the folder labeled Security. In there, you’ll see a list of different schemas. Right click on the folder itself and in the context menu select “New Schema…†This will open the New Schema window as shown here:
For our example, type in the name of the schema, Finance. There are a number of other options that you could set for this schema, including defining a different owner than your login. But, most of that relates to security and will be covered later in the blog series. For now, click on the OK button after typing in the name. A new schema with the defined name will be immediately created.
It’s just as easy to create a schema using T-SQL. If you don’t have one already, open a query window and use this script to create another schema:
CREATE SCHEMA Personnel;
That statement will create a schema named Personnel. Just like with the tables you created, this new schema won’t appear in Object Explorer until you refresh the list of schemas.
Moving Tables Into Schemas
So far, none of these new schemas have anything in them. We’ll fix that by moving tables from the existing schema to these new schemas. Through the GUI, it’s a somewhat complicated procedure. First you navigate to the table you want to move. Right click on a table in the Object Explorer window and select Design from the menu choices. This will open up the Table Designer window. On the right, in the table properties, you can pick the schema from a drop down list. Select the schema Management from the list. A window will pop up informing you that you’re changing security settings on the object and asking if you want to proceed. It will look like this:
Clicking on the Yes button will modify the table, but it won’t be committed until you click on the Save icon on the toolbar. To save the object you can also use the quick key CTL-S or select Save from the File menu. Once saved, if you refresh the object explorer window, you can see the table is now in a different schema than before.
This is a much simpler operation using T-SQL, but, you’re not warned about the changes that will take place when you perform the move. It is important to learn what you’re doing and what the implications may be before you do it. This is the T-SQL to move the Person table to the Personnel schema:
ALTER SCHEMA Personnel TRANSFER dbo.Person;
You won’t see a warning if you run this, it will simple complete the operation and the Messages tab should appear with the “Command(s) completed successfully.†Message. You’ll have to refresh the Object Explorer to see this change as well.
Conclusion
Planning on creating and using schemas within your database is a good idea. It offers a greater degree of control over your tables. They are easy enough to implement that there’s no big reason to not use them.