At Tech-Ed Aust last week I had an interesting evening with a few people chatting about finding information & how hard it is to judge the value of textbooks when you are looking online. As I am an avid reader & have a huge library a few of my colleagues asked me to post my 2 cents about the books I’d suggest.
Microsoft SQL Server 2008 T-SQL Fundamentals is not specific to SQL 2008. The emphasis is on fundamentals & 99.9% of the book is just as applicable to SQL 2005. Possibly 75% would also be valuable to those on SQL 2000. So if you want a strong understanding of TSQL & someone to point out where most people come unstuck. This book is for you.
Recommended Audience: Everybody. Especially C#, VB, Java Programmers & anybody writing code that talks to a SQL Server database.
I’m forever rewriting a ton of poorly written TSQL. It seems most people uncomfortable with anything but the most simple of joins. Beyond that they resort to Temp Tables, Cursors & chaos. If that is you, then this book is for you. It is only 380 pages so not a large tomb to wade thru.
Disclaimer: It is unlikely I will post a really bad review. Why? Am I biased? Nope. Am I being paid? I wish. Am I concerned about offending the authors? Nope … Anyone who knows me will tell you I’m not backward in “sharing my opinion” about both good & bad. Well? I kill time in book stores flicking thru titles, I have a huge reading backlog & so only buy books that look compelling. So, I can only think of 2 really crappy books I have. Both I was given. Yes if I ever get thru all the good books I may review those.
The book is well structured, with a good section (Ch 1) on how the Optimiser uses the different parts of the query (FROM, WHERE, ORDER BY, SELECT etc). A lack of understanding of this is a major reason many struggle with TSQL, they don’t even realise it is important.
Chapter 2 looks at the functions & extensions handy for a single table query. (Way more than SELECT * FROM …). Chapters 3 & 4 builds on this with an easy to read yet comprehensive coverage of Joins; Inner, Outer, Cross, Composite, How NULLs can trip you up. But then builds with the harder stuff; Subqueries, Correlated subqueries. Again, this is not syntax, but solid examples of pitfalls, common errors & recommendations.
Chapter 5 covers Table Expressions; Derived Tables, Common Table Expressions, Inline Table-Valued Functions & the Apply Operator. I use this syntax all the time to easily rewrite code to remove the dependency on Temp Table’s. This reduces the need for Store Procedure recompiles, which reduces the load on the CPU & Proc Cache buffer pages, which improves performance.
Chapter 6: Set based operations UNION, INTERSECT & EXCEPT. Again a way better idea than “INSERT … INTO #Temp …” & then “UPDATE #Temp” followed by “SELECT DISTINCT #Temp…”. What you want is the UNION Statement.
Chapter 7: OLAP style queries: Pivot, Unpivot, Grouping Sets. Sometimes handy to know but you can skim this. If you use these a lot you may wish to consider installing Analysis Server.
Chapter 8: Is rather large coverage on Data Modification; Insert, Update Delete, Merge, Output Clause. There is lots to know here. If you ever Insert a Row & then select it again to see its IDENTITY value or Defaults, you need to read this chapter. There is a better way to code that will reduce your overhead by 50%.
Chapter 9: Transactions, Locks & Blocking. Not a huge coverage but enough to get you started on Deadlocks & NOLOCKS. If troubleshooting Locks & Waitstates is your issue, this book is not enough. The SQL Internals &/or SQLCAT Whitepapers would be better for you. I’ll cover that in a future post.
Chapter 10: A plethora of tips on executing dynamic TSQL. Batches, While, IF, Cursors, EXEC, sp_executeSQL, Error Handling.
Itzik Ben-Gan & Joe Celko are my favourite authors. Both for the same reason. They are real thought leaders in the SQL Language. Both look at solving relational problems from a “Pure Mathematics – SET Theory” perspective & I believe this sets them apart in the innovation they are able to bring to our industry.
This book is not a showcase for all Itzik’s best “elegant solutions to tricky problems” his other books do that. This is about fundamentals, It is short, sweet & if you knew half of what was in this book your applications would run much faster with much less code.
If you are too lazy or too busy to walk to a book store to following are links to Online Retailers.
- MS Press Site (note they Price match Amazon & Tech-Ed attendees can still get 40% off)
- Amazon Price match Promise
Rest of World:
Please tell me if you would find this type of post useful. Particularly the type of audience the book is best suited to.
I’ve a huge backlog of innovation / things I could post & what to know what you value most.