What is the real meaning of the "Select a language [for] non-Unicode programs..." dialog?

Quick answer: Windows uses this setting as a guideline for converting between older 8-bit ASCII characters and modern 16-bit Unicode characters. Some applications abuse this setting to determine which language user interface should be shown.

The treatment of text files is application-specific , so this setting will not affect that.

Not entirely true. Here are some examples: (when set to Korean language for non-Unicode programs '\' (backslash) is replaced with '₩' (Korean currency symbol))

• In many applications all '\' appear as '₩' Take a look at gVim (a text editor). Note: the underlying data is still the same as when '\' appears. If you open this file on another computer, it will look like '\' again.
• All file paths are delimited by '₩'.
• Some applications, like Intel's video drivers, grab this setting to decide which language UI to display.

Background info:

Windows used to store strings with 8-bit ASCII characters. That is, each character used 8 binary digits. This allowed for only 256 different characters; not enough to represent all the written languages of the world. So different language regions decided to interpret the 8-bit characters differently to suit their own language (adding accented vowels, own currency sign in addition to '$', etc) These are called encodings. Applications must specifically tell Windows which encoding to use; many programs don't, so Windows assumes the local encoding for that version of Windows.

Now Windows stores strings with 16-bit Unicode characters. Each character uses twice as many bits. This allows for up to (almost) 65,536 different characters. Still not enough to represent all the written languages of the world, but Unicode also specifies a way to pick certain sets of characters from a much wider pool of characters.

Now what does Windows do when it runs an old program that does not know about Unicode? Windows has to convert between 8-bit ASCII characters and 16-bit Unicode characters. Windows can do this pretty well if the older application explicitly specifies the encoding, but many applications don't. So then Windows defaults to the setting in question: Language for Non-Unicode Programs.

Edit: addressing Joshua's comments

So, in addition to choosing the encoding for GUI strings, this setting can affect font choice (I guess the ₩ is simply a font representation for what appears as backslash in European fonts, no?)

Yes and no. Something called "font linking" is occurring to display the '₩' when a TrueType font does not include this character. Only '₩' is displayed in a different font. However, in the case of non-TrueType fonts, an entirely different font is used (for example, in the cmd console.) Also, currency symbols are a very special case. Windows does extra work to replace '\' with the local currency symbol to maintain the behavior of previous versions of Windows.

The Windows system fonts are also altered via something called "font substitution." In this case, the system fonts are totally replaced with a new font (although the name of the font remains the same). From my experience with Korean locale, the new font that is substituted does not look nearly as nice as the original font.

Does this setting affect anything else?

I have had rare experiences where using the Korean locale caused an application to not function correctly. One application I remember was a regular expression preview tool. It just did not produce the correct results when in Korean locale (or maybe it would not even start?) I had to switch to the US locale whenever I wished to use this program.

Unicode based platforms, such as Windows XP and Server 2003, emulate the language environment required to run non-Unicode applications by internally converting application's non-Unicode text data to Unicode using a system wide variable commonly called the "system locale" (or "language for non-Unicode applications"). The language of the non-Unicode applications should be the same script or family as the one defined by the "system locale". Failing to meet this condition results in display of garbage characters in the UI of the application.

Source.

Or:

This setting enables programs that do not support Unicode to display menus and dialog boxes in their native language by installing the necessary code pages and fonts. However, programs designed for other languages may not display text correctly.

Only non-Unicode programs are affected by this setting. The language you select for this option does not change the menus and dialog boxes of Windows or other Unicode programs.

from To display non-Unicode programs in their native language.

More info at Language for Non-Unicode Programs.