97 Things SQL
This is the Commons page for the book 97 Things Every SQL Developer Should Know, edited by Alan Beaulieu.
Imagine that you are speaking at a Convention or User's Group, and you have a room full of novice SQL Developers eagerly awaiting your pearls of wisdom. What would you tell them? What information or advice would you impart in order to help them to become productive members of a development team? Perhaps you are a DBA, or a Project Manager, or a Systems or Data Architect; here's your chance to help avoid the many pitfalls that await them. Are you tired of seeing database devlopers "code around" issues? Do you despair at the lack of knowledge of basic database concepts, such as proper transaction boundaries, or the difference between optimistic and pessimistic locking? Now's your chance! Tell them what they need to know!
Note: This site is not public - this wiki is only an interim space where we can develop the book.
I am an independent consultant with 20 years experience with the design, development, and implementation of custom databases, data-access layers, and associated applications. I have worked with numerous databases over the years (Informix, Sybase, MySQL, Oracle, SQL Server), but specialize in Oracle Database, with which I have 14 years experience going back to version 7.0. I have written 2 books for O'Reilly Media (2 editions of each): Mastering Oracle SQL (with Sanjay Mishra), and Learning SQL. I continue to be dismayed at the general lack of knowledge in the database developer community, and I'd like to see this book in every SQL developer's cubicle.
Rules of Engagement
- Nominate Others. Contribution is by invitation only, but you can nominate others for inclusion by sending their names, email addresses, and potential topics to Alan Beaulieu. Assume that each author may write one or more contributions.
- Create an account. You can set yourself up with an account (just click create_account) and begin writing your first contribution immediately.
- Each contributor is asked to provide an axiom (a tip or bit of wisdom) and a brief discussion. The axiom (tip) should only be a 2 to 10 words long if possible. In print, we want each axiom and discussion to fit on a two-page spread. Keep your discussion between 250 and 500 words. Discussions shorter than 250 words are fine, but only ones that are 250 or more are likely to make it into the final book. Instructions for adding your tip are found in the Contributions section below.
- Create an author page. Content on your author pages certainly doesn't count towards your 500 words. We'll want (minimally) a bio and a head shot. Only contributions with associated bios (including head shot, and description of background) will be considered for contributions to the book. We may include the bios and head shots in the print version of the book. You may use an icon that's associated with you instead of a picture. Please keep your author page up to date.
- After adding your tip in the list in the Contributions sections (instructions provided in that section), clicking on your own name will take you to the author page. Choose the Edit tab to enter in your own bio.
- Add a head shot. Go to the Toolbox along the left hand side of the page. (You may need to scroll up) Click on Upload file, the third item on the toolbox list.
- In the Upload file window, next to the Source filename: entry box, click on the Browse button. Choose the folder on your own computer in which you have stored your photo. Locate the photo you want to use and double click on it. The information will automatically fill in. Click the Upload file button at the bottom on the window. You have now uploaded your photo to the O’Reilly server.
- Go back to the Home Page. Copy this tag [[Image:filename.jpg]]
- Scroll down to the Contributions section and click on your own name. Choose the Edit tab if it is not already selected.
- Go to a blank line at the bottom of your bio. Paste in the tag.
- Change the word “filename” to the name of the file with your photo. For example, if your photo was named Alan.jpg you would type the word Alan in place of “filename”. Your tag will now read [[Image:Alan.jpg]] If you have a different type of file extension, such as .gif, change .jpg to .gif. Change from the Edit tab to the Article tab to see your photo at the bottom of your biography.
- Add your address and e-mail. Please also add your postal service address and email address to the 97SQL Addresses page. This information will not be released when the site goes public. We need to keep track of it so that we can send you a free copy of the print book if one is published, and in case in the future O'Reilly's legal department decides we need to get explicit releases from all the contributors prior to publication. If you still don't want to disclose your contact info (and I cannot promise that this site won't "leak" out to the rest of the world), please still add your name to 97SQL Addresses, and write "I'd prefer not to" or something like that. Then send me your contact info.
- No graphics. Please, no illustrations, graphs, cartoons, or code. We want principles of SQL development to be text only. 250 - 500 well-chosen words can say a lot more than a picture.
- Commercial Free. Please keep contributions free from references to specific products or technologies that compare their worth, or paint them in a positive or negative light. For example, don't talk about why you should use Oracle 11g; just talk about principles that are valid for any project manager dealing with a SQL project.
- Editing Ethics. You have the ability to add or change your tip contribution at any time. To be a good participant, please edit your own contribution only. Be very careful that you don't accidentally alter someone else's work.
- Protect the privacy of our site. Please keep this URL private sharing it only with people you invite personally to contribute. Don't link to it, digg it, put it on your web pages, send it out to a mailing list, etc. First, it's only temporary. This project will not live within O'Reilly commons indefinitely. Second, we'd like to keep this under wraps until we have a decent block of material to release.
- Legal Stuff. All contributions made to this site are required to be made under the Creative Commons Attribution 3.0 license. This means that by making a content contribution, you are agreeing that it is licensed to us and to others under this license. If you do not want your content to be available under this license, you should not contribute it.
- Volunteers Only. Contributions are made on a volunteer bases - in other words, contributors are not paid for their contributions. The contributions will be made easily available to everyone on the world wide web for free. However, remember that those of you whose tips are chosen for publication will get your name attached to your work, you picture published next to it, and a free copy of the published book.
- Submit Only Original Work. You warrant that all work that you contribute to this site is your original work, except for material that is in the public domain or for which you have obtained permission.
Frequently Asked Questions
Here are some specs and FAQs regarding the book.
How big will the book be?
I expect the book to come in around 200 pages, with each of the 97 topics spanning 2 pages (roughly 500 words).
How long should my topic be?
Each topic should be approximately 500 words.
What's in it for me?
You will get your name, picture, and short bio on your first topic page. Thousands of people will read your pearls of wisdom. You may get invited to appear on Oprah (unlikely, but possible...). You will feel a great deal of satisfaction in helping tens of thousands of developers to understand the environment in which they work.
What if I don't like the proposed topics?
By all means, suggest new ones. I came up with over 80 topics in my first brainstorming session, but there's plenty of untapped areas.
Please add your contributions here. Click the  link on the upper right of this section to add a new contribution. (Do it now. You can read the rest of these instructions in that window.) A new window will appear with colored icons at the upper left.
Once in the new window, to add your own contribution, click next to an open item's title. Add " by " and click the "Ab" icon at the top of this page (third from the left). Type your name to replace the words "Link title" within the double brackets. If you want to add a new item, add a new row under the appropriate topic area, type "*", and click the "Ab" icon. Just start typing the tip title you want to add in the highlighted blue area, replacing the words "Link title" which appear inside double brackets.
At the bottom of the page, click the "Save page" button. You'll be taken back to the Home Page. Click on the red link showing your tip title to be taken to a window where you can either create or cut and paste your 250- to 500-word tip. Remember to click the "Save page" button at the lower left after you have added your short article.
Clicking on the red link for your own name will take you to the author page. Choose the "edit" tab to enter your bio. Note that you only have to create this page once, even if you are submitting more than one contribution - just make sure you are using the same name (not nicknames or variations) every time you make a new Link title for yourself.
Please use the following status comments after your entry to let us know about the state of your contribution:
- (in progress)
- (ready for editing)
- (author review)
- What is a Relational Database?
- What is SQL?
- Understanding ACID Properties by Mark Richards (ready for editing)
- Database Architectures – No Two Servers Are Alike
- What You Need to Know About Locking and Concurrency
- Are “Dirty Reads” ever OK?
- What is Eventual Consistency? by Mats Kindahl
- What is BASE and how does it differ from ACID? by Mats Kindahl
- What is XA?
- The Hardware Guys Want to Virtualize Your Database Server; Should You Let Them?
- What You Need to Know About Your Disk Array
- Would Your Application Benefit from Solid-State Disks?
- Unix vs. Windows: Which is Best for Databases and Why?
- Understand How Many CPUs Are Available And How to Actually Use Them
- Don’t Blame the Server; It’s Probably Your Code by User:ThomVF (ready for editing)
- The Database is Just One Piece of a Larger System by Sarah Novotny
- No Man is an Island: Get to Know Every Member of the Team by Sarah Novotny
- Please Don’t Undermine the Architecture!
- What You Need to Know about Messaging
- Understand as Much as You Can about the Business by Tim Chapman (in progress)
- Who is Managing Transactions? by Mark Richards (in progress)
- 2-Phase Commits: What are They, and When They are Needed by Mark Richards (in progress)
- Take Advantage of Excel Pivot Tables by Larry Rockoff (ready for editing)
- Understand the Role of the Underlying Database in Your Application Architecture by Yuli Vasiliev (ready for editing)
- Know How to Read an ER Model by Karen N. Johnson (ready for editing)
- The Difference Between Logical and Physical Modeling by User:drsql (ready for editing)
- What is Normalization, and How to Know When You’re Done by User:drsql (ready for editing)
- What is a Star Schema, and When to Use One by User:ThomVF (in progress)
- Don’t Denormalize Just Because You Think You Should
- Model the Business, Not the Application by Chris Woodruff (in progress)
- Abstract Designs: Flexibility vs. Complexity
- Please Don’t Undermine the Database Design!
- Star Schemas Aren't Just for OLAP Cubes by Larry Rockoff (ready for editing)
- What You Should Know about Indexes by Tim Chapman (in progress)
- Run Tests Using Reasonable Data Sets by Arup Nanda
- Pre-tune Every Non-trivial SQL Statement
- How Not to Launch the Query From Hell
- Don’t Let Day 1 of Production Deployment Be the First Time Your Code Has Run Under Load
- When to Partition Your Tables and Indexes by Arup Nanda
- Design a Strategy for Removing Unwanted Data by Neil Neely (Ready for Editing)
- No, I Can’t Make a Development Copy of the 10-TB Production Database Every Other Day
- Redo and Undo and Temp; Oh, My!
- Security Should Be Designed Up-Front, Not Bolted On Later
- Use Roles
- What to Encrypt and Why
- Column-level Security Using Views
- How to Implement Row-level Security
- What is an SQL Injection Attack? by Neil Neely
- When SQL is the Wrong Answer by Richard Sonnen (ready for editing)
- Optimistic vs. Pessimistic Locking
- Deadlocks and How to Avoid Them by Tim Chapman (in progress)
- Why You Need to Use Bind Variables
- The Difference Between Truncation and Deletion by Tim Chapman (in progress)
- Know When to use Transactions by Mark Richards (ready for editing)
- Use the ANSI-Approved Join Syntax
- There’s More Than One Set Operator: Use Them!
- The Logic of Set Operators by Larry Rockoff (ready for editing)
- Use CASE Expressions
- The Case for CASE Expressions by Larry Rockoff (ready for editing)
- Tables Are Just One of the Things You Can Put in a FROM Clause
- Adopt a Coding Standard Before You Begin Coding by Chris Woodruff (in progress)
- Exceptions Happen; Deal With Them
- Don’t Let Exceptions Fall Through the Cracks
- Instrument Your Code
- Clean Up After Yourself! (cursor management)
- What is Metadata, and When to Use It
- How to Generate XML Docs From a Query
- How to Use XML Docs as Data Sources
- How to Use Functions as Data Sources
- Using the WITH Clause
- Do More in SQL, Less in Procedural Code by Alan Beaulieu
- Doing More with SQL: Minimize Cursor Use
- Doing More with SQL: Merge and Multi-table Insert
- Doing More with SQL: Analytic Functions
- Doing More with SQL: Pivots
- Doing More with SQL: Cube, Rollup, and Grouping Sets
- Queues as alternative for distributed transactions by Gert E.R. Drapers
- Your schema is your public contract by Gert E.R. Drapers
- Should you abstract your physical schema? by Gert E.R. Drapers
Performance and Tuning Issues
- What is the Optimizer, and What Does it Do?
- Understanding Execution Plans
- Covering Your Queries
- What is a Nested-loops Join?
- What is a Hash Join?
- Understanding Partition Pruning by User:ThomVF (ready for editing)
- Influencing Optimizer Decisions
- Controlling Optimizer Decisions Using Hints
- Nesting Your Query to Influence Optimizer Decisions
- Use Scalar Subqueries in Your SELECT Clause
- What are Wait States? by Gert E.R. Drapers
- Death by UDF by Kevin G. Boles
- Table Variables: Not as Good as You Might Think by Kevin G. Boles
- How to Write a Unit Test
- Design the Tests First, Then Code
- Data Types and Testing by Karen N. Johnson
- Think Like a Tester by Karen N. Johnson (ready for editing)
- Program with Objects and Collections
- Alternatives to Using Temp Tables
- Do Things in Parallel
- Do Things In Bulk
- What Are Ref-Cursors, and When to Use Them
- What You Need to Know about RAC
- Putting it All Together with Pipelined, Parallel-Enabled Table Functions
- Building an Object-Oriented Interface to Your Relational Data
- Bringing Together the Power of SQL and XML with XQuery by Yuli Vasiliev(ready for editing)
SQL Server-Specific Issues
- Generating Custom Datasets Using Table-Valued Functions
- Creating Delimited Lists Using T-SQL by Adam Machanic
- Variable collation derives from master by Gert E.R. Drapers
- Temp tables vs. table variables vs. tables inside tempdb by Gert E.R. Drapers
- MySQL Server Engines - What They Are, and When to Use Which One by Richard Sonnen (Ready for Editing)
- Replication by Charles A. Bell (in progress)
- Backup and Recovery by Charles A. Bell (in progress)
- High Availability and Scale Out by Charles A. Bell (in progress)
- Mastering the Binary Log by Charles A. Bell (in progress)