SQL Server 2005 T-SQL Recipes: A Problem-Solution by Joseph Sack

SQL Server 2005 T-SQL Recipes: A Problem-Solution

  • Author: Joseph Sack
  • Softcover: 768 pages
  • Publisher: Apress(Dec 6 2005 12:00AM)
  • Book dimensions: 9.2 x 7.1 x 1.5 inches
  • ISBN: 159059570X
  • ITBookworm score: 4.5 /5

Score Reasoning

Very few books are perfect. I found this book left me wanting more. I would have really liked it to have more explanation, and possibly even more theory… especially for the new T-SQL constructs for development. It’s an excellent book, but it did leave me wanting more. It’s billed as a reference for syntax, which it does very well, but most of the info can be found in BOL, it’s just easier to find here.


This book is kinda more like BOL that’s been reorganized by topic than it is a problem-solution approach. It goes a little beyond what you get in BOL sometimes, which can give you a leg up in understanding why you have to do the things you do to make them work. It’s an excellent book that will serve as a good reference on your bookshelf. Can most of the content be found in BOL? Yes. However, it is much better organized and it has an outstanding index so you can find specific syntax very quickly and get a short explanation of how it works. It’s not a very interesting read, so I really wouldn’t try reading it cover to cover. Each chapter is completely autonomous, so you can just look up the syntax you need at the time without having to rely on knowledge from previous chapters. It’s really an excellent reference that deserves a place on your desk. I use it about 3 times a week for one thing or another. One more thing I think is worth mentioning. I’ve timed myself looking up topics in BOL and in this book, and more often than not, this book was much easier to look things up in, and a little faster too. In some cases it was actually much faster. That alone is worth the price.

Who is this book really for?

This book is for DBAs or developers who want to have a nicely organized reference to the new and standard syntax in Yukon. BOL can put you through the ringer sometimes when it comes to trying to find the exact info you’re looking for, but this organizes everything by topic so you can easily find what you’re looking for.

Writing Style

This is really done in the style of BOL and doesn’t really have much of a style. If it weren’t merely supposed to be a reference I’d be upset at the lack of personality here, but since it is, it’s just fine.


This is another really solid book. I took a much longer time to review this one and I abused it the entire time and it’s still holding up just fine. I managed to keep this one away from my son (1yr old) this time, and even though I carried it in my backpack for weeks with the spine bent backwards, it’s not coming apart at all. It’s a good quality book.

Table of Contents

Chapter 1: SELECT

Here we start out talking about the basics of the select statement. You get into the basic where clause, operators, NULLS, ordering, etc. You also get into grouping, distinct, subqueries, and many others. It’s a good first chapter that sets the tone for the level of the explanations given in future chapters. It starts out with a basic explanation of what problem you’re trying to solve, then a code sample, and then an explanation of how it works. It goes far beyond just the basic select though. It gets into joins, and new features like outer apply, and tablesample. The section that explains how tablesample works is well worth the price of the book. I recently reviewed Pro SQL Server 2005 by APress, and it also discusses tablesample, but only speculates as to how it works, but this book tells you what’s actually going on, and why you see unexpected results sometimes. Of course, I found this info very easily in BOL, so you just have to look some of this stuff up, but the book explains it very succinctly and makes a good reference. Along with the basics, it also gets into PIVOT, CTEs, and a lot of other topics. I really think the PIVOT operator could have been explained a little more, but if you’ve got decent experience already, you’ll be able to get through it.


I wish I could say there were amazing pearls of wisdom here, but that’s just not the case. It’s not the author’s fault though, there just isn’t that much to say about DML. It’s a good chapter though that will bring a novice up to speed on these statements. It also covers the new OUTPUT syntax for Yukon.

Chapter 3: Transactions, Locking, Blocking, and Deadlocking

This chapter is just what it sounds like, a primer on all the topics listed in the title. It starts out with defining ACID and talking about explicit vs implicit transactions. You also get a bit of management sprinkled in there with a brief discussion of DBCC OPENTRAN and its usage. Next, concurrency and locking issues are covered. It’s a good refresher for those who may not deal with these topics very often and need to bone up, or just want to pick it up in passing looking for something else. It really doesn’t go into as much detail as I would have liked though, but it goes give some very good code examples, and since that’s what the book is about, then I suppose I can let it slide. Blocking is covered next, and again, I really would have liked to have seen some more detail on how the different blocking hints relate to each other, and what the effects of using some of them could be, but it covers the basics. I just think that this would be the perfect place to chase down discussions like that since the person reading it will be looking for blocking-related topics specifically here. Deadlocks are next and covered quite nicely. You get to troubleshoot with trace flags, and set deadlock priority, so all’s well.

Chapter 4: Tables

This is actually a much bigger chapter than I thought it would be. It really does cover all things table. Starting out with the basics, it discussed CREATE syntax as well as an explanation of most of the data types. Why it couldn’t discuss all of them I’ll never know, but whatever. It then extends the basics into syntax for altering tables. I think pretty much every situation is covered from creating and altering PKs to dealing with identity. It also has a brief section on collation, and I’ve interviewed plenty of DBAs who had no idea what collation is, so listen up guys… get this book, and skim ch. 4. It really does give a lengthy discussion on PKs and FKs. All the different topics are covered, so you’ll be well served by this chapter on that topic. There are many more topics on table basics that are covered, but I’m not going to list them all here. Next, we get into #tables and @tables. It’s not a huge discussion, but you’ve got the syntax there, so you can at least work with them. Partitioning is the next big topic. There’s plenty of time given to this very new topic. It discusses how to create, manage, and modify partitions and schemas. I think it could have gone into a little more detail on the specifics of placing data in the different partitions, but it’s adequate. I just walked away really wanting a little bit more.

Chapter 5: Indexes

Here we start out with a very brief overview of B-tree structures with a couple diagrams. Nothing special there. You then get right into the CREATE INDEX syntax with examples for different situations. Disabling and dropping indexes is covered next, and given enough time to be useful. Next, a little time is given to controlling and improving index build performance. Now we’re getting into some of the cooler new features like includes. It’s an excellent topic, but I think it should have gone more into some of the performance benefits, and what kind of increase you might expect. It does give you some good syntax to follow though, so it’s very useful. There are a few other topics given here, so it’s really a worthwhile chapter all told.

Chapter 6: Full-Text Search

I’m not going to go into a lot of detail here, but needless to say that here you’ll get a pretty decent discussion on the use of FTS. All basic topics for creating, modifying and querying FTS catalogs are covered.

Chapter 7: Views

Since views really aren’t that complicated, this chapter stays fairly basic. I mean, it gets about as complicated as you can for views, and I guess the indexed view section has some merit in that they’re a bit more restrictive than regular views, but that’s really about it. Don’t get me wrong, this chapter delivers on the promise of the book… you get all the syntax you need to get up and running with views. It also goes into distributed partitioned views and the rules governing how to update them.

Chapter 8: SQL Server Functions

What can I say about this chapter. It pretty much just goes over the system functions found in SQL and how to use them. I don’t really remember finding anything here that isn’t in BOL, but it does complete the book. A simple saved search in BOL however could make a very usable reference as well.

Chapter 9: Conditional Processing, Control-of-Flow, and Curso

Here you start out with CASE and its uses. It gives a couple use case scenarios and explains how they work. You then move into IF…ELSE, Return, WHILE, etc. After that you get right into cursors. Here you get into the basic syntax for creating a cursor, but there isn’t much in the way of examples, so this is just a perfunctory discussion on the topic at best. It’ll get the point across for understand basic syntax, but that’s about all it’ll do. Maybe I expect too much from topics like this, but I also feel I have the right to. I would like to see some good use case scenarios addressed.

Chapter 10: Stored Procedures

This starts out talking about the basic syntax for creating and working with SPs. And then moves into a brief discussion on cashing and recompilation. It’s clearly not a tutorial on the ins and outs of using SPs or coding them or using good techniques within them. It’s merely a discussion on the basic syntax. The chapter isn’t that long, and frankly, I can’t really find anything here that isn’t in BOL. It’s a good chapter for syntax and it helps complete the book, but this isn’t the first source I would go to when in need of syntax for creating an SP.

Chapter 11: User-Defined Functions and Types

You start out here with UDF basics. Creating, modifying, and viewing metadata are all covered. You then get into a couple good examples of practical uses for UDFs with plenty of code to back it up. Then you finish up UDFs with a quick discussion on using them in place of views. Next you get into UDTs, creating dropping, etc. It’s a quick discussion, but there’s not much to discuss in the first place.

Chapter 12: Triggers

You start off here with DML triggers and all the different kinds there are like Instead Of, After, etc. Next you get into DDL triggers which are new to Yukon. It goes into nice detail with fully coded examples… good stuff. Now you get right into managing triggers, controlling recursion, setting firing order, etc. It’s a fairly short chapter, but it gets right to the heart of the matter.

Chapter 13: CLR Integration

Considering the depth of this topic, this is actually a pretty short chapter. It doesn’t go into a lot of detail on the hows and the whys, but if you forget the basic syntax for something it’ll be a handy reminder. It starts out with a CLR overview and then when you should and shouldn’t use assemblies. CLR objects are overviewed next along with enabling CLR support. It then covers the basic syntax for writing stored procedures UDFs, and triggers. Administering and loading assemblies are also covered. Again though, this isn’t a tutorial so don’t expect tons of explanation on what’s going on. For that, I would suggest the book Pro SQL Server 2005 Assemblies, also by APress.

Chapter 14: XML

Ah, XML… the next big topic in SQL. It’s hard to remember sometimes that the scope of this book is to be a template for coding snippets. That said, you can expect a little explanation here, but you’re not going to get into tons of XML theory here. What you will get is plenty of code to get your job done in a crunch. It starts out here with some basic discussions on XML technologies. You then get into working with native XML. There’s plenty of code showing you how to create XML data type columns and inserting XML data directly with a query. Validating with schemas is next, and a little more discussion on the broader scope of what it means to use a schema against your XML data, but again, that isn’t the scope of this book, and that’s still hard to keep in mind. You’re clearly expected to already know why you want to do things, this book just tells you the syntax to do it with. You then get into querying and modifying XML data. FOR XML is covered next and is handled well enough. You finish off with a real quick discussion on OPENMXL.

Chapter 15: Web Services

You start out here with a nice table of XML and web technology protocols and methods. It’s nice to see them all in one place and defined. Next you get right into HTTP endpoints. Again, there’s hardly any explanation, but plenty of code. Most of it you could probably get from BOL, but it’s a nice completion category for the book, and it’s nice to have all this stuff in one place that’s easy to search. After HTTP endpoints you get into creating a .NET client that uses a web service. This is going to be a .NET Windows application. It’s a very simple application, so don’t expect much, but it’s a clear example that should get the point across and teach you the basics.

Chapter 16: Error Handling

Ok, without much explanation, you start out with system-defined and user-defined error messages. Now you get into RAISERROR with some code to back it up. TRY…CATCH comes up next with a short explanation of the different aspects of the error you can capture. There’s now a short section on old style error handling. It shows a couple quick examples and then gets right into the examples for TRY…CATCH.

Chapter 17: Principals

This is surprisingly thorough. Though there really isn’t much for me to report about it, but it does cover Windows and SQL principals and all the syntax needed to manage them. It then moves into DB-level principles and how to manage those as well, and then moves directly into application roles. Nice chapter in all.

Chapter 18: Securables and Permissions

Here you’re going to get into much deeper security administration than in the last chapter. It starts out with a permissions overview that tells what the different permissions mean. It then gets directly into syntax for reporting on permissions. It’s very thorough in this respect. It then goes directly into server-scoped permissions and lists all the statements you can use to admin user rights. Server permissions are next followed by DB-level permissions. You then get into schema-scoped permissions which is handy because schemas are the newest big thing in SQL security and are going to make security much easier. Next you get into object permissions followed by managing permissions across securable scopes. Changing ownership comes next. One of the things I like about this chapter is the sections it has for reporting permissions. It’s very helpful to see all of the related syntax for the different functions in one place.

Chapter 19: Encryption

Encryption is so malunderstood that even the little bit of explanation here is a wealth. There are a couple really good tips here along with the code and I honestly thought that implementing encryption in Yukon would be harder than it is, but as it turns out, it’s pretty easy. This is a really good chapter that will get you up and running with encryption very quickly, and even teach you the basics of the different encryption methods. It starts out talking about encryption via passphrase, which is just a really quick way to introduce encryption into your DB. It then enters into a very good discussion about master keys and how to create them, back them up, restore them, etc. Asymmetric key encryption is next and it’s given quite enough space. The truly curious will be left with questions, but it’s still a good primer. Next the same information is given for symmetric and certificate encryption methods.

Chapter 20: Service Broker

This is a good chapter on service broker. I’m not going to put a lot of effort into a detailed review because frankly I’m just not that interested in the topic, but rest assured that it follows the tone of the rest of the book and is fairly thorough in its presentation of code.

Chapter 21: Configuring and Viewing SQL Server Options

Wow!!! What can I say about this chapter. I really thought this was going to be something all encompassing and cool, but it turned out to be 4 pages of sp_configure. Surely this could have been rolled into another chapter.

Chapter 22: Creating and Configuring Databases

Starts out with different code for creating DBs and adding user access. Detatch and reattach come next along with configuring DB options. There are a lot of options covered so I’m not going to get into it here, but you’ll find that not too much has changed in syntax. Most of, if not all of your previous scripts should still run just fine. There are so many other topics covered it would make this review way too long to be practical. Rest assured though that this chapter is quite worth a glance.

Chapter 23: Database Integrity and Optimization

Here you start out talking about the major integrity DBCCs like checkdb and the like. It’s all fairly straightforward. Index maintenance is next and there are some significant enhancements to these procedures, so you really need to know this stuff if you plan on doing any production work. That’s about it. It’s pretty short, so for any decent discussion you’ll need to hit BOL.

Chapter 24: Maintaining Database Objects and Object Dependenci

This is another really short chapter but it’s got some of that miscellaneous DB admin stuff in it like renaming objects and changing schemas, etc. Not too bad, but definitely should be more meaty for those who don’t know much to begin with.

Chapter 25: Database Mirroring

Man, what can I say… mirroring is the biggest technology since sliced indexes. One thing this book has over BOL is its logical presentation of how to setup and configure mirroring. BOL contains all the same info, but it’s not nearly as easy to come by. This chapter starts out with a simple explanation of how mirroring is architected and then all of the actual steps IN ORDER of how to implement it. Once you know how to set it up and get it going, then BOL can kick in and teach you a lot, but this chapter goes far beyond BOL by organizing this hornet’s nest into something a novice can understand and implement easily.

Chapter 26: Database Snapshots

This is a pretty short chapter… probably because there just isn’t enough to the topic to make a huge chapter. That said, it covers the syntax of everything you need to know to create and manage snapshots. It starts out with snapshot basics and then moves right into the syntax for creating and querying them. Then the most logical step is to explore how to remove a snapshot. It finishes up with recovering data from snapshots.

Chapter 27: Linked Servers and Distributed Queries

Again, this is a short chapter, but it does what it needs to do. I’m not going to explore it in detail, but just know that it’s good coverage of linked servers and there are no surprises.

Chapter 28: Performance Tuning

This is another one of those chapters that misleads you into thinking that it will be an actual tutorial on performance tuning. Of course, as I’ve discovered again and again in this book, it merely gives the syntax with brief explanations. It does start out however with some basic explanation of some basic performance tips. It moves directly into Profiler with some screenshots. It then moves into capturing queries using a DMV and viewing execution plans. It then covers forced plan usage in queries and runtime stats. You now get into the major syntax portion for managing statistics, fragmentation, and index usage. Database Tuning Advisor is given ample time with screenshots next, and the chapter finishes up with some miscellaneous techniques.

Chapter 29: Backup and Recovery

All of the standard and new features are covered. Yukon has some really good features it brings to backup and recovery. This chapter shows you how to use these new features like partial restore as well as page-level restore. Good stuff.

-Sean McCown, ITBookworm.com


Leave a Reply

Your email address will not be published. Required fields are marked *