RDBMS Basics: What Makes Up a SQL Server Database?

What makes up a database? Data for sure. (What use is a database that doesn’t store anything?) But a Relational Database Management System (RDBMS) is actually much more than data. Today’s advanced RDBMSs not only store your data, they also manage that data for you, restricting the kind of data that can go into the system, and facilitating getting data out of the system. If all you want is to tuck the data away somewhere safe, you could use just about any data storage system. RDBMSs allow you to go beyond the storage of the data into the realm of defi ning what that data should look like, or the business rules of the data.
Don’t confuse what I’m calling the “business rules of the data” with the more generalized business rules that drive your entire system (for example, preventing someone from seeing anything until they’ve logged in, or automatically adjusting the current period in an accounting system on the fi rst of the month). Those types of rules can be enforced at virtually any level of the system (these days, it’s usually in the middle or client tier of an n-tier system). Instead, what I’m talking about here are the business rules that specifi cally relate to the data. For example, you can’t have a sales order with a negative amount. With an RDBMS, you can incorporate these rules right into the integrity of the database itself. The notion of the database taking responsibility for the data within, as well as the best methods to input and extract data from that database, serves as the foundation for this book. This chapter provides an overview of the rest of the book. Most items discussed in this chapter are covered again in later chapters, but this chapter is intended to provide you with a road map or plan to bear in mind as you progress through the book. With this in mind, I’ll give you a high-level look into:
Database objects
Data types
Other database concepts that ensure data integrity

AN OVERVIEW OF DATABASE OBJECTS
An instance of an RDBMS such as SQL Server contains many objects. Object purists out there may quibble with whether Microsoft’s choice of what to (and what not to) call an object actually meets the normal defi nition of an object, but, for SQL Server’s purposes, the list of some of the more important database objects can be said to contain such things as:

• The database itself
• The transaction log
• Tables
• Indexes
• Filegroups
• Diagrams
• Views
• Stored procedures
• User-defi ned functions
• Sequences
• Users
• Roles
• Assemblies
• Reports
• Full-text catalogs
• User-defi ned data types
• The Database Object

The database is effectively the highest-level object that you can refer to within a given SQL Server. (Technically speaking, the server itself can be considered to be an object, but not from any real “programming” perspective, so I’m not going there.) Most, but not all, other objects in a SQL Server are children of the database object.

A database is typically a group of constructs that include at least a set of table objects and, more often than not, other objects, such as stored procedures and views that pertain to the particular grouping of data stored in the database’s tables.
What types of tables do you store in just one database, and what goes in a separate database? I’ll discuss that in some detail later in the book, but for now I’ll take the simple approach of saying that any data that is generally thought of as belonging to just one system, or is signifi cantly related, will be stored in a single database. An RDBMS, such as SQL Server, may have multiple databases on just
one server, or it may have only one. The number of databases that reside on an individual SQL
Server depends on such factors as capacity (CPU power, disk I/O limitations, memory, and so on),
autonomy (you want one person to have management rights to the server this system is running
on, and someone else to have admin rights to a different server), and just how many databases your
company or client has. Some servers have only one production database; others have many. Also,
any version of SQL Server that you’re likely to fi nd in production these days has multiple instances
of SQL Server — complete with separate logins and management rights — all on the same physical
server. (SQL Server 2000 was already fi ve years old by the time it was replaced, so I’ll assume most
shops have that or higher.)

When you fi rst load SQL Server, you start with at least four system databases:

• master
• model
• msdb
• tempdb

All of these need to be installed for your server to run properly. (Indeed, without some of them, it won’t run at all.) From there, things vary depending on which installation choices you made. Examples of some of the databases you may see include the following:

• ReportServer: The database that serves Reporting Server confi guration and model storage needs
• ReportServerTempDB: The working database for Reporting Server
• AdventureWorks: The sample database
• AdventureWorksDW: Sample for use with Analysis Services

In addition to the system-installed examples, you may, when searching the web or using other
tutorials, fi nd reference to a couple of older samples:
pubs
Northwind
Because these examples were no longer used in the prior edition of this book, I won’t deal with them
further here, but I still mention them mostly because they carry fond memories from simpler times,
and partly because you might fi nd them out there somewhere.