Professional SQL Server™ 2005 Programming

8.1. SQL Server Storage

Data in SQL Server can be thought of as existing in something of a hierarchy of structures. The hierarchy is pretty simple. Some of the objects within the hierarchy are things that you will deal with directly and will therefore know easily. A few others exist under the cover, and while they can be directly addressed in some cases, they usually are not. Take a look at them one by one.

8.1.1. The Database

Okay — this one is easy. I can just hear people out there saying, "Duh! I knew that." Yes, you probably did, but I point it out as a unique entity here because it is the highest level of the definition of storage (for a given server). This is the highest level that a lock can be established at, although you cannot explicitly create a database level lock.

A lock is something of both a hold and a place marker that is used by the system. We will be looking into locking extensively in Chapter 12, but we will see the lockability of objects within SQL Server discussed in passing as we look at storage.

8.1.2. The File

By default, your database has two files associated with it:

• The first is the primary physical database file — that's where your data is ultimately stored. This file should be named with a *.mdf extension(this is a recommendation, not a requirement — but I think you'll find doing it in other ways will become confusing over time).

• The second is something of an offshoot to the database file — the log. We'll dive into the log quite a bit when we deal ...