Review: Programming MS SQL Server 2008 – Holistic coverage for an App Developer

  • Article

Following on from my review of my library. This week, Programming Microsoft SQL Server 2008.

Summary:

It appears to me that the authors started this book with the premise “There is a lot in SQL2008 that can dramatically change the way you architect solutions. Many features elegantly remove some of the clunky poor performance approaches we use today” So they set about drawing your attention to those features & mapping it back to the problems you currently experience when writing applications.

The book is a good read, plenty of code examples & screenshots to help understand the subject quickly (not just to pad out the pages)

This book differs from other SQL 2008 books I’ve reviewed in 3 ways :-

  1. It takes a holistic approach. It doesn’t stop at the Relational database but expands your thinking into
    (i) handling non-relational datatypes; Spatial, Text, Video & Audio streaming, Hierarchies.
    (ii) Using the BI Components for Reporting & Analytics instead of the, usually less efficient approach, of doing it all yourself with TSQL.
  2. It focuses purely on SQL 2008. There is very little here for those on SQL 2005 or earlier. So it is not a “Hey there is much in the engine you will need, a lot was already there, & this new feature extends it” but more a “This new interface now lets you do it in 3 lines & removes that “huge cliff” you had to program around. OR SQL now has a component that does that, you don’t need to develop your own component from scratch.”
  3. It is more for those in development teams. Except for a small section, Chapter 2, the book assumes you will be comfortable in Visual Studio or at least BI Dev Studio; for writing SQL Reports & the Analytics piece. OR even if you don’t code yourself, you want the knowledge to suggest new approaches to architect the app better.

Photo of Book Cover

Recommended Audience:

SQL Leaders - Everyone who considers themselves “a database person”, especially if you advise others on how to create more efficient solutions that use SQL Server. 
Architects – more so if you design down to the component level.
Developers – who write code to interact with Databases, Analytics, Reporting or Data Mining.
Anyone who wants a good understanding of the new capabilities of SQL 2008 in 1 book, at 951 pages this is quite a reference.

Version: SQL 2008, & SQL 2008 R2 (when it ships in 2010). Not for SQL 2005 & earlier versions – except the Analytics part. If that was all you wanted, you could find another book just dedicated to that.

Detail:

Like SQL 2008 Internals, this book is written by a team of authors. Handy because it is hard to specialise in everything. Compared to the SQL Internals authors, they must have spent much more time peer reviewing as there is a lot less variation in style between each chapter. Though you can see one author was keen to give a little background while one of the others prefers to dive right in.

Part 1: Core Fundamentals (228 Pages)

This part is great. Buy the book just for Chapter 2 & then flesh out your thinking with the other chapters.

Chapter 1: Overview

Nothing. Just tells you what is in the book.

Chapter 2: T-SQL Enhancements

You’ve gotta read this chapter, even if you just stand there in the book store, it is pure gold. I could tell you that it has a very nice coverage of CTE’s both recursive & not. Or list the TSQL Features that it covers: PIVOT, UNPIVOT, APPLY, TOP, ROW_NUMBER, OVER, Ranking Functions, Try/CATCH, WAITFOR, DDL Triggers,  Date & Time Datatypes, MERGE Statement, OUTPUT clause, Grouping Sets. But Books Online does that too. What sets this apart is their application of these features. They have put a lot of thought into “Does this new feature empower me to solve a problem I’d been unable to fix before”, then serves up a lot of really useful examples & ideas. If you don’t read this thinking “wow I wish I had that in the last project(s) I worked on”, you must be fresh from school. (or did too many drugs in your youth)

Chapter 3: Exploring SQL CLR

This is not the ultimate reference in SQL CLR, it misses some of the “how to ensure your CLR Designs perform well”. That said it is a great tutorial. It covers all you need to know to write all forms of CLRs; Datatypes, Triggers, Aggregates. How to deploy them. How to use the tools. There are a few landmines in CLR development that you can waste days in. This guides you around them, giving you a methodology that helps you avoid a lot of the newbie pain.  

Chapter 4: Server Management

How to programmatically access Server Management Objects (SMO). If your app needs to Manage SQL Server, SMO is preferred over sniffing around the System Catalogue with TSQL scripts ie: “SELECT * FROM sys.objects”. It briefly touches Policy-Based Management. But for PBM you’d get more listening to a Tech-Ed session on the topic.

Chapter 5: Security in SQL Server 2008

Security is always a pain & is often the cause of a lot of “hard to solve” user problems. Largely because is it boring, not well understood & squeezed into the design when the project is nearly complete. This chapter makes it easier. It covers Authorization, Authentication, Encryption (inc Transparent Data encryption), Keys & Certificate.

There is a nice section on the new SQL Server Audit Feature. If you commonly use (i) LastModifiedDate & LastModifiedBy columns OR (ii) Triggers that mirror the DML changes into “Audit” tables. This is a must read for you. 
Also a small piece on common ways hackers will hit you.

Part 2: Beyond Relational (106 Pages)

This part may permit you to remove large inefficient parts of your app & replace them with easier to maintain much more performant alternatives. It may also get you thinking of new exciting features you can offer, that will excite your users &/or customers. 

Chapter 6: XML & the Relational Database

While not a lot of recommendations or things to extend your use of this feature. It is a very comprehensive coverage of SQL’s XML capabilities together with a lot of examples. It should be enough to help figure out what isn’t working & get you going.

Chapter 7 Hierarchal Data and the Relational Database

This is 25 pages on the HierarchyID datatype. Nothing insightful, it just tells you how to use it with good examples.
While I’d prefer the HierarchyID datatype to be more I want it to “just work” ie: figure out & maintain the hierarchy without any involvement from me. It is very powerful & does remove a lot of performance issues from apps that use this style of relationship. eg: Org Charts, Bill of Materials. So it is worthwhile you know about it.

Chapter 8: Using Filestream for Unstructured Storage

This chapter lives up to its title, with a fairly comprehensive coverage on how to incorporate large files; Video, Audio, Picture & Documents, into SQL much more efficiently using the FILESTEAM feature.

While a good chapter I was a little disappointed by the narrow focus. SQL 2008 introduced two new features for BLOB handling to compliment the SQL2005’s VARBINARY(MAX) replacement of the IMAGE datatype. FILESTREAM was one of them & Remote BLOB Storage API was the other. It seems they missed the opportunity to cover Remote BLOB’s here or at least to give them a mention & compare / contrast when to use one over the other.

Chapter 9: Geospatial Data types

This small chapter contains a reasonable coverage of (i) Spatial Types, Methods & (ii) how to integrate with Virtual Earth. Also a small C# example of how write code that uses the Spatial Types.
Call me biased but I think my 12 Blog Posts on the topic gives a deeper coverage on using Spatial Types & methods. And Johannes Kebeck's Blog is an authority source for the Virtual Earth (Bing Maps) interop. But I’d agree if they hadn’t put something in I’d be complaining that they overlooked it, so I’ll shut-up now.

Part 3: Reach Technologies (226 Pages)

This Part is largely a coding guide. Introducing you to features in the development tools & language that will have you up & running quickly. It ensures you are informed & can choose the right technology for you.

Chapter 10: The Microsoft Data Access Machine

This is a 101 for the coder. It covers all those important things you never hear Microsoft talk about in their events. Creating DataSets, Dataset designer, Table Adaptor, Connection management, DataReader, … all the stuff that is easy to do when someone has shown you & time-consuming to figure out by yourself.

After ADO.NET, there is similar depth on Language Integrated Query (LINQ) technologies: LINQ to DataSet, LINQ to SQL, The Entity Framework & EDM, Web Services for Data.

Chapter 11: The many facets of Data Binding

Data Binding can remove a lot of code. This is a good introduction of how to get it working from the different app development environments; Windows Forms (inc LINQ to SQL, Entity Framework), Web with ASP.NET (inc AJAX) & Windows Presentation Foundation (WPF) including XAML & Silverlight.

Chapter 12: Transactions

It seems that most people believe that Transactions are unimportant in a relational system & recommend you use the NOLOCK hint to turn them off. ;-)  But if you believe that data corruption is undesirable in your app this chapter may interest you. It covers everything from the basics; ACID Properties & Isolation level. Then looks at the impact of SQL Configuration Options; ie: AutoCommit vs Explicit Transactions vs Batch Scoped & MARS.  Then finishes with good coverage on Distributed Transactions; 2 Phase commits,  Resource Managers. Examples show xtns in your TSQL Code, your .NET app code & your SQL CLR code.

Chapter 13: Developing Occasionally Connected Systems

70 pages of really detailed coverage looking at the technology alternatives for distributed occasionally connected apps. The new Sync Services & Change Tracking technologies are explained the compared & contrasted with Merge Replication. This chapter has more than sufficient detail for you to use it as a reference with starting to develop your app using either Merge or CT technologies. It does not overlook that most of these solutions will want to sync with PDA/Mobile phones, so SQL Server Compact v3.5 is featured strongly.  
Note: SQL 2008’s Change Tracking (CT) is very different to Change Data Tracking (CDC) though in some apps you could use CT or CDC.

Part 4: Business Intelligence (388 Pages)

This Part is largely a tutorial style, guiding people inexperienced in the SQL BI topics thru the options available to them.

I had planned on comparing this to Microsoft SQL Server 2008 Analysis Services Step by Step but in my generous excitement I gave it away at a SQL User Group meeting. From memory, the content is similar. Of course this book has 3 other parts in it & is more targeted to those writing applications that use the engine.

Chapter 14: Data Warehousing

Much of this chapter is a “What is DW”; comparing Inmon vs Kimbal & similar topic definitions. Change Data Capture gets a very detailed coverage. Whereas features like SPARSE columns, Data Page Compression & Backup Compression are introduced & have 1 example each. 

Chapter 15: Basic OLAP

This is an OLAP 101. It guides you thru using the tools to create a cube. Predominantly it is an introduction to the designers & wizards. Like chapt 14 much of this is applicable to SQL 2005 & perhaps even SQL 2000.

Chapter 16: Advanced OLAP

This is not Advanced OLAP, but it is a useful coverage of all the other features beyond creating a basic cube. eg: Dimensions; Member Grouping, Attribute Relationships, Calculations, Names Sets, KPI’s, Actions, Perspectives, Translations & Security. It covers Proactive caching, Partitions, Aggregation Design & other Storage Settings.
If you have minimal to zero experience in SQL OLAP you will find these 3 chapters have sufficient detail to get you going without getting lost in detail or reading a specialist book on the topic.
If you want Advanced OLAP get the bible.  SQL Server 2005 Analysis Services (not an introductory text)

Chapter 17 OLAP Queries, Tools and Application Development

This covers a multiple of visualisation options.
(i) Excel as a client tool. This will be superseded when the Gemini Execl add-in is released with SQL 2008 R2.
(ii) Excel Services for web BI.
(iii) A tiny glimpse of MDX (Multi-Dimensional eXpression language Analysis Services (SSAS) equivalent to T-SQL )
(iv) .NET development via ADO MD.NET
(v) Analysis Management Objects (AMO) this is the SSAS equivalent to SMO.
(vi) XMLA – XML for Analysis Services.
(vii) Writing “SQL CLR’s” for Analysis Services.

That is a lot of ground to cover. It does a good job of making you aware of the technologies, what they can do with 1 or 2 examples. As you can imagine once you get started on a project you’ll need more than just this chapter as your reference.

Chapter 18 Expanding your Business Intelligence with Data Mining

This is a nice overview of Data Mining; from methodology you should use, a look at the viewers, creating a mining model & how Nested Tables work thru to reasonable coving of DMX (Data Mining eXtension language). There is a few pages on including it in your code & some mention of the SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007
Again a nice succinct chapter but if I were spending any time reading on Dataming I’d get the bible Data Mining with Microsoft SQL Server 2008  see my Blog Post

Chapter 19: Reporting Services.

This would be best described as a “Tutorial Walk thru” it will get you up & running. It does give some examples on how to incorporate reports into your own code. But you do have to extrapolate from the tutorial into your own work. It is not a reference on all the options you have, nor does it have any “Best Practise” Recommendations. Nor does it contain anything that will expand your thinking into new “not so obvious” applications as does chapter 2 of this book.
For a more detailed walkthru I’d consider Microsoft® SQL Server® 2008 Reporting Services Step by Step its 525 pages swamp the 79 pages of this chapter. But still it is an introductory text & one suitable for Report Authors . I’ve yet to see a book that covered the programmatic aspects &/or extending SQL RS in any great detail. But I’ll keep looking.

If you are too busy to walk to a book store & too lazy to type a search into Bing or Google, the following are links to Online Retailers.

Australians:

When getting the link I noticed this promotion.

So many reasons to shop at the Microsoft Press Online Book Store
– 40% off all books purchased until October 31st 09 and...
  • Best & Latest Microsoft Press Books
  • Express Delivery
  • Quick & Easy Ordering
  • Amazon Price Match Promise
  • Regular Promotions
  • Affiliate Program

Rest of World:

As always I’m interested in your thoughts. Did you read this far? Was it useful or way too much detail?

Thanks Dave.

Thought for the post:

This took way longer to type up than I thought it would. Then again it took a few weeks to read the book too.

Technorati Tags: TSQL,Book Recommendations,Book Reviews,SQL 2008,SQL Server 2008