New book: Inside Microsoft SQL Server 2008: T-SQL Programming
We’re pleased to announce that Inside Microsoft SQL Server 2008: T-SQL Programming, by Itzik Ben-Gan, Dejan Sarka, Roger Wolter, Greg Low, Ed Katibah, and Isaac Kunen, is now available!
The book’s ISBN is 9780735626027, and it’s 832 pages. This book follows Itzik’s well-received Microsoft SQL Server 2008 T-SQL Fundamentalsand Inside Microsoft SQL Server 2008: T-SQL Querying , by Itzik, Dejan, Lubor Kollar, and Steve Kass.
In this post, we’ll share the book’s Table of Contents, Foreword, and an excerpt from Chapter 3, “Stored Procedures.”
The book’s full Table of Contents can be found here.
And here’s the Foreword, by Microsoft’s Lubor Kollar:
Foreword
Let me start with a few words about the lead author of this book, Itzik Ben-Gan. He is a mentor, consultant, presenter, teacher, and writer. All his roles have a common theme—Microsoft SQL Server programming. But wait—there’s even more: he is an MVP (officially “Microsoft Valued Professional,” but often interpreted as Most Valuable Programmer) and a close partner with many SQL Server developers in Redmond, Washington. The combination of these traits puts Itzik into a unique position when writing a book about SQL Server
programming. He knows what works and what does not. He knows what performs and
what does not. He knows what questions people ask when he is teaching. And he knows
what people grasp easily and what they don’t when it comes to introducing more complex
concepts of SQL Server programming.
Itzik invited several coauthors to write parts of this book. He does not hesitate to tap the
resources of the SQL Server development team when it comes to introducing the newest SQL
Server technologies. This was the case with spatial data chapter written by Ed Katibah (a.k.a.
“Spatial Ed”) and Isaac Kunen and with the Service Broker chapter written by Roger Wolter.
Dejan Sarka helped with CLR and XML, and contributed the fascinating chapter on temporal
support in the relational model, where he pokes at SQL Server developers about usefulness
of PACK and UNPACK relational operators still missing in SQL Server. Greg Low untangled the
many ways one can go about tracking access and changes to data and metadata. Both Dejan
and Greg are SQL Server veterans and Itzik’s colleagues in Solid Quality Mentors.
I personally believe in hands-on learning when it comes to programming. This book has
many examples and they are all presented in a way that allows you to run them on your
own SQL Server installation. If you don’t have your own SQL Server installation, you can go
to https://www.microsoft.com/sql and download the evaluation version of SQL Server 2008
(you must have a Windows Live ID; the evaluation version is Enterprise and it is good for 180
days). Preferably you should be using the Enterprise or Developer edition of SQL Server to
run the examples. And no, you don’t need to retype all code segments in the book! You can
download the source code from https://www.InsideTSQL.com.
If you are new to the SQL language you should start with the earlier published book,
Microsoft SQL Server 2008: T-SQL Fundamentals. If you are new to SQL Server but you
have used other SQL supporting products you may want to start with the companion book
Inside Microsoft SQL Server 2008: T-SQL Querying. But you can jump right into this book as
well; it will give you great insight into SQL Server–specific programming. You can use the
examples in the book to find out whether you need to study specific statements where SQL
Server has a different implementation from your previous experiences and you can use these
books for reference.
Even if you are a seasoned SQL Server developer I’m sure this book will show you new and
more efficient ways to perform your tasks. For example, I agree with Dejan that there are
few CLR UDTs in production systems. And this is not only true for UDTs—few UDFs, triggers,
and stored procedures are written in CLR languages. The book provides numerous examples
of C# and Microsoft Visual Basic solutions. Most of the examples are presented in both C#
and Visual Basic, which are the most popular CLR languages. The authors are careful about
CLR recommendations because of performance implications. Itzik not only provides general
performance guidelines, but he also tells you how long the alternatives took to execute on
his computer. Of course, you will try it on your computer!
Performance considerations are not restricted to CLR. You will find performance improvement
tips in every single chapter of this book. For example, in Chapter 7, “Temporary Tables and
Table Variables,” you will learn when it is better to use temporary tables and when it is better
to use table variables. Itzik uses simple examples, interpreting query plans and showing how
to use IO counters when comparing different solutions for the same task.
I mentioned that Chapter 12— Dejan’s “Temporal Support in the Relational Model”
chapter—is fascinating. Why? Let me share a little secret. Some time ago we considered
implementing special support for temporal data inside SQL Server. The work was intense
and the SQL Server development team got help from leading academic sources as well.
One development lead even personalized the license plate on his car to “TIME DB.” What
happened with the project? The implementation was complex and costly. Some of the
alternatives were repeatedly re-evaluated without providing a clear winner. And there was
always a counter-argument—“you can use a workaround.” Whenever this argument was
challenged someone wrote a piece of code showing how a particular temporal task could
be achieved using existing features in SQL Server. But I don’t know anybody who did as
complete a job as Dejan in Chapter 12 of this book!
I worked with Roger Wolter on the same team when he was responsible for developing the
brand new Service Broker in SQL Server 2005. His chapter (Chapter 16) is great reflection of
his personality—deep with very accurate details in perfect structure. If you are new to Service
Broker you may want to start reading this chapter from the end, where you will learn which
scenarios you can use Service Broker with, along with a brief comparison of Service Broker with
messaging solutions delivered by Microsoft Message Queue (MSMQ), BizTalk, and Windows
Communication Foundation (WCF). Bank Itau in Brazil and MySpace are two examples of SQL
Server customers who use Service Broker for very different purposes. Bank Itau uses Service
Broker for batch processing. In MySpace, Service Broker creates a communication fabric
among hundreds of SQL Servers behind the MySpace.com social networking site.
I’m confident you will find this book useful and worth reading whether you are a new or
seasoned SQL Server user. It is an invaluable reference for developers, data architects, and
administrators.
Lubor Kollar
Group Program Manager
SQL Server Customer Advisory Team
Microsoft, Redmond, Washington U.S.A.
And here’s the book excerpt:
Chapter 3
Stored Procedures
Itzik Ben-Gan and Dejan Sarka
Stored procedures are executable server-side routines. They give you great power and
performance benefits if used wisely. Unlike user-defined functions (UDFs), stored procedures
are allowed to have side effects; that is, they are allowed to change data in tables, and even
alter object definitions. Stored procedures can be used as a security layer. You can control
access to objects by granting execution permissions on stored procedures and not to
underlying objects. You can perform input validation in stored procedures, and you can use
stored procedures to allow activities only if they make sense as a whole unit, as opposed to
allowing users to perform activities directly against objects.
Stored procedures also give you the benefits of encapsulation; if you need to change the
implementation of a stored procedure because you developed a more efficient way to
achieve a task, you can issue an ALTER PROCEDURE statement. As long as the procedure’s
interface remains the same, the users and the applications are not affected. On the other
hand, if you implement your business logic in the client application, the impact of a change
can be very painful.
Stored procedures also provide many important performance benefits. By default, a stored
procedure will reuse a previously cached execution plan, saving the CPU resources and
the time it takes to parse, resolve, and optimize your code. Network traffic is minimized
by shortening the code strings that the client submits to Microsoft SQL Server—the client
submits only the stored procedure’s name and its arguments, as opposed to the full code.
Moreover, all the activity is performed at the server, avoiding multiple roundtrips between
the client and the server. The stored procedure passes only the final result to the client
through the network.
This chapter explores stored procedures. It starts with brief coverage of the different types of
stored procedures supported by SQL Server 2008 and then delves into details. The chapter
covers the stored procedure’s interface, resolution process, compilation, recompilations and
execution plan reuse, plan guides, the EXECUTE AS clause, and common language runtime
(CLR) stored procedures.
Types of Stored Procedures
SQL Server 2008 supports different types of stored procedures: user-defined, system, and
extended. You can develop user-defined stored procedures with T-SQL or with the CLR. This
section briefly covers the different types.
User-Defined Stored Procedures
A user-defined stored procedure is created in a user database and typically interacts with the
database objects. When you invoke a user-defined stored procedure, you specify the EXEC
(or EXECUTE) command and the stored procedure’s schema-qualified name and arguments:
EXEC dbo.Proc1 <arguments>;
As an example, run the following code to create the GetSortedShippers stored procedure in
the InsideTSQL2008 database:
USE InsideTSQL2008;
IF OBJECT_ID('dbo.GetSortedShippers', 'P') IS NOT NULL
DROP PROC dbo.GetSortedShippers;
GO
-- Stored procedure GetSortedShippers
-- Returns shippers sorted by requested sort column
CREATE PROC dbo.GetSortedShippers
@colname AS sysname = NULL
AS
DECLARE @msg AS NVARCHAR(500);
-- Input validation
IF @colname IS NULL
BEGIN
SET @msg = N'A value must be supplied for parameter @colname.';
RAISERROR(@msg, 16, 1);
RETURN;
END
IF @colname NOT IN(N'shipperid', N'companyname', N'phone')
BEGIN
SET @msg =
N'Valid values for @colname are: '
+ N'N''shipperid'', N''companyname'', N''phone''.';
RAISERROR(@msg, 16, 1);
RETURN;
END
-- Return shippers sorted by requested sort column
IF @colname = N'shipperid'
SELECT shipperid, companyname, phone
FROM Sales.Shippers
ORDER BY shipperid;
ELSE IF @colname = N'companyname'
SELECT shipperid, companyname, phone
FROM Sales.Shippers
ORDER BY companyname;
ELSE IF @colname = N'phone'
SELECT shipperid, companyname, phone
FROM Sales.Shippers
ORDER BY phone;
GO
The stored procedure accepts a column name from the Sales.Shippers table in the
InsideTSQL2008 database as input (@colname); after input validation, it returns the rows from
the Shippers table sorted by the specified column name. Input validation here involves verifying
that a column name was specifi ed, and that the specified column name exists in the Shippers
table. Later in the chapter, I will discuss the subject of parameterizing sort order in more detail;
for now, I just wanted to provide a simple example of a user-defined stored procedure. Run the
following code to invoke GetSortedShippers specifying N’companyname’ as input:
EXEC dbo.GetSortedShippers @colname = N'companyname';
This generates the following output:
shipperid companyname phone
----------- ------------------ ---------------
2 Shipper ETYNR (425) 555-0136
1 Shipper GVSUA (503) 555-0137
3 Shipper ZHISN (415) 555-0138
You can leave out the keyword EXEC if the stored procedure is the first statement of a batch,
but I recommend using it all the time. You can also omit the stored procedure’s schema
name (dbo in our case), but when you neglect to specify it, SQL Server must resolve the
schema. The resolution in SQL Server 2008 occurs in the following order (adapted from SQL
Server Books Online):
- The sys schema of the current database.
- The caller’s default schema if executed in a batch or in dynamic SQL. Or, if the
nonqualified procedure name appears inside the body of another procedure definition,
the schema containing this other procedure is searched next. - The dbo schema in the current database.
For example, suppose that you connect to the InsideTSQL2008 database and your
user’s default schema in InsideTSQL2008 is called Sales. You invoke the following code
in a batch:
EXEC GetSortedShippers @colname = N'companyname';
The resolution takes place in the following order:
- Look for GetSortedShippers in the sys schema of InsideTSQL2008 (sys.
GetSortedShippers). If found, execute it; if not, proceed to the next step (as in our case). - If invoked in a batch (as in our case) or dynamic SQL, look for GetSortedShippers in
Sales (Sales.GetSortedShippers). Or, if invoked in another procedure (say, Production.
AnotherProc), look for GetSortedShippers in Production next. If found, execute it; if not,
proceed to the next step (as in our case). - Look for GetSortedShippers in the dbo schema (dbo.GetSortedShippers). If found (as in
our case), execute it; if not, generate a resolution error.
As I mentioned earlier, you can use stored procedures as a security layer. You can control
access to objects by granting execution permissions on stored procedures and not on
underlying objects. For example, suppose that there’s a database user called user1 in
the InsideTSQL2008 database. You want to allow user1 to invoke the GetSortedShippers
procedure, but you want to deny user1 direct access to the Shippers table. You can achieve
this by granting the user EXECUTE permissions on the procedure, and denying SELECT (and
possibly other) permissions on the table, as in:
DENY SELECT ON Sales.Shippers TO user1;
GRANT EXECUTE ON dbo.GetSortedShippers TO user1;
SQL Server allows user1 to execute the stored procedure. However, if user1 attempts to query
the Shippers table directly:
SELECT shipperid, companyname, phone
FROM Sales.Shippers;
SQL Server generates the following error:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Shippers', database 'InsideTSQL2008', schema
'Sales'.
This security model gives you a high level of control over the activities that users will be
allowed to perform.
I’d like to point out other aspects of stored procedure programming through the
GetSortedShippers sample procedure:
- Notice that I explicitly specified column names in the query and didn’t use SELECT *.
Using SELECT * is a bad practice. In the future, the table might undergo schema changes
that cause your application to break. Also, if you really need only a subset of the table’s
columns and not all of them, the use of SELECT * prevents the optimizer from utilizing
covering indexes defined on that subset of columns. - The query is missing a filter. This is not a bad practice by itself—it’s perfectly valid
if you really need all rows from the table. But you might be surprised to learn that
in performance-tuning projects at Solid Quality Mentors, we still find production
applications that need filtered data but filter it only at the client. Such an approach
introduces extreme pressure on both SQL Server and the network. Filters allow the
optimizer to consider using indexes, which minimizes the I/O cost. Also, by filtering at
the server, you reduce network traffic. If you need filtered data, make sure you filter it
at the server; use a WHERE clause (or ON, HAVING where relevant)! - Notice the use of a semicolon (;) to suffix statements. Although not a requirement of
T-SQL for all statements, the semicolon suffix is an ANSI requirement. In SQL Server
2008, you are required to suffix some statements with a semicolon to avoid ambiguity
of your code. For example, the WITH keyword is used for different purposes—to define
a CTE, to specify a table hint, and so on. SQL Server requires you to suffix the statement
preceding the CTE’s WITH clause to avoid ambiguity. Similarly, the MERGE keyword is
used for different purposes—to specify a join hint and to start a MERGE statement.
SQL Server requires you to terminate a MERGE statement with a semicolon to avoid
ambiguity. Getting used to suffixing all statements with a semicolon is a good practice.
Now let’s get back to the focus of this section—user-defined stored procedures.
As I mentioned earlier, to invoke a user-defined stored procedure, you specify EXEC, the
schema-qualified name of the procedure, and the parameter values for the invocation if
there are any. References in the stored procedure to system and user object names that are
not fully qualified (that is, without the database prefix) are always resolved in the database in
which the procedure was created. If you want to invoke a user-defined procedure created in
another database, you must database-qualify its name. For example, if you are connected to
a database called db1 and want to invoke a stored procedure called dbo.Proc1, which resides
in db2, you would use the following code:
USE db1;
EXEC db2.dbo.Proc1 <arguments>;
Invoking a procedure from another database wouldn’t change the fact that object names
that are not fully qualified would be resolved in the database in which the procedure was
created (db2, in this case).
If you want to invoke a remote stored procedure residing in another instance of SQL Server,
you would use the fully qualified stored procedure name, including the linked server name:
server.database.schema.proc.
When done, run the following code for cleanup:
IF OBJECT_ID('dbo.GetSortedShippers', 'P') IS NOT NULL
DROP PROC dbo.GetSortedShippers;
You can get the full Chapter 3 here.