The answer is: NO, there is no native support for UTF-8 in SQL (up to SQL 2008 R2). SQL uses UCS-2 to store unicode text. Unicode was introduced to standardize on the numerical representation for different charts sets for different languages. So, according to the Unicode Consortium:
Unicode provides a unique number for every character,
no matter what the platform,
no matter what the program,
no matter what the language.
Now there are different ways to represent Unicode. A commonly seen method is to use UTF-8 (Unicode Transformation Format — 8-bit). SQL server uses UCS-2 which stands for Universal Character Set-2, which is loosely reffered to as UTF-16. For a SQL DBA point of view, all I need to know is SQL represents Unicode in UCS-2 format.