19

I can't find option(s) directly to set UTF-8 rellated Collations/Charsets in SQL Server 2005/2008, same as is possible to set in another SQL engines, but in SQL Server 2005/2008 are there only Latin and SQL collations.

Is there some option to force/install these collations / charsets in SQL Server engine (for both ver.) 2005/2008 on Win2008 OS

Improve this question

4 Answers 4

Reset to default
15

No, there isn't. SQL Server doesn't support UTF-8.

You need to define your columns as nvarchar/nchar if you want unicode data. Note, internally SQL Server stores this as UCS-2.

Note that this has ben requested from MS on Connect and there is an older KB article. And some info on this blog too

Improve this answer
2
  • 6
    additionally, if you're going to be doing any text matching on an nvarchar with foreign characters, you need to match on a string formatted with an N before the string (e.g. N'οἰκονόμον').
    – swasheck
    Commented Mar 9, 2012 at 17:31
  • This is becoming a reality in SQL Server 2019, though it's still not something one should use unless they have an explicit need for it. Please see my answer for details.
    – Solomon Rutzky
    Commented Mar 14, 2019 at 18:54
3

Starting in SQL Server 2019 (currently in beta / "Community Tech Preview"), there is native support for UTF-8 via a new series of UTF-8 collations. HOWEVER, having the ability to use UTF-8 does not mean that you should. There are definite drawbacks to using UTF-8, such as:

  1. Only the first 128 code points are 1 byte (i.e. the standard 7-bit ASCII set)
  2. The next almost 2000 code points are 2 bytes, hence no space savings over UTF-16 / NVARCHAR
  3. The remaining 63k code points in the BMP (i.e. the U+0800 - U+FFFF range) are all 3 bytes, hence 1 byte larger than the same character in UTF-16 / NVARCHAR.
  4. Just have it stated: Supplementary Characters are 4 bytes in both encodings, so no space difference there
  5. While you might save space using UTF-8, there is a very good chance you will take a hit on performance for doing so.

What it really comes down to is this: UTF-8 is a storage format design to enable 8-bit systems (that were typically designed around ASCII and ASCII Extended -- Code Pages) to use Unicode without breaking anything or requiring any modification of existing files in order to keep things running. UTF-8 is wonderful for file systems and networking, but data stored inside SQL Server is neither. The fact that data that just happens to be mostly (or entirely) within the standard ASCII range requires less space than the same data when stored as UTF-16 / NVARCHAR is a side-effect. Sure, it's a side-effect that can prove useful, but that decision needs to be made by someone who understands both the data and the consequences / drawbacks of this decision. This is not a feature for general use.

Also, the main use-case for UTF-8 (in SQL Server) is for app code already using UTF-8, possibly already with another RDBMS that supports it, and there is no desire or ability to update the app code / DB schema to use NVARCHAR datatypes (for tables, variables, parameters,etc), or to prefix string literals with an uppercase "N". The goal is the same as the reason for UTF-8 existing: enable app code to use Unicode without changing the overall structure or rendering exist data invalid. If this describes your situation, then use UTF-8, but be aware that there are still a few bugs / issues with it.

If you do not have an explicit need for Unicode working without using NVARCHAR or uppercase "N" prefixed string literals, then the only other scenario where UTF-8 is a benefit is if you have A LOT of mostly standard ASCII data that needs to allow for Unicode characters, and you are using NVARCHAR(MAX) (which means that data compression won't work), and the table gets updated frequently (so Clustered Columnstore Index is probably not going to truly help).

For full details, please see my post:

Native UTF-8 Support in SQL Server 2019: Savior or False Prophet?

Improve this answer
2

You can't install UTF-8 as a character set because it's not a character set, it is an encoding.

If you want to store Unicode text you use the nvarchar data type.

If you want to store text encoded using UTF-8, you store it as binary data (varbinary).

Improve this answer
0

I my case, I had to display Arabic characters and my development database was in 2014, here things worked well. Here, in query I could see Arabic chars and my collation was SQL_Latin1_General_CP1256_CI_AS

But my production was in SQL server 2008 and eventually it din't supported UTF-8 charset. Here, I could see all ??????????? as UTF-8 is not supported in SQL 2008.

What all I did is changed all varchar to nvarchar and I could see Arabic char properly. Also I change my 2008 database collation to SQL_Latin1_General_CP1256_CI_AS

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.