Datasheet

In this section you look at each of these, getting a flavor for them but without going into too much depth

at this stage.

Joins

In the earlier relationship discussion, it may have seemed like accessing related data from multiple

tables might involve a convoluted procedure. In actual fact — luckily for us — that isn’t the case. It is

possible to fetch data from multiple tables simultaneously, and end up with a single set of results. The

mechanism for doing this involves joins, of which there are several types. A join is a way to specify a

relationship between two tables to obtain related data from both. A join between a product table and a

category table, for example, enables you to obtain all the products belonging to a single category in one

operation. This is something that you’ll see in action after you’ve learned a bit more about the language

used to execute database queries — Structured Query Language (SQL).

Functions

Any good DBMS supplies you with an extensive set of functions to use to view and manipulate data.

You are likely to find mathematical functions, conversion functions, string manipulation functions, date

and time manipulation functions, and so on. These enable you to perform much of your data processing

inside the DBMS, reducing the amount of data that needs to be transferred to and from your applica-

tions, and improving efficiency.

DBMS functions can take several forms. There are scalar functions that return single values, table valued

functions that can return multiple rows of data, and aggregate functions that work with entire data sets

rather than individual values. Aggregate functions include those with capabilities to obtain the maxi-

mum value in a given column of a table, perform statistical analysis, and so on.

Another type of function that you will probably find yourself using at some point is the user-defined

function. As its name suggests, you can create your own function to perform whatever task you like.

User-defined functions may be scalar, table valued, or aggregate.

There is one more important feature of functions as used in SQL Server 2005 — it is possible to write

them in C# code that runs (managed) inside the database. This is something you’ll see in action later in

this book.

Views

There are some database operations that you might want to repeat often within your applications, such

as those involving joins, as detailed earlier. Rather than forcing the DBMS to combine data from multiple

sources, often transforming the data along the way, it is possible to store a view of the data in the DBMS.

A view is a stored query that obtains data from one or more tables in the database. For example, a view

might be a query that obtains a list of products that include all product columns and the name of the cat-

egory in an additional column. The client applications don’t have to make more complicated queries

involving joins to obtain this information, because it is already combined in the view. The view looks

and behaves identically to a table in every way except that it doesn’t actually contain any data; instead,

it provides an indirect way to access data stored elsewhere in the database.

Apart from the obvious advantage of a view — that querying the underlying data is simplified for client

applications — there is another important point to note. By telling the DBMS how the data is to be used

Database Fundamentals

44063c01.qxd:WroxBeg 9/12/06 10:31 PM Page 9