Converting from Unicode data to a particular Code Page employs what is known as the "Best-fit" strategy (as noted in @Paul's answer and in the link that @Martin noted in a comment on the Question). According to that MSDN page for Character Encoding in the .NET Framework:
Best-fit mapping is the default behavior for an Encoding object that encodes Unicode data into code page data...
But what exactly are these mappings? That MSDN page used to state the following:
Best-fit strategies vary for different code pages, and they are not documented in detail.
However, that was not entirely correct. Perhaps the "strategies" for determining the mappings are not exactly documented. Ok. But, the mappings themselves are documented, just not in the easiest of places to find.
So, thanks to Microsoft moving the documentation to GitHub, that page now states the following (because I updated it 😸 ):
Best-fit strategies are not documented in detail. However, several code pages are documented at the Unicode Consortium's website. Please review the readme.txt file in that folder for a description of how to interpret the mapping files.
If you go to the following URL you will see a list of several files, each one named for the Code Page that it maps Unicode characters to:
ftp://ftp.unicode.org/Public/MAPPINGS/VENDORS/MICSFT/WindowsBestFit/
Most of the files were last updated (or at least placed there) on 2006-10-04, and one of them was updated on 2012-03-14. The first part of those files maps ASCII codes into an equivalent Unicode Code Point. But the second part of each file maps the Unicode characters into their ASCII "equivalents".
I wrote a test script that uses the Code Page 1252 mappings to check if SQL Server is truly using those mappings. That can be determined by answering these two questions:
- For all mapped Code Points, does SQL Server convert them into the specified mappings ?
- For all unmapped Code Points, does SQL Server convert any of them into a non-"
?
" character?
The test script is too long to place here, so I posted it on Pastebin at:
Unicode to Code Page mappings in SQL Server
Running the script will show that the answer to the first question above is "Yes" (meaning that all of the provided mappings are adhered to). It will also show that the answer to the second question is "No" (meaning, none of the unmapped Code Points convert into anything but the character for "unknown"). Hence, that mapping file is very accurate :-).