Datasheet

in this way, the DBMS is capable of optimizing things further for you. It might, for example, cache view
data so that retrieving its compound information becomes much faster than querying individual tables
might be.
In addition, views can be defined in some quite complicated ways using functions, including user-
defined functions, such that your applications can retrieve highly processed data with ease.
Stored Procedures
Stored procedures (often called sprocs) are an extremely important part of database programming —
despite the fact that you could use a fully functioning database without ever using a sproc. Stored proce-
dures enable you to write code that runs inside the database, capable of advanced manipulation and sta-
tistical analysis of data. Perhaps more important, their operation is optimized by the DBMS, meaning
they can complete their tasks quickly. In addition, long-running stored procedures can carry on unat-
tended inside the database while your applications are doing other things. You can even schedule them
to run at regular intervals in some DBMSes.
Stored procedures don’t do anything that you couldn’t do by other means — for example, in C# code.
However, for some operations that work with large quantities of data, it might mean transferring the
data into application memory and then processing it to get a result. With stored procedures the data
never has to leave the database, and only the result needs transferring to your application. With remote
databases this can provide a significant performance boost.
Some DBMSes — such as SQL Server — provide you with a rich set of operations that you can use when
programming stored procedures. These include cursors that you can position within sets of data to
process rows sequentially, branching and looping logic, variables, and parameters. And as with func-
tions, SQL Server lets you write stored procedures in managed C# code.
Triggers
A trigger is a specialized form of stored procedure that is executed automatically by the DBMS when
certain events happen, rather than being called manually by client applications. In practice, this means
defining an event that will occur at some later date (“when a new row is added to table X,” for example),
and then telling the DBMS to execute a certain stored procedure when that event occurs.
Triggers aren’t as commonly used as some other features of DBMSes, but when they are, it’s because
they are the only solution to a problem, so it’s good to have them. They are typically used to log or audit
database access.
E-mail
Some DBMSes are capable of sending e-mails independently of other applications. This can be useful,
especially when combined with triggers. It enables you to keep tabs on data in a database, as well as per-
mitting more advanced scenarios. When orders are placed, for example, you could generate and send
e-mails to customers directly from the DBMS with no external coding required. The only limitation here
is that a mail server such as a simple mail transfer protocol (SMTP) server is likely to be required.
10
Chapter 1
44063c01.qxd:WroxBeg 9/12/06 10:31 PM Page 10