Database Reference
In-Depth Information
The really cool thing is that the MERGE syntax works with single or
multiple rows of source data, so our single MERGE statement can insert,
update, or even delete address records from the address table for the spec-
ified customer. Again, we are not saying that you have to use MERGE; you
can build all the logic in separate steps, but a single T-SQL statement is
likely to be more efficient than anything you can write. We highly recom-
mend that you look at MERGE in further detail.
Other Components of an Abstraction Layer
Although views and stored procedures will make up the bulk of your ex-
posed abstraction layer, you will use many other SQL Server objects in
your unexposed abstraction layer. First, let's define exposed versus unex-
posed portions of your abstraction layer. Simply put, anything that is meant
to be consumed by your users or an application is
exposed;
anything con-
sumed only by other elements of your abstraction layer is
unexposed.
For example, user-defined functions are extremely useful in a SQL
Server database, but they typically perform small tasks as part of a larger
process. Therefore, UDFs are generally called by stored procedures or
used in a view and are not usually exposed to users or applications directly.
As you build your abstraction layer you may find that a UDF or a trigger is
appropriate for handling a task, and in these situations you should take full
advantage of these objects. Just keep in mind that they will probably be
called by another object and not the user.
If you take nothing else away from this topic, remember that abstraction
layers are extremely important. They provide extensibility and flexibility
unrivaled by databases that lack abstraction layers. In addition, abstraction
layers provide you with an extra layer of security and are handy during your
next Sarbanes-Oxley audit.
At this point, we should have a complete database solution and our
customer should be ready to begin using the database. We hope that if you
follow the guidelines we have laid out in this topic, you will be able to de-
sign and implement successful databases. Remember that there are many
ways to accomplish the same goals, so take what you have learned here and
incorporate it into your own practices and techniques.
Search WWH ::
Custom Search