Datasheet

Chapter 1: Being Objective: Re-Examining Objects in SQL Server
We will utilize this database extensively when reviewing OLAP concepts and taking a look at Analysis
Services. Take a look at the differences between the two databases. They are meant to serve the same
fictional company, but they have different purposes; learn from this.
The Transaction Log
If you’re far enough along in your SQL Server learning to be reading this title, then I would think you
would have at least a basic familiarity with the log. That said, it is among the most misunderstood objects
in SQL Server. Although the data is read from the database, any changes you make don’t initially go to
the database itself. Instead, they are written serially to the transaction log. At some later point in time, the
database is issued a checkpoint — it is at that point in time that all the changes in the log are propagated
to the actual database file.
The database is in a random access arrangement, but the log is serial in nature. While the random nature
of the database file allows for speedy access, the serial nature of the log allows things to be tracked in the
proper order. The log accumulates changes that are deemed as having been committed, and the server
writes the changes to the physical database file(s) at a later time.
We’ll take a much closer look at how things are logged in Chapter 11, but for now, remember that the log
is the first place on disk that the data goes, and it’s propagated to the actual database at a later time. You
need both the database file and the transaction log to have a functional database.
The Most Basic Database Object: Table
Databases are made up of many things, but none are more central to the make-up of a database than
tables. A table is made up of what is called domain data (columns) and entity data (rows). The actual data
for a database is stored in tables. Each table definition contains the metadata (descriptive information
about data) that describes the nature of the data the table is to contain. Each column has its own set of
rules about what can be stored in that column. A violation of the rules of any one column can cause the
system to reject an inserted row or an update to an existing row, or prevent the deletion of a row.
A table can have additional objects associated with it — these objects exist only within the construct of a
particular table (or, in somewhat rare cases, a view). Let’s take a look at each of these.
Indexes
An index is an object that exists only within the framework of a particular table or view. An index works
much like the index does in the back of an encyclopedia; there is some sort of lookup (or ‘‘key’’) value
that is sorted in a particular way, and, once you have that, you are provided another key with which you
can look up the actual information you are after.
An index provides us ways of speeding the lookup of our information. Indexes fall into two categories:
❑ Clustered — You can have only one of these per table. If an index is clustered, it means that the
table on which the clustered index is based is physically sorted according to that index. If you
were indexing an encyclopedia, the clustered index would be the page numbers; the information
in the encyclopedia is stored in the order of the page numbers.
6