Book recommendation for advanced SQL design, scripting and optimiziation.

Question

What is a good book to become an SQL master? That is, learn how to tweak queries, table design, optimizing the server, etc.

I use mostly MySQL and Postgres, but maybe a DBMS agnostic book would come handy. The "optimizing the server" part is probably specific to each DBMS.

I would like to know of a good book to learn advanced SQL and database design which would help someone become the DB Admin.

Answer 1

Targeted at SQL server, Adam Machanic's Expert SQL Server 2005 Development is full of practical gems, and hard to beat for some advanced topics. This book paid for itself the first time I opened it.

Pro SQL Server 2008 Relational Database Design and Implementation

The Art of SQL by Stephane Faroult and Peter Robson is very good.

Any of Itzak Ben Gan's T-SQL books.

For tuning, I recommend:

• SQL Server Query Performance Tuning Distilled

• Inside Microsoft® SQL Server(TM) 2005: Query Tuning and Optimization

• SQL Tuning

Answer 2

I'll add Ken Henderson's Guru's Guide books for SQL Server and Tom Kyte's Expert one-on-one Oracle and Effective Oracle by Design for Oracle. He's also written a newer book called Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions which I haven't read.

For generic database and SQL programming books, Joe Celko's SQL for Smarties series is a good set of resources covering a wide variety of advanced SQL programming topics, and is explicitly done in a platform-independent way.

One point to note about Oracle is that it's quite a mature product and doesn't change that much from release to release. Much of what you read in a book about older versions is still applicable to 11g.

Answer 3

I learned many things from SQL Performance Tuning.

SQL Performance Tuning is a handbook of practical solutions for busy database professionals charged with managing an organization's critically important data. Covering today's most popular and widely installed database environments, this book is an indispensable resource for managing and tuning SQL across multiple platforms.

Packed with tips, techniques, and best practices, this hands-on guide covers SQL commands and queries over a wide range of conditions and environments. Every facet of the SQL environment is covered, from concurrency control to optimization—and every issue in between. The book includes an overview of each of the most widely used database management systems (DBMS) and provides tuning tips for common day-to-day situations.

Topics covered include:

• General and specific tuning
• General sort considerations and options
• Joins and subqueries
• Columns and tables
• Indexes, including B-trees and bitmaps
• Constraints and stored procedures
• ODBC and JDBC
• Data changes and concurrency control (locking)
• Clients, servers, and middleware

Whether you are a programmer, consultant, or user, you will find SQL Performance Tuning an indispensable guide to optimizing SQL performance across today's popular database environments.

Throughout SQL Performance Tuning, ideas and techniques are tested on a variety of popular SQL environments, including IBM DB2, Informix, Ingres, InterBase, Microsoft SQL Server, MySQL, Oracle, and Sybase ASE. The book's practical examples are written in ANSI/ISO Standard SQL: 1999.

Answer 4

High Performance MySQL (2nd ed.) is the best I've read. It covers all the important aspects - design, optimization, scaling and integrity, and with so much rigor and attention to detail. The book was written by real experts and contains a ton of precious gems (some of those authors have very useful MySQL blogs as well).

Answer 5

There are several other questions here with good answers as well:

http://stackoverflow.com/questions/tagged/books+sql

http://stackoverflow.com/questions/tagged/book+sql

The ones mentioned here are all excellent: Celko, Ben Gan, Henderson, Faroult. I also recommend Hernandez.

Answer 6

I've found the following two books the most useful in all of my database projects. Its targeted audience is for novices, but even intermediates will find the book extremely useful. Both books are platform independent, so they'll be a great start to building your SQL knowledge.

Database Design Mere Mortals

SQL Queries for Mere Mortals

Here's another book which I've found useful. If purchased with the above books, it would have an overlap in information, but this book will show how the top 5/6 different SQL product's SQL queries can differ in trying to find the same information (Example would would MS SQL's "TOP 10" vs. MySQL's "LIMIT 10" method, or even how group is done between the two).

SQL Cookbook

Answer 7

For SQL Server, I still find Books On Line the most complete and authoritative source. Other authors (some of whom are named above) generally, in my experience, end up expanding on the same information with less complete coverage and greater verbosity, which may be your style - it's not mine, but I still prefer unix-style man pages.

This is the one product for which Microsoft seems to have gotten the documentation right.

A good contra-indication is how many pages are spent on edge cases, like faux arrays, numbering records and ways to avoid dealing with NULL. Another bad sign is long lists of practices that are good or bad, but no indication how trivial or massive the comparative damage might be. (Blogs and listservs are especially guilty of this.)