Database Fundamentals #5: Database Properties

Don’t let the ease of creating databases lull you into a false sense of security. They actually can be very complicated. You can modify and individualize their behavior within your server so that different databases behave in radically different ways. The best way to see all the different manipulations you can make, open the New Database window by right clicking on the Databases folder within the Object Explorer window, assuming you’re already connected to the server.

Don’t bother typing anything into the first page. Click on the Options tab on the left side of the window. You’ll see a screen that should look very similar to this:

Don’t worry. We won’t be talking about all the possible settings on this page. Instead we’ll focus on a few that are extremely important and we’ll learn about a few that are going to be covered later in other blog posts.

Making changes to database options through the GUI is reasonably straight-forward. You’re going to use the drop down lists provided or type values into the text boxes provided. To make changes to database properties through TSQL is generally done one of two ways. Some of the properties can be set when creating the database. Most properties have to be modified after creating the database us ALTER DATABASE commands. I’ll show an example of each.

The most important settings, and the ones you’re most likely going to use to change the behavior of the database when you create it, are located right on top. That makes things easy because it means you won’t have to worry about scrolling through that long list of options.

Collation

One of the most important options on the database is the Collation. Most of the time though, this is set to a single value for all databases on all your servers. Collation controls how SQL Server will deal with character information. Changing the collation is something you do for international systems so that you see sorting and comparisons of character values in ways that are appropriate for the international language and character set that you’re dealing with. This is why most systems will have a single value for all databases. But if you have international languages to support, you may have to change your collation.

Collation is a little different than other options when creating a database using TSQL. It has a slightly different syntax:

CREATE DATABASE MyColDB
 ON  PRIMARY
( NAME = N'MyColDB', FILENAME = N'C:\DATA\MyCol.mdf', 
SIZE = 4MB , FILEGROWTH = 1MB )
LOG ON 
( NAME = N'MyColDB_log', FILENAME = N'C:\DATA\MyColDB_log.ldf' 
, SIZE = 1GB , FILEGROWTH = 10%)
 COLLATE Albanian_100_CI_AI_WS_SC

You can see that the COLLATE command is not a part of the WITH clause I mentioned earlier. The exact values for getting COLLATE working correctly have to be looked up individually because there are hundreds of them. Remember, Books Online is a reference you should be constantly using.

The key point here is that, for most people, most of the time, collation is something that they will set once when they install the server and then never change again. But it’s important to know that it’s a setting you can control if you need it.

Recovery Model

The Recovery Model option is something that you will manipulate constantly as you create databases. A full overview of what the different recovery models are and why you would choose each one will be covered in detail in a blog post later when we talk about database backups. Just to introduce the concept, if you set Recovery to Full, you will need to set up backups for your log. If you set Recovery to Simple then the logs will clean up on their own. There’s a lot more to the topic than just that, but that’s the simple part. To create a database using Simple Recover here’s a script:

CREATE DATABASE MySimpleDB
ON  PRIMARY
( NAME = N'MySimpleDB', FILENAME = N'C:\DATA\MySimpleDB.mdf',
SIZE = 3MB , FILEGROWTH = 1MB )
LOG ON
( NAME = N'MySimpleDB_log', FILENAME = N'C:\DATA\MySimpleDB_log.ldf', 
SIZE = 1MB , FILEGROWTH = 10%);

ALTER DATABASE MySimpleDB
SET RECOVERY SIMPLE;

This script will create the database and then modify the database through the ALTER command to make it into Simple Recovery.

Compatibility Level

Some databases and their code may not be designed for running on the latest version of SQL Server. SQL Server has the capability to support databases from previous versions by changing the capability level. Only alter this if you absolutely are required to. It will change the behavior of the database and could remove or change functionality.

Containment Type

A contained database is one where all the functionality that defines the database and it’s objects are present within that database alone. It means that there are no dependencies on the SQL Server instance that is hosting the database. You would modify this option for databases that you are developing yourself that are likely to be part of an application that you are going to distribute or sell. You may receive contained databases from third party vendors. Because of the way that database containment works, you won’t generally be switching between a non-contained and a contained database. Drilling down into the details of database containment are beyond the scope of this post.

Other Options

The remaining Database Options are split up into different categories in the list below the initial set of options that I’ve defined. Some of these options will be addressed as we come to the pertinent blog posts in this series and others will not be. In general, until you need one of these to be something other than the default values, you should leave the default values in place.

Conclusion

Part of creating and maintaining the database is setting these properties. You’ve seen how to do that with SSMS and with T-SQL. Remember that even though you may be most comfortable with the GUI in SSMS, the best way to expand and learn to manage SQL Server properly is to use T-SQL because it leads to automation of your processes.