Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Microsoft SQL Server Defined

Last updated Mar 28, 2003.

SQL Server is a large, complex product. It didn’t start out that way, having only a relational database engine (more on that in a moment) and a set of graphical management tools — which in itself differentiated it from other databases such as Oracle and IBM’s systems. At the time, those were managed primarily using text. Having a graphical interface made SQL Server easier to use for a lot of people.

This guide has a lot of information on all of the other features SQL Server has added over time, such as Reporting Services and Analysis Services. But before you dive into those sections, you need to understand just what a Relation Database Management System (RDBMS) is and why it is different than storing data any other way, such as in a word-processing or just plain text (ASCII) document. This section of the guide will help you do that, along with how you can access that data. At this bottom of this overview I’ll show you the other articles contained in this section of the guide.

We also provide an easy way to find out when any part of the guide is updated. If you're using an RSS reader, you can subscribe to these notifications here. We also have a blog you can subscribe to for even more up-to-date information.

The sections in this Guide are designed to allow quick access to what you need. The tutorials and overviews can be read in just a few minutes, and many contain useful scripts and hands-on guides to examples you can follow.

Relational Database Management Systems

Before we dive into the other articles in this section, let’s cover some of the basics about what SQL Server does and how you can use it.

As I mentioned earlier, the heart of Microsoft SQL Server is a Relational Database Management System. An RDBMS stores and retrieves data for multiple sources. But then, so does word processing and spreadsheet software and regular old text files. What differentiates an RDBMS is the way it stores and retrieves data. Data in an RDBMS is stored in sets — think algebra sets here, with circles and numbers, circles overlapping where the numbers match. A relational database is like that. The sets of data contained in the database are related to each other.

To oversimplify a bit, think of two spreadsheets, one with names and addresses, and another with the names of a few companies, like this:

Names

Name Address Phone Number
Bob 123 Here Street 123-1234
Jane 231 Under Street 231-0987
Sparky 432 Simon Lane 321-9877

Companies

Name Phone Number Address
Big Co 123-7657 123 There Street
Small Co 234-4567 231 Over Street

If you want to store the name of a particular person and where he or she works, you need to relate the two spreadsheets together somehow. To do this, you could repeat the data of the company information for each person; but a better approach might be to include another column in each of the spreadsheets that contains a number. In the Companies spreadsheet, this column might be called Company Number. You could create another column in the Names spreadsheet with that same number, like this:

Names

Name Address Phone Number Company Number
Bob 123 Here Street 123-1234 1
Jane 231 Under Street 231-0987 1
Sparky 432 Simon Lane 321-9877 2

Companies

Name Phone Number Address Company Number
Big Co 123-7657 123 There Street 1
Small Co 234-4567 231 Over Street 2

By visually "tying" these numbers between the spreadsheets, you could look for a list of the names of the people in the Names table and the company name from the Companies table where the numbers in the Company Number columns match. (Read that again slowly if you need to!) That would produce this new list:

Merged List

Name Company Name
Bob Big Co
Jane Big Co
Sparky Small Co

By combining the things in common between these two groups of data (the Company Number), you’re using something you learned in school called “set theory” — and that's how a relational database system works. SQL Server manages this kind of data, and that makes it a relational database management system.

SQL Server manages this kind of data by storing it in two or more types of files. The first of these files is called the database and it stores the actual data. The second file is called the log, and is used to write the data to the database. Why would an RDBMS do that? Why not just write the data to the database in these tables and be done with it?

The reason is that using a log file process produces both data integrity and speed. Users write data to the log in a sequential fashion, so that each entry can be tracked, and more importantly, backed out if necessary. The log file maintains all the transactions that the users have entered. When the server gets a free moment, it writes this data to the database. Users then query this "read-only" copy of the data from the database. Any changes, deletions, or additions to the data are made first to the log.

By separating the log file from the database file, you can place the database on one drive and the log on another — allowing the querying to happen on one set of spindles and the entries to happen on another, making both processes faster.

The other advantage to a log file is that if the server crashes, the data since the last entry is still in the log. When SQL Server starts up again, it can look at the entries that didn't make it to the database when it crashed. If the entries create a complete set of data called a transaction, the data is "rolled forward" — entered into the database. If the data was incomplete, the bits of data are not sent to the database — a process called "rolling back" the database.

So what constitutes a transaction? Well, there are implicit transactions, meaning that they just should be kept together, like the letters in a word. Suppose you saw the following line written on a page:

Contemp

You wouldn't be sure if the word was supposed to be contemporary, contemplation, contemporaneous, or something else. The word (transaction) isn't complete. You don't really need to understand which word I meant; a simple spell-checker tells you that these letters aren't even a complete English word. This is similar to what SQL Server can do — it implicitly knows that if data isn't a well-formed complete binary unit, then it isn't supposed to write that data to the database. It would make the database inconsistent, and you wouldn't be able to access the database anymore.

You can also define explicit transactions, meaning that you tell the system “I’m starting something” and then “I’m done now.” An incomplete transaction in this mode is more analogous to a sentence than a word, like this:

First, connect the green wire to the

These words are all spelled correctly; they just don't make sense to us. They do make sense to a computer, though, since they're all correctly formed. Computers, unlike us, can't be confused by this kind of sentence since computers don't understand context. You have to tell the computer that you want certain things kept together. You do that by telling SQL Server to group certain items together—that they all go, or none of them do. That's an explicit transaction SQL Server includes in its instruction set (called Transact-SQL, or T-SQL for short)—consisting of the commands BEGIN TRANSACTION and END TRANSACTION. SQL Server knows to keep these bookends together. Consider the following code:

BEGIN TRANSACTION
 <Insert some data>
 <Insert some more data>
 <Insert even more data>
END TRANSACTION

When SQL Server sees code like this, it will keep all items grouped together. If line 3 doesn't make it, the whole thing is aborted. It's sort of an all-or-nothing approach.

Why would you want this behavior? Let's take a real-world example. If you use an ATM machine to transfer $100 from your savings account to your checking account, you expect that if the money comes out of savings, it goes into checking. Otherwise, you don't want it to go anywhere at all. The bank, on the other hand, wants to make sure that the money comes out of savings if it goes into checking! You both want to make sure that both things happen (money comes out, money goes in) or neither of them happens (both accounts stay just as they are).

Now that you know how the files work, you should also know that these files affect the way backups are made. I describe these backups in the section of this Guide called "Backup and Recovery." For now, be aware that you can back up all or part of the database, and you can also back up the log separately. I'll show you more of this log file architecture there.

So what is SQL Server? It's a high-end data server. It allows you to enter, edit, delete, and retrieve lots of data very quickly. Many people can use it at once, and it performs nicely, even under load. In the rest of the tutorials, I'll explain the features SQL Server provides in addition to the database engine.

In this section

To help you navigate this section of the Guide, let's take a look at a few of the sections you'll find here.

SQL Server Editions

Microsoft, like most database vendors, creates various copies of its software that has different capabilities. The vendors do this to charge different rates, and to serve different customers. Microsoft calls these “editions” (not to be confused with “versions”, which is based on when it is released) and they range from a small, free offering called MSDE in SQL Server 2000 and SQL Server Express in SQL Server 2005 and higher, all the way up to editions capable of running on large-scale systems. This section of the guide explains what those editions are and how they differ from each other.

SQL Server Access

A database is a great thing, but all it does is store data. To let users get data in and out of it, you’ll need to provide some sort of access for them. This section covers the general ways you can do that.

InformIT Articles and Sample Chapters

There are other resources here at InformIT that can help you with SQL Server. Most notably are the other articles and tutorials you’ll find, and a wealth of online books in Safari.

Online Resources

The two best sites for general SQL Server information are here and here.  

Click Next to continue reading. Next topic: SQL Server Editions