Datasheet

❑ Views that reference multiple tables generally perform much faster with an indexed view
because the join between the tables is preconstructed.
❑ Aggregations performed in the view are precalculated and stored as part of the index; again,
this means that the aggregation is performed one time (when the row is inserted or updated),
and then can be read directly from the index information.
❑ Inserts and deletes have higher overhead because the index on the view has to be updated
immediately; updates also have higher overhead if the key column of the index is affected by
the update.
We will look into these performance issues more deeply in Chapter 9.
Stored Procedures
Stored procedures (or sprocs) are historically and, in the .NET era, even more likely to be the bread and
butter of programmatic functionality in SQL Server. Stored procedures are generally an ordered series of
Transact-SQL (the language used to query Microsoft SQL Server) statements bundled up into a single
logical unit. They allow for variables and parameters as well as selection and looping constructs. Sprocs
offer several advantages over just sending individual statements to the server in the sense that they:
❑ Are referred to using short names, rather than a long string of text; as such, less network traffic
is required in order to run the code within the sproc.
❑ Are pre-optimized and precompiled, saving a small amount of time each time the sproc is run.
❑ Encapsulate a process, usually for security reasons or just to hide the complexity of the database.
❑ Can be called from other sprocs, making them reusable in a somewhat limited sense.
In addition, you can utilize any .NET language to add program constructs, beyond those native to
T-SQL, to your stored procedures.
User-Defined Functions
User-defined functions (or UDFs) have a tremendous number of similarities to sprocs, except that they:
❑ Can return a value of most SQL Server data types. Excluded return types include text, ntext,
image, cursor, and timestamp.
❑ Can’t have “side effects.” Basically, they can’t do anything that reaches outside the scope of the
function, such as changing tables, sending e-mails, or making system or database parameter
changes.
UDFs are similar to the functions that you would use in a standard programming language such as
VB.NET or C++. You can pass more than one variable in, and get a value out. SQL Server’s UDFs vary
from the functions found in many procedural languages, however, in that
all variables passed into the
function are passed in by value. If you’re familiar with passing in variables
By Ref in VB, or passing in
pointers in C++, sorry, there is no equivalent here. There is, however, some good news in that you can
return a special data type called a table. We’ll examine the impact of this in Chapter 11.
9
Being Objective: Re-Examining Objects in SQL Server
04_584340 ch01.qxp 10/18/06 2:11 PM Page 9