To be fair, the format in which data is stored in the DB and the format used for importing data are two entirely different things.
If you want to treat data as a stream of bytes hardcore UTF8 & PHPesque style (this function is "binary safe" woo) with no regard to the actual text involved, feel free to store it a bytes. SQL Server supports that.
If you want to store it as unicode text feel free to use the ntext and nvarchar types. I'm pretty sure that's what you intend to do anyway, even though you insist on calling it UTF8.
I'm not the original complainer about UTF8 support, but "If you want to store it as unicode text feel free to use the ntext and nvarchar types." comes at a price: for the o so common almost-ASCII text collections, it blows up your disk usage and I/O bandwidth for actual data by a factor of almost 2. For shortish fields, the difference probably isn't that, but if you store, say, web pages or blog posts, it can add up.
char [ ( n ) ] Fixed-length, non-Unicode string data.
http://technet.microsoft.com/en-us/library/ms186939.aspx
Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.
So, (var)char is "non-Unicode", and n(var)char is UCS-2 only.
That is in agreement with http://blogs.msdn.com/b/qingsongyao/archive/2009/04/10/sql-s..., which claims the glass is half full ("In summary, SQL Server DOES support storing all Unicode characters; although it has its own limitation.")
On the other hand, we have http://msdn.microsoft.com/en-us/library/ms143726.aspx that seems to state that SQL Server 2012 has proper unicode collations. UTF8 still is nowhere to be found, though.