97 Things SQL

From WikiContent

(Difference between revisions)
Jump to: navigation, search
(Database Design)
Current revision (00:39, 4 February 2010) (edit) (undo)
(Programming Strategies)
 
(39 intermediate revisions not shown.)
Line 45: Line 45:
* '''Legal Stuff'''. All contributions made to this site are required to be made under the [http://creativecommons.org/licenses/by/3.0/ 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.
* '''Legal Stuff'''. All contributions made to this site are required to be made under the [http://creativecommons.org/licenses/by/3.0/ 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.
+
* '''Volunteers Only'''. Contributions are made on a volunteer basis - 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, your 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.
* '''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.
Line 91: Line 91:
*[[What You Need to Know About Locking and Concurrency]]
*[[What You Need to Know About Locking and Concurrency]]
*[[Are “Dirty Reads” ever OK?]]
*[[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?]]
===Hardware Issues===
===Hardware Issues===
Line 105: Line 108:
*[[Please Don’t Undermine the Architecture!]]
*[[Please Don’t Undermine the Architecture!]]
*[[What You Need to Know about Messaging]]
*[[What You Need to Know about Messaging]]
-
*[[Understand as Much as You Can about the Business]]
+
*[[Understand as Much as You Can about the Business]] by [[Tim Chapman]] '''(in progress)'''
*[[Who is Managing Transactions?]] by [[Mark Richards]] '''(in progress)'''
*[[Who is Managing Transactions?]] by [[Mark Richards]] '''(in progress)'''
-
*[[2-Phase Commits: What are They, and When They are Needed]]
+
*[[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)'''
*[[Understand the Role of the Underlying Database in Your Application Architecture]] by [[Yuli Vasiliev]] '''(ready for editing)'''
===Database Design===
===Database Design===
*[[Know How to Read an ER Model]] by [[Karen N. Johnson]] '''(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]] '''(in progress)'''
+
*[[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]] '''(in progress)'''
+
*[[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)'''
*[[What is a Star Schema, and When to Use One]] by [[User:ThomVF]] '''(in progress)'''
*[[Don’t Denormalize Just Because You Think You Should]]
*[[Don’t Denormalize Just Because You Think You Should]]
Line 120: Line 122:
*[[Abstract Designs: Flexibility vs. Complexity]]
*[[Abstract Designs: Flexibility vs. Complexity]]
*[[Please Don’t Undermine the Database Design!]]
*[[Please Don’t Undermine the Database Design!]]
-
*[[Star Schemas Aren't Just for OLAP Cubes]] by [[Larry Rockoff]] '''(in progress)'''
+
*[[What You Should Know about Indexes]] by [[Tim Chapman]] '''(in progress)'''
===Administration Issues===
===Administration Issues===
*[[Run Tests Using Reasonable Data Sets]] by [[Arup Nanda]]
*[[Run Tests Using Reasonable Data Sets]] by [[Arup Nanda]]
-
*[[Pre-tune Every Non-trivial SQL Statement]]
+
*[[Pre-tune Every Non-trivial SQL Statement]] by [[Tim Chapman]]
*[[How Not to Launch the Query From Hell]]
*[[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]]
*[[Don’t Let Day 1 of Production Deployment Be the First Time Your Code Has Run Under Load]]
Line 142: Line 144:
===Programming Strategies===
===Programming Strategies===
*[[When SQL is the Wrong Answer]] by [[Richard Sonnen]] '''(ready for editing)'''
*[[When SQL is the Wrong Answer]] by [[Richard Sonnen]] '''(ready for editing)'''
-
*[[Optimistic vs. Pessimistic Locking]]
+
*[[Optimistic vs. Pessimistic Locking]] by [[Alan Beaulieu]] '''(in progress)'''
-
*[[Deadlocks and How to Avoid Them]]
+
*[[Deadlocks and How to Avoid Them]] by [[Tim Chapman]] '''(in progress)'''
*[[Why You Need to Use Bind Variables]]
*[[Why You Need to Use Bind Variables]]
-
*[[The Difference Between Truncation and Deletion]]
+
*[[The Difference Between Truncation and Deletion]] by [[Tim Chapman]] '''(ready for editing)'''
*[[Know When to use Transactions]] by [[Mark Richards]] '''(ready for editing)'''
*[[Know When to use Transactions]] by [[Mark Richards]] '''(ready for editing)'''
-
*[[Use the ANSI-Approved Join Syntax]]
+
*[[Use the ANSI-Approved Join Syntax]] by [[Alan Beaulieu]] '''(in progress)'''
*[[There’s More Than One Set Operator: Use Them!]]
*[[There’s More Than One Set Operator: Use Them!]]
*[[Use CASE Expressions]]
*[[Use CASE Expressions]]
-
*[[Tables Are Just One of the Things You Can Put in a FROM Clause]]
+
*[[Tables Are Just One of the Things You Can Put in a FROM Clause]] by [[Alan Beaulieu]] '''(in progress)'''
*[[Adopt a Coding Standard Before You Begin Coding]] by [[Chris Woodruff]] '''(in progress)'''
*[[Adopt a Coding Standard Before You Begin Coding]] by [[Chris Woodruff]] '''(in progress)'''
*[[Exceptions Happen; Deal With Them]]
*[[Exceptions Happen; Deal With Them]]
Line 160: Line 162:
*[[How to Use XML Docs as Data Sources]]
*[[How to Use XML Docs as Data Sources]]
*[[How to Use Functions as Data Sources]]
*[[How to Use Functions as Data Sources]]
-
*[[Using the WITH Clause]]
+
*[[Using the WITH Clause]] by [[Alan Beaulieu]] '''(in progress)'''
-
*[[Do More in SQL, Less in Procedural Code]] by [[Alan Beaulieu]]
+
*[[Do More in SQL, Less in Procedural Code]] by [[Alan Beaulieu]] '''(in progress)'''
*[[Doing More with SQL: Minimize Cursor Use]]
*[[Doing More with SQL: Minimize Cursor Use]]
*[[Doing More with SQL: Merge and Multi-table Insert]]
*[[Doing More with SQL: Merge and Multi-table Insert]]
Line 167: Line 169:
*[[Doing More with SQL: Pivots]]
*[[Doing More with SQL: Pivots]]
*[[Doing More with SQL: Cube, Rollup, and Grouping Sets]]
*[[Doing More with SQL: Cube, Rollup, and Grouping Sets]]
-
*[[The Case for CASE Expressions]] by [[Larry Rockoff]] '''(ready for editing)'''
 
*[[Queues as alternative for distributed transactions]] by [[Gert E.R. Drapers]]
*[[Queues as alternative for distributed transactions]] by [[Gert E.R. Drapers]]
*[[Your schema is your public contract]] by [[Gert E.R. Drapers]]
*[[Your schema is your public contract]] by [[Gert E.R. Drapers]]
Line 173: Line 174:
===Performance and Tuning Issues===
===Performance and Tuning Issues===
-
*[[What is the Optimizer, and What Does it Do?]]
+
*[[What is the Optimizer, and What Does it Do?]] by [[Charles A. Bell]] '''(in progress)'''
*[[Understanding Execution Plans]]
*[[Understanding Execution Plans]]
*[[Covering Your Queries]]
*[[Covering Your Queries]]
Line 190: Line 191:
*[[How to Write a Unit Test]]
*[[How to Write a Unit Test]]
*[[Design the Tests First, Then Code]]
*[[Design the Tests First, Then Code]]
-
*[[Data Types and Testing]] by [[Karen N. Johnson]]
+
*[[Data Types and Testing]] by [[Karen N. Johnson]] (ready for editing)
*[[Think Like a Tester]] by [[Karen N. Johnson]] (ready for editing)
*[[Think Like a Tester]] by [[Karen N. Johnson]] (ready for editing)
Line 202: Line 203:
*[[Putting it All Together with Pipelined, Parallel-Enabled Table Functions]]
*[[Putting it All Together with Pipelined, Parallel-Enabled Table Functions]]
*[[Building an Object-Oriented Interface to Your Relational Data]]
*[[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===
===SQL Server-Specific Issues===
*[[Generating Custom Datasets Using Table-Valued Functions]]
*[[Generating Custom Datasets Using Table-Valued Functions]]
Line 210: Line 213:
===MySQL-Specific Issues===
===MySQL-Specific Issues===
*[[MySQL Server Engines - What They Are, and When to Use Which One]] by [[Richard Sonnen]] '''(Ready for Editing)'''
*[[MySQL Server Engines - What They Are, and When to Use Which One]] by [[Richard Sonnen]] '''(Ready for Editing)'''
-
*[[Replication]] by [[Charles Bell]] '''(in progress)'''
+
*[[Replication]] by [[Charles A. Bell]] '''(in progress)'''
-
*[[Backup and Recovery]] by [[Charles Bell]] '''(in progress)'''
+
*[[Backup and Recovery]] by [[Charles A. Bell]] '''(Ready for Editing)'''
-
*[[High Availability and Scale Out]] by [[Charles Bell]] '''(in progress)'''
+
*[[High Availability and Scale Out]] by [[Charles A. Bell]] '''(in progress)'''
-
*[[Mastering the Binary Log]] by [[Charles Bell]] '''(in progress)'''
+
*[[Mastering the Binary Log]] by [[Charles A. Bell]] '''(in progress)'''

Current revision

This is the Commons page for the book 97 Things Every SQL Developer Should Know, edited by Alan Beaulieu.

Contents

Concept

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.

Editor Bio

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 basis - 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, your 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.

Contributions

Please add your contributions here. Click the [edit] 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)
  • (complete)


The Basics

Hardware Issues

Architecture

Database Design

Administration Issues

Security Issues

Programming Strategies

Performance and Tuning Issues

Testing Issues

Oracle-Specific Issues

SQL Server-Specific Issues

MySQL-Specific Issues

Personal tools