SQL Server Reference Guide

We're beginning our study of programming SQL Server databases with a discussion of the very first thing you will need to understand: database objects. There are several database objects that are common to many Relational Database Systems (RDBMS). These include:

• Databases
• Tables
• Views
• Indexes
• Users

If you’re not familiar with how relational databases work, you might want to check out this overview before you continue.

And some objects are particular to a vendor, or at least implemented in a different way. For SQL Server, these include:

• Stored Procedures
• Roles
• User Defined Functions
• Other programming and system constructs…

Other objects are part of the different features in SQL Server such as the SQL Server Agent, Analysis Services, Data Transformation Services and other features of the SQL Server suite, but I'm going to focus on the primary objects in this series. You'll learn more about the other objects throughout this site.

We'll start with the database itself. You can read in this series of articles on maintenance and backups that SQL Server is made up of the software that makes up the engine that accesses data (called the binaries), and the files that make up the databases (called the data files). This, along with some registry entries and a few ancillary files make up the database system on a particular computer.

The data files are made up of at least two files: a file to store the data (with an extension of .mdf or .ndf) and a file to store the transactions (with an extension of .ldf). You can have more than one of each of these files, used for performance and security.

Here are a few quick facts about both the data and transaction log files:

• They must be stored on a locally connected drive, or SAN, but they can (and most often should) be stored on separate drives.
• They can be set to a specific size or set to auto-grow to a maximum size. They can also be shrunk and expanded. All of these operations can happen on the fly.

Here are a few more interesting facts about SQL Server databases:

• The database files can be backed up while in use.
• Databases can be copied, moved, detached and attached to another server.
• You can rename a database.
• A database can be a maximum size of 1,048,516 Terabytes. Hopefully, you can make do with that!
• You can have 32,767 databases per SQL Server.
• You can have 2,147,483,647 objects in a database.

OK, that's enough trivia for now. At this point let’s examine how to create a database.

But before you create your database, it's important to remember that one of the system databases, called model, has a lot to do with how your database will end up.

The model database is just that — it serves as a model for the rest of the databases on your server. Any options, tables, users, or any other objects in the model database will exist in any new database you create. Unless you specify otherwise, the recovery settings, size, and other options will carry through to your newly created database. If you have a certain stored procedure that you'd like to have in every new database, just create it in model. Be careful to document that.

Note that this doesn't hold true about restored or attached databases; they keep their original options.

If any of the terms above seem new to you, check out this tutorial for more information.

You can create your database in several ways, but the main ways are using graphical tools or by using Transact SQL statements, and possibly restoring from a backup. Since I've covered the backup and restore process in another article, I'll focus on the first two methods here.

Creating a database using Transact-SQL

I’ll begin with the method that you can use on both systems. Before I start, however, you should know that the CREATE DATABASE and ALTER DATABASE statements are among the longest entries in Microsoft’s Books Online, so to keep this understandable in the time and space we have here, I won’t cover every option. You should, however, take the time to read over these entries for both SQL Server 2000 and 2005, since each has slight variations, and the options you set make all the difference when you create your system regarding performance, maintenance, security and so on.

You can enter these commands in SQL Server 2000’s Query Analyzer, SQL Server 2005’s Management Studio, or at a command prompt in the operating system using osql or sqlcmd. You can also enter these commands in a program or using PowerShell. In short, you have a lot of options, but the syntax here remains the same.

The simplest command to run for creating a database called “Test” is:

CREATE DATABASE [Test]

GO

SQL Server will use the defaults found in the model database to create this one. Whatever is there, is here. Of course, you may want at least a bit more control, so let’s take a look at a more complex example:

CREATE DATABASE [Test]
ON (NAME = N'Test_Data'
 ,FILENAME = N'd:\Program Files\Microsoft SQL Server\MSSQL\data\Test_Data.MDF' 
,SIZE = 1, FILEGROWTH = 10%) 
LOG ON (NAME = N'Test_Log'
 ,FILENAME = N'd:\Program Files\Microsoft SQL Server\MSSQL\data\Test_Log.LDF'
,SIZE = 1
,FILEGROWTH = 10%)
GO 

There are quite a few settings going on here. Books online has the entire syntax, but I'll explain a few of the more important options.

The first option is the name. Notice that I have brackets [] around the text of “Test”. You don't really need to do that for this simple name, but if the database has special characters in the name like spaces and so forth (never a good idea), this is necessary. If you get in the habit of including the brackets around any database name you’ll save yourself some heartache later when you work with your code. The N part means that regardless of the Unicode setting (if you don’t know what that is you can find out more here) the string will render properly.

The next option - ON (NAME= — establishes the logical filename that SQL Server will use to reference this database. The next option — FILENAME = — sets the file name that the operating system will use. You’ll want to change your example to a directory on your system.

The SIZE option sets the starting size (in megabytes) that the database will use, and the FILEGROWTH option sets how big the jumps will be when the server requests more room for that file.

There are far more options, but these are the ones you’ll find yourself using the most. Let’s now examine the graphical tools method for creating the database.

Creating A Database Using Graphical Tools

To create a database in SQL Server 2000 graphically, you can use the Enterprise Manager tool, which you can read more about here. Once inside, drill down to the Server object, and then the Databases object. Right-click that object and a menu appears. The first option on the menu allows you to create a new database. Once you click that, you’ll be led through a series of panels that set the options for the database.

For SQL Server 2005, you’ll use the SQL Server Management Studio tool, which you can read more about here. Navigate down to the Server object, locate the Databases object, right-click that and select New Database... from the menu that appears. Instead of being led through a series of panels, you’re presented with one that has various tabs on it. Click into each of the tabs to see the elements below.

Database Name

Here you can give the database a name. As I mentioned earlier, you can use spaces or other special characters in the name, but it is usually a bad idea to do so. The reason is that it is confusing, could result in having errors in your code, and some languages might not even be able to compose the strings easily with a space or other special character in the name.

File Locations

You’re able to specify the logical name of the database file (what Microsoft calls the database file), and tie it to the physical file or files on the hard drive. This logical name gets stored in one place in the master system database, and the physical tie to that gets recorded in another.

You can also set the growth options for the files. If you set the option not to grow, you can set an absolute size. Otherwise, you have the option of setting the growth to happen in megabytes or percentage. It's important to keep in mind that a percentage growth is exponential; in other words, 10% of 1 megabyte is smaller than 10% of 10 gigabytes. It seems obvious, but I've seen DBAs scratch their heads when a server suddenly runs out of room this way. Also keep in mind that the growth operation, although automatic, is not without cost. Several locking operations take place while the next bit of space is allocated, and that can slow down the system. If you can set a particular size, you should do so, but often it is difficult to plan that, so the safer option is to set the database to grow.

Speaking of running out of room, another option limits how large the database can get. What happens when it does? Actually, SQL Server is pretty kind about that — the database is still usable, it just goes into read-only mode. That means that you can back it up to tape while you scramble for more space.

You can set the log file name, size and growth options, all with the same information as the data files.

Database Options

There are a lot of database options you can set in the two tools, but the one that you should be concerned about first is the recovery model. You can read more about that here.

Most of the time you'll leave the database at the default collation for your system — but you can check Books Online to see the various code page types that you can use to support the languages your server needs.

This introduction article to the database object will sheds a little more light on the first of our SQL Server objects. We'll ramp up a bit faster in future articles on the tables, stored procedures, and other objects in the database. We'll also see the database creation process used again during our future programming exercises.

InformIT Articles and Sample Chapters

Further along we put this information to practical use. You can see an example of that here.

Online Resources

The model system database I mentioned in this article has a lot of powerful features you many want to investigate further. Read about it here.