Datasheet

Filegroups

By default, all your tables and everything else about your database (except the log) are stored in a single

file. That file is a member of what’s called the

primary filegroup. However, you are not stuck with this

arrangement.

SQL Server allows you to define a little over 32,000

secondary files. (If you need more than that, perhaps it

isn’t SQL Server that has the problem.) These secondary files can be added to the primary filegroup or

created as part of one or more

secondary filegroups. While there is only one primary filegroup (and it is

actually called “Primary”), you can have up to 255 secondary filegroups. A secondary filegroup is cre-

ated as an option to a

CREATE DATABASE or ALTER DATABASE command.

Diagrams

We will discuss database diagramming in some detail when we discuss database design, but for now, suf-

fice it to say that a database diagram is a visual representation of the database design, including the various

tables, the column names in each table, and the relationships between tables. In your travels as a developer,

you may have heard of an

entity-relationship diagram— or ERD. In an ERD the database is divided into two

parts: entities (such as “supplier” and “product”) and relations (such as “supplies” and “purchases”).

Although they have been entirely redesigned with SQL Server 2005, the included database design tools

remain a bit sparse. Indeed, the diagramming methodology the tools use doesn’t adhere to any of the

accepted standards in ER diagramming.

Still, these diagramming tools really do provide all the “necessary” things; they are at least something

of a start.

Figure 1-1 is a diagram that shows some of the various tables in the AdventureWorks database. The dia-

gram also (though it may be a bit subtle since this is new to you) describes many other properties about

the database. Notice the tiny icons for keys and the infinity sign. These depict the nature of the relation-

ship between two tables.

Views

A view is something of a virtual table. A view, for the most part, is used just like a table, except that it

doesn’t contain any data of its own. Instead, a view is merely a preplanned mapping and representation

of the data stored in tables. The plan is stored in the database in the form of a query. This query calls for

data from some, but not necessarily all, columns to be retrieved from one or more tables. The data

retrieved may or may not (depending on the view definition) have to meet special criteria in order to be

shown as data in that view.

Until SQL Server 2000, the primary purpose of views was to control what the user of the view saw. This

has two major impacts: security and ease of use. With views you can control what the users see, so if

there is a section of a table that should be accessed by only a few users (for example, salary details), you

can create a view that includes only those columns to which everyone is allowed access. In addition, the

view can be tailored so that the user doesn’t have to search through any unneeded information.

Being Objective: Re-Examining Objects in SQL Server

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