Database Fundamentals #4: Create a Database

SQL Server provides multiple ways to perform most functions. In order to maximize your understanding of how SQL Server works and have as many different mechanisms as possible for getting work done, you’ll use the GUI and TSQL to learn how to create and drop databases. You can then use whichever mechanism works best for you at the moment.

Using  each method, we’ll first create a database using the least amount of work possible, just so you can see how easy it is create a database. We’ll go over how to remove databases from the system, getting rid of the database you just created. From there we’ll drill down to create another database, exploring some of the different mechanisms you can use to change how databases get created. Then we’ll clean up behind ourselves and remove all those databases too. Performing the actions repeatedly will help you to understand what you’re doing better and increase your retention of the information.

Creating a database with the GUI

Connect to your server using SSMS. In the Object Explorer window, right click on the Databases folder (if it’s not visible, you may have to click on the plus sign next to the server to expand the list for that server). This will open up a context menu. Right at the top will be a menu choice for “New Database…” Clicking that will open the New Database window as shown below (you can click on the image to make it larger):

Creating your first database is very simple. Type a value into the “Database name:” text box. Try “MyFirstDb.” As you type you’ll see that the Logical Name down in the “Database files” section will be filled in as you type. The data file will have a logical name that is the same as the database name. The log file will have a logical name that has “_log” appended to it. You can leave these as they are for the moment. To create a database, nothing else needs to be done. Your screen should look similar to this:

Click the OK button and a database will be created using the name you gave it and the automatic file location defined by default. The New Database window will close and the database you just created will be shown at the bottom of the list in the Object Explorer window. Normally that window is sorted alphabetically. If you click on the Refresh button at the top of the Object Explorer, the list of databases will resort and your database will appear in it’s appropriate location.

You can click on the plus signs in the Object Explorer window now and see that you have a database, but one without any objects under than system objects. Databases are created by copying a system database named Model. If you want an object, such as a table or a stored procedure, to always appear in any database you create, you can add that object to the Model database.

To remove the database, right click the database name in the Object Explorer window. This will bring up a context menu. Near the bottom of the menu will be a menu selection labeled “Delete.” Clicking that will open the “Delete Object” window just like in Figure 2.4:

There are options that you can manipulate with this window, but it’s unlikely you’ll need to unless other people are connected to the database currently. Clicking the OK button will remove the database from your system.

Let’s create another database, but this time, manipulate the file settings to see how that works.

Manipulating files with the GUI

Creating a database using just the defaults is fine in many situations, but what if you need to deviate from the defaults for some reason? For example, you might have one drive on the system where you’re putting most of your databases, but you have a special, very large drive, that you need to map a new database to because you expect it to grow in size. To make this type of manipulation with the GUI, you would start the process exactly the same way. Connect to the server, if you’re not already, then navigate to the Databases folder in the Object Explorer window. Right click on that folder and select “New Database…” from the menu choice. This will open the New Database window that you’ve already seen.

Type in a database name, “MySecondDB.” Now move the cursor down to the grid under Database Files. Click inside the text box under the Logical Name column. You can modify this. You would do that if you had a particular standard for naming files you wanted to maintain. Click the Initial Size column. Here you can adjust the size of the database up or down. Since I don’t know your system at all, let’s move the initial size up a very small amount and create a 4MB data file. You can either highlight the value and type a replacement or use the scroll buttons to change this value. You would make changes to this, usually to larger numbers, not smaller, when you had a database that you knew needed more space when you created it. This way you don’t have to rely on the autogrow settings. Speaking of autogrow settings, let’s modify those.

Click on the box under the Autogrowth/Max Size column. You’ll see an ellipsis on the right side of the box. If you click on that ellipsis, you’ll open a new window that controls autogrowth. It will look like this:

Most of this window should be self-explanatory. The checkbox at the top, “Enable Autogrowth,” is for turning autogrowth on or off for this file. That’s important to remember, autogrowth is set on each file. This is so you can pick and choose which files get autogrowth and which don’t. You can change the autogrowth to use a percentage or grow by a fixed amount. Unless your databases are extremely small, I would not recommend letting it grow by 1MB at a time. It depends on the size of your databases, but generally, this number should be a lot higher. Finally, the most important setting, is “Maximum File Size.” As you can see above, the default value is “Unlimited.” This means that with this setting, SQL Server will grow to until it fills the drive, just on this file alone. I strongly recommend that if you do enable autogrowth, that you put limits on the files. This won’t prevent a drive from being filled, but it does make it less likely. Once you’re done making adjustments to the autogrowth settings, click OK to close this window and save the changes.

If you scroll all the way to the right in the Database Files window, the last column is the physical file name of the file that will be stored on your Windows file system. This is usually named by default from the Logical Name, but you can control it directly by typing into this box. I’ve shown an example here:

Finally, you have the capability to add new files to the database. This is necessary so that you can create multiple storage locations for your data. SQL Server will automatically distribute the data across these locations, or you can directly manipulate what gets stored on a given file when you create the database objects. All of this is additional management capabilities that enable your flexibility in making sure that you can store information in the most optimal fashion for your database. Most databases you create are just going to have the two files though.

Before proceeding, go ahead and delete this database the same way you did before. Now let’s try creating a database using TSQL.

Creating a Database with TSQL

TSQL is the scripting language for SQL Server. As such, it’s your primary means of automating SQL Server. If you want to create a database, and you need to create that database on more than one server, or you want to recreate it on one server, you’re not going to want to use the GUI to click through all sorts of different settings. It will be much easier to type the appropriate information and then save the file. You can re-run it any time you need to.

The basic approach for creating a database using TSQL is actually very simple. The language is largely self-explanatory for simple operations like this:

CREATE DATABASE MyFirstTDB;

If you click on the button that is labeled “New Query…”, which you can see in the upper left of the image below, a query window will open inside SSMS. You can type this code or copy and paste it into the window. When you’re ready to execute the code, click on the button that says “Execute” and has a red exclamation point next to it, again, visible below. This will execute the query and you’ll have a new database.

You’ll also see the screen change. A new section of a window will open up below your TSQL code. This is your introduction to the results and messages pane. When you run a query in SSMS, by default, the results and any messages from that query appear in a separate pane in the query window along with tabs to separate the two. Assuming you typed everything correctly, you should see something like this:

It’s a good thing that you see this message because if you look over at the Object Explorer, you won’t see your new database. You need to click on the Databases folder in the Object Explorer and then click on the refresh button at the top of that window. You’ll then see your database listed.

Since you provided no other information, SQL Server used the defaults, including the default file size and location for building your database. It’s that easy. Removing the database from the system is just as easy. Type this command:

DROP DATABASE MyFirstTDB;

Before you click the Execute button, you need to know something about how the query window works. Depending on how you followed the directions you’ve been given, you may have two different statements, one for CREATE and one for DROP. If you click on the execute button now, SQL Server will attempt to execute both statements. Since there is already a database ther called MyFirstTDB you’ll get an error. You need to be careful about how you execute your code from within the TSQL window. If you have two separate commands, the best thing to do is use the mouse to select the statement you wish to execute. Your TSQL window would then look like this:

With the DROP statement selected, you can now click on the Execute button, or, if you’re fingers are already on the keyboard, just type CTRL-E, which will also cause the query to execute. You’ll once again see that the statement has completed successfully. The Object Explorer window will not reflect the change, so you’ll want to refresh that at some point.

You’ve now used TSQL to create and drop a database. As you can see, it can be very simple. Now we’re going to use TSQL to manipulate the files in the same way we did using the GUI. This will not be quite as simple , but we’ll walk through the process so that it’s completely pain free.

Manipulating files with TSQL

Using TSQL to manipulate the files is not difficult, but it does require quite a lot more syntax than if you simply rely on the defaults to do everything for you. There are actually a very large number of parameters and settings that you can use when running the CREATE database script. They give you all sorts of control that you may need for creating particular databases, but most of the time they’re not needed. Going through every possible permutation of the command is way beyond our scope. For more information always remember that you can refer to the online documentation.

To manipulate the file sizes, autogrowth settings, logical names and file names as you did previously through the GUI, this script should suffice:

CREATE DATABASE MySecondTDB
ON  PRIMARY
(NAME = N'MySecondTDB', FILENAME = N'C:\DATA\MySecondTDBData.mdf', 
SIZE = 4MB , FILEGROWTH = 1MB, MAXSIZE = 10MB)
LOG ON
(NAME = N'MySecondTDB_log', FILENAME = N'C:\DATA\MySecondTDBLog.ldf', 
SIZE = 1MB , FILEGROWTH = 10%);

Let me point out several things in this script. First, and most importantly, the script could be all on one line. But that makes it very difficult to read. Instead it’s broken down into individual sections that logically group better together. Even those sections are broke down with line breaks. For most commands in TSQL, this type of rearranging for aesthetic purposes is fine, and actively encouraged by anyone who has to look at scripts after you’re gone. The second thing is that I’ve changed the default location of my databases, not to illustrate a point, but just so that there is less useless information in the script. You will either need to create a directory on your system on the C:\ drive as I have done, or you’ll need to modify the path for these scripts to fit your system.

The basic command is exactly as you would expect from the previous script. The very next line begins to deviate. By default you get a Filegroup. A Filegroup is a further mechanism for managing your files and placement of objects on those files. It’s not that important at the moment to understand these and they will be covered in more detail in a later blog post. Suffice to say, that I had to define one and tell SQL Server which files were going into it as part of creating the database when I’m not using the defaults. The first of these is the PRIMARY filegroup. You can see the same thing back in the images earlier of the GUI.

Defining the file is logically straight forward. I’m giving a NAME, which is the logical name. Note the letter ‘N’ directly in front of the value. This is because the text for the definition of objects within SQL Server is Unicode. Unicode is used so that you can create objects using different character sets for different languages. The text values are differentiated from objects within the code by placing them inside of single quote marks. You can see that in the NAME and also in the FILENAME values. Placing the ‘N’ directly in front makes that value into a Unicode value.

Setting the database size is very straightforward. In fact, it’s actually quite a bit easier than using the GUI since you can define the values so that it’s clear you want a MB or a GB instead of having to rely on just creating numbers in the thousands to achieve larger databases. Setting autogrowth is also very straight forward, but uses the property FILEGROWTH instead of autogrow. You’ll note that I’ve left the defaults in place in this case so that you can see how to set to a value or a percentage. I also added the MAXSIZE to limit the amount of growth allowed for this database to 10MB

That’s all that’s needed to directly manipulate the database files. If you ran that script on your system, please delete that database by either running a DROP command or deleting it from the Object Explorer.

Conclusion

These are the fundamentals to creating a database. The better method to learn and understand is the method using TSQL, however, the GUI is there and you can use it. You’ll be better served long term to focus always on how to automate your processes, because you will regularly repeat them.