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 Fundamentals and 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:
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 Serverprogramming. He knows what works and what does not. He knows what performs andwhat does not. He knows what questions people ask when he is teaching. And he knowswhat people grasp easily and what they don’t when it comes to introducing more complexconcepts of SQL Server programming.
Itzik invited several coauthors to write parts of this book. He does not hesitate to tap theresources of the SQL Server development team when it comes to introducing the newest SQLServer 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 temporalsupport in the relational model, where he pokes at SQL Server developers about usefulnessof PACK and UNPACK relational operators still missing in SQL Server. Greg Low untangled themany ways one can go about tracking access and changes to data and metadata. Both Dejanand 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 hasmany examples and they are all presented in a way that allows you to run them on yourown SQL Server installation. If you don’t have your own SQL Server installation, you can goto http://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 180days). Preferably you should be using the Enterprise or Developer edition of SQL Server torun the examples. And no, you don’t need to retype all code segments in the book! You candownload the source code from http://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 youhave used other SQL supporting products you may want to start with the companion bookInside Microsoft SQL Server 2008: T-SQL Querying. But you can jump right into this book aswell; it will give you great insight into SQL Server–specific programming. You can use theexamples in the book to find out whether you need to study specific statements where SQLServer has a different implementation from your previous experiences and you can use thesebooks for reference.
Even if you are a seasoned SQL Server developer I’m sure this book will show you new andmore efficient ways to perform your tasks. For example, I agree with Dejan that there arefew 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 examplesof 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 aboutCLR recommendations because of performance implications. Itzik not only provides generalperformance guidelines, but he also tells you how long the alternatives took to execute onhis computer. Of course, you will try it on your computer!
Performance considerations are not restricted to CLR. You will find performance improvementtips in every single chapter of this book. For example, in Chapter 7, “Temporary Tables andTable Variables,” you will learn when it is better to use temporary tables and when it is betterto use table variables. Itzik uses simple examples, interpreting query plans and showing howto 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 consideredimplementing special support for temporal data inside SQL Server. The work was intenseand 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.” Whathappened with the project? The implementation was complex and costly. Some of thealternatives were repeatedly re-evaluated without providing a clear winner. And there wasalways a counter-argument—“you can use a workaround.” Whenever this argument waschallenged someone wrote a piece of code showing how a particular temporal task couldbe achieved using existing features in SQL Server. But I don’t know anybody who did ascomplete 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 thebrand new Service Broker in SQL Server 2005. His chapter (Chapter 16) is great reflection ofhis personality—deep with very accurate details in perfect structure. If you are new to ServiceBroker you may want to start reading this chapter from the end, where you will learn whichscenarios you can use Service Broker with, along with a brief comparison of Service Broker withmessaging solutions delivered by Microsoft Message Queue (MSMQ), BizTalk, and WindowsCommunication Foundation (WCF). Bank Itau in Brazil and MySpace are two examples of SQLServer customers who use Service Broker for very different purposes. Bank Itau uses ServiceBroker for batch processing. In MySpace, Service Broker creates a communication fabricamong 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 orseasoned SQL Server user. It is an invaluable reference for developers, data architects, andadministrators.
Lubor KollarGroup Program ManagerSQL Server Customer Advisory TeamMicrosoft, Redmond, Washington U.S.A.
And here’s the book excerpt:
Chapter 3Stored ProceduresItzik Ben-Gan and Dejan Sarka
Stored procedures are executable server-side routines. They give you great power andperformance benefits if used wisely. Unlike user-defined functions (UDFs), stored proceduresare allowed to have side effects; that is, they are allowed to change data in tables, and evenalter object definitions. Stored procedures can be used as a security layer. You can controlaccess to objects by granting execution permissions on stored procedures and not tounderlying objects. You can perform input validation in stored procedures, and you can usestored procedures to allow activities only if they make sense as a whole unit, as opposed toallowing users to perform activities directly against objects.
Stored procedures also give you the benefits of encapsulation; if you need to change theimplementation of a stored procedure because you developed a more efficient way toachieve a task, you can issue an ALTER PROCEDURE statement. As long as the procedure’sinterface remains the same, the users and the applications are not affected. On the otherhand, if you implement your business logic in the client application, the impact of a changecan be very painful.
Stored procedures also provide many important performance benefits. By default, a storedprocedure will reuse a previously cached execution plan, saving the CPU resources andthe time it takes to parse, resolve, and optimize your code. Network traffic is minimizedby shortening the code strings that the client submits to Microsoft SQL Server—the clientsubmits 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 betweenthe client and the server. The stored procedure passes only the final result to the clientthrough the network.
This chapter explores stored procedures. It starts with brief coverage of the different types ofstored procedures supported by SQL Server 2008 and then delves into details. The chaptercovers the stored procedure’s interface, resolution process, compilation, recompilations andexecution 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, andextended. You can develop user-defined stored procedures with T-SQL or with the CLR. Thissection briefly covers the different types.
User-Defined Stored Procedures
A user-defined stored procedure is created in a user database and typically interacts with thedatabase 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 inthe InsideTSQL2008 database:
IF OBJECT_ID('dbo.GetSortedShippers', 'P') IS NOT NULL DROP PROC dbo.GetSortedShippers;GO-- Stored procedure GetSortedShippers-- Returns shippers sorted by requested sort columnCREATE PROC dbo.GetSortedShippers @colname AS sysname = NULLAS
DECLARE @msg AS NVARCHAR(500);
-- Input validationIF @colname IS NULLBEGIN 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 columnIF @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 theInsideTSQL2008 database as input (@colname); after input validation, it returns the rows fromthe Shippers table sorted by the specified column name. Input validation here involves verifyingthat a column name was specifi ed, and that the specified column name exists in the Shipperstable. 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 thefollowing 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-01361 Shipper GVSUA (503) 555-01373 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 schemaname (dbo in our case), but when you neglect to specify it, SQL Server must resolve theschema. The resolution in SQL Server 2008 occurs in the following order (adapted from SQLServer Books Online):
For example, suppose that you connect to the InsideTSQL2008 database and youruser’s default schema in InsideTSQL2008 is called Sales. You invoke the following codein a batch:
EXEC GetSortedShippers @colname = N'companyname';
The resolution takes place in the following order:
As I mentioned earlier, you can use stored procedures as a security layer. You can controlaccess to objects by granting execution permissions on stored procedures and not onunderlying objects. For example, suppose that there’s a database user called user1 inthe InsideTSQL2008 database. You want to allow user1 to invoke the GetSortedShippersprocedure, but you want to deny user1 direct access to the Shippers table. You can achievethis by granting the user EXECUTE permissions on the procedure, and denying SELECT (andpossibly 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 querythe Shippers table directly:
SELECT shipperid, companyname, phoneFROM 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 beallowed to perform.
I’d like to point out other aspects of stored procedure programming through theGetSortedShippers sample procedure:
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, theschema-qualified name of the procedure, and the parameter values for the invocation ifthere are any. References in the stored procedure to system and user object names that arenot fully qualified (that is, without the database prefix) are always resolved in the database inwhich the procedure was created. If you want to invoke a user-defined procedure created inanother database, you must database-qualify its name. For example, if you are connected toa database called db1 and want to invoke a stored procedure called dbo.Proc1, which residesin 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 namesthat are not fully qualified would be resolved in the database in which the procedure wascreated (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 NULLDROP PROC dbo.GetSortedShippers;
You can get the full Chapter 3 here.
Thanks. Looks like it's a very clearly-written text.