Diffraction affects your image sharpness by limiting Depth of Field and useful Resolution. See how our camera and lens choices influence these limits.

To increase Depth of Field we simply decrease aperture (larger f-stop). However, we cannot get infinite Depth of Field by decreasing our aperture infinitely. Diffraction establishes the upper limit to Depth of Field.

The subject may seem very technical, but the solution is far from being difficult. To understand this tutorial better, consider reading my Correct Exposure Series of Tutorials and my previous tutorial on Hyperfocal Distance, which explains the relationship of aperture and Depth of Field.

Diffraction is an optical effect limits the resolution / sharpness of our photograph. Since it is an optical effect, higher resolution sensors will not improve resolution further. Higher resolution cameras are thus more demanding on our optics and eventually will yield little to no improvement in total resolution. Diffraction plagues landscape photographers who are striving for a large depth of field and high-resolution images. Many of us are not even aware of this, blindly selecting a small aperture according to our calculators and charts.

  What is Diffraction?

Diffraction describes the interaction between waves and obstacles. It describes how waves deform to fill the space behind the object (the wave shadow so to speak). "Sound waves can diffract around objects, this is the reason we can still hear someone calling us even if we are hiding behind a tree." - quoted from Wikipedia.

(generated from this site)

In the two extreme examples above, I illustrated diffraction:
An object like the lens diaphragm will shape a wave passing it. The object deforms the wave. The smaller the opening of our diaphragm (or the smaller the aperture) the more the wave will be affected. The distortion of the wave will create a pattern on the sensor. We call this pattern the Airy Disc (after George Airy and not the lofty nature of the subject). It takes the form shown in the next figure.

Shape of the Airy Disc on the sensor plane

The diameter of the Airy Disc starts to increase with a decreasing aperture size. When the cone (the red and yellow) area is as large as your smallest resolution unit is, you will start to lose sharpness due to diffraction. The smallest resolution unit is the larger of your pixel size or your circle of confusion for a desired Depth of Field. In order to maximize the output resolution of your camera, the Airy Disc should not be much larger than your pixel. The pixel size for a Canon 20D is roughly 39 square microns.
To calculate the pixel size, simply divide the sensor area by the number of pixels. Although it is only a simplification, it is accurate enough for our purposes.
The sensor of the 20D is 22.5mmx15mm with 8.5 million total pixels. So (22500um x 15000um / 8,500,000 = 39um^2). Assuming square pixels, we get an edge of about 6.2um.
With this, we can approximate the diameter of the airy disk:

where λ is the wavelength of the light (about 450nm=0.45um), f/d is the f/stop of our aperture. For f/16 x=8.78um. The Airy Disc is thus already larger then our pixel limiting the resolution of our system. The theoretical limit for the 20D lies between f/11 to f/13.
Decreasing the aperture further to improve our Depth of Field will result in an overall sharpness loss throughout the picture.
By comparison, a Sony W200 (1/2.5" sensor with 12 Megapixel) has a pixel width of about 2um and will start showing the limits of diffraction at apertures smaller than 3.5. The benefit of 12 Megapixels in such a small sensor over its 8MP counterpart the W90 is virtually nonexistent.
Since we are actually interpolating the color information of each pixel from its adjacent pixels through a process called Bayer Interpolation, we already get a certain amount of un-sharpness. Knowing this, one can assume an absolute upper limit of two four pixels (Bayer Pattern) or two pixel widths. For my 20D this translates into a smallest usable aperture of f/22.
This means that the effect of diffraction will start to have an impact below f/11, but for apertures larger then f/22 the effect will be less visible due to the way the sensors work. With some sharpening, we can still improve the image.

The following pictures illustrate how the sensor can resolve two adjacent features.

Two adjacent non-overlapping airy discs that can be resolved

 
Two adjacent overlapping airy discs that cannot be resolved

Without lengthy analysis, we can conclude that two adjacent lines start to blur together at around f/11 for the 20D (at the onset of diffraction).

Diffraction Limited Lens

Optical aberrations - often plaguing consumer grade lenses - require "stopping down" in order to reduce their effect. These lenses have a limited "useful" aperture range. Aberrations limit one end of the scale, Diffraction the other.
Very high quality lenses are sharp even at wide-open apertures. Due to the effects of diffraction, they are sharpest wide open.
I recommend performing a series of tests for your lens - camera combination.

Using some of the above mentioned limitations of the camera itself, and neglecting any other effects, I calculated the Minimum usable Aperture for a range of popular camera systems. Column 3 lists the minimum Aperture for highest quality. 
 

This table does not indicate a maximum depth of field. Smaller sensors have a larger Depth of Field due to their focal length multiplication. Since the lens diameter and the distance from the sensor also limit the maximum aperture, the useful range for those cameras is very small.

Example Calculation

For a 1/2.5" sensor I calculated the pixel size like this:
The sensor diagonal dimension is z=25400um/2.5. With  y=3/4x (the aspect ratio) and with

I easily calculated x=8128um and y=6096um. I can now calculate the pixel area to be:

, and by finding the square root, I came up with the pixel dimension. Using the formula above, I calculated the aperture for an Airy disc with a diameter of the size of a single pixel and for the Airy disc the size of two pixels.

Conclusion

I think it is important not to read too much into all those numbers, but it is nice to understand why pictures are sometimes not as sharp as we might expect.
When you are buying your next digital camera, do not obsess too much about resolution and technical mambo jumbo. If you funds are limited, buy a cheap DSLR instead of the latest flashy point and shoot model. You will end up with much more creative options and better-looking pictures.
Limiting the useful range of apertures, Point and Shoot cameras make it next to impossible to create shallow depth of field images. Cluttered portraits with in-focus backgrounds distract from the subjects. At the other end of the scale, you cannot get more depth of field by simply using smaller apertures. Many P&S already limit the minimum aperture via software.

Postscript

I wrote this article as a reference for future tutorials on the correct exposure. It is not necessary to understand everything, but it is helpful to know there is such a thing as diffraction limit and how to deal with it.