Datasheet

Chapter 1: Being Objective: Re-Examining Objects in SQL Server
We will look more deeply into these performance issues as well as other special uses for views in
Chapter 8.
It is important to note that, while the code to create an indexed view will work in all editions, the query
optimizer will only consider an indexed view when used in the Enterprise Edition of the product.
Stored Procedures
Stored procedures (or sprocs) are historically 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 create assemblies and 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 . Note that you can return varchar(max) and varbinary(max)
values.
❑ 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 10.
Users and Roles
These two go hand in hand. Users are pretty much the equivalent of logins. In short, this object repre-
sents an identifier for someone to log in to the SQL Server. Anyone logging in to SQL Server has to map
(directly or indirectly depending on the security model in use) to a user. Users, in turn, belong to one or
10