Datasheet

AdventureWorksDW

This is the Analysis Services sample. (The DW stands for data warehouse, which is the type of database

over which most Analysis Services projects will be built.) Perhaps the greatest thing about it is that

Microsoft had the foresight to tie the transaction database sample with the analysis sample, providing

a whole set of samples that show the two of them working together.

Decision support databases are well outside the scope of this book, and you won’t be using this database,

but keep it in mind as you fire up Analysis Services and play around. 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

Believe it or not, the database file itself isn’t where most things happen. Although the data is certainly

read in from there, any changes you make don’t initially go to the database itself. Instead, they are writ-

ten 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 12, 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 can be thought of as equating to an accountant’s ledger or an Excel spreadsheet. It is made

up of what is called

domain data (columns) and entity data (rows). The actual data for the database is

stored in the tables.

Each table definition also contains the

metadata (descriptive information about data) that describes the

nature of the data it 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.

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.

Being Objective: Re-Examining Objects in SQL Server

04_584340 ch01.qxp 10/18/06 2:11 PM Page 5