3

I'm already quite comfortable with using COMPRESS() and DECOMPRESS() in an internal forum software for our company (Currently in SQL Server 2017), but trying to make the database as efficient as possible, is there an advantage to adding _UTF-8 to my current collation as in Latin1_General_100_CI_AS_SC_UTF8 upon future migration to SQL Server 2019?

  • Don't rush it. UTF8 is most useful when the data needs that encoding, eg web content, data that comes from or is sent to UTF8 endpoints (REST services, UTF8 data files etc). It's also needed in Linux environments where UTF8 is assumed at the system level - programs like R use single-byte arrays assuming the environment codepage will be set at UTF8. – Panagiotis Kanavos Nov 1 '18 at 10:35
4

Here's a list of recommended uses taken from here:

The UTF-8 encoding, being a variable-length encoding, can be a huge benefit in some scenarios, but it can also make things worse in others. Unfortunately, there is very little use for a “_UTF8” encoding given that Data Compression and Clustered Columnstore Indexes are available across all editions of SQL Server. The only scenario that truly benefits from a UTF-8 encoding is one in which all of the following conditions are true:

  1. Data is mostly standard ASCII (values 0 – 127), but either has, or might have, a small amount of a varying range of Unicode characters (more than would be found on a single 8-bit Code Page, or might not exist on any 8-bit Code Page).
  2. Column is currently (or otherwise would be) NVARCHAR(MAX) (meaning, data won’t fit into NVARCHAR(4000)).
  3. There is a lot of data for this column or set of columns (1 GB or more when stored in NVARCHAR).
  4. Performance would be negatively impacted by making the table a Clustered Columnstore table (due to how the table is used) OR data is typically < 8000 bytes There is no desire to make the column VARBINARY(MAX), use COMPRESS() for INSERT and UPDATE operations, and use DECOMPRESS() for SELECT queries (no need to worry about lack of ability to index the VARBINARY value since it is MAX data anyway that cannot be indexed). Keep in mind that the Gzipped value will be much smaller than even the UTF-8 version of the string, though it would require decompressing before the value could be filtered on (outside of “=”) or manipulated.
  5. The benefits of reducing the size of backups and reducing the time it takes to backup and restore, and reducing the impact on the buffer pool, outweigh the cost of the likely negative impact on query performance (for both CPU and elapsed times). Just keep in mind that Backup Compression (available in Enterprise and Standard Editions) might help here.

Storing HTML pages is a good example of a scenario that fits this description. UTF-8 is, of course, the preferred encoding for the interwebs precisely due to it using the minimal space for the most common characters while still allowing for the full range of Unicode characters.

| improve this answer | |
6

trying to make the database as efficient as possible

There are at least two different types of efficiency that are really at play here:

  1. space (disk and memory)
  2. speed

Under certain conditions (as described in Outman's answer, which is a copy/paste of the "Recommended Uses / Guidance" section of my blog post, linked at the top of that answer) you can save space, but that is entirely dependent on the type and per-row quantity of characters.

However, at least in its current implementation, you are more likely than not to have a decrease in speed. This could be due to how they are handling the UTF-8 data internally. I know that when comparing UTF-8 data to non-UTF-8 VARCHAR data, both values are converted to UTF-16 LE (i.e. NVARCHAR). I wouldn't be surprised if other (perhaps even most) operations needed to convert the UTF-8 data into NVARCHAR given that is how Windows / SQL Server / .NET have always handled Unicode.

So, assuming that you have a scenario that could possibly benefit from using UTF-8, you need to choose which efficiency is more important.

Now, whether or not UTF-8 will benefit scenarios where the environment itself is naturally UTF-8 (e.g. Linux) remains to be seen. Typically the database driver (ODBC, SQL Native Client, etc) handles the translation between client and server. I suppose there could be a performance / efficiency gain here if doing this would result in the driver software skipping the additional steps (and CPU cycles) it takes to do those encoding translations. So far this is just a theory as I have not tested it.

Just keep in mind the following:

  1. UTF-8 was designed to achieve ASCII compatibility for easier implementation. This allows systems that are Standard ASCII-based (values 0 - 127; values 128 - 255 are Extended ASCII and not covered by this) to enable Unicode without having to re-save anything in a new encoding.

    For SQL Server, the goal was that existing apps that are currently using VARCHAR can start supporting Unicode without needing to do much re-coding (i.e. adding N prefixes to string literals) or updating datatypes from VARCHAR to NVARCHAR.

    It was not designed to be a form of compression. If you have data that has a reduced footprint in UTF-8, then great. But when working with data that is not Standard ASCII, then either won't have any savings, or even worse, you might increase the data size by going to UTF-8 (given that 63k of the 65k BMP characters are 3 bytes in UTF-8, which is 1 byte more than the 2 bytes they require in UTF-16).

    And, if UTF-8 gives a performance gain, or at least you don't see a drop in performance, then great. But, don't expect it. In fact, don't be surprised if you happen to see a decrease in performance.

  2. If you decide to implement the UTF-8 collations in SQL Server, you need to be aware of some potential data "issues":

    1. Data loss from mixing UTF-8 string literals and/or variables (due to the current database having a UTF-8 default collation) and non-UTF-8 VARCHAR columns. This is caused by collation precedence effectively downgrading the collation from UTF-16 to whatever code page the column is using.
    2. Minor truncation from mixing non-UTF-8 string literals and/or variables with UTF-8 columns (and, in some cases, variables). This is caused by certain characters requiring more bytes in UTF-8 than they did in their original encoding.
    3. Invalid byte sequences in UTF-8 can throw an error instead of returning the default replacement character "�". This is a different approach than has been taken so far with invalid sequences in any other 8-bit encoding or in UTF-16.
       

    For more details and examples, please see the "Things to Keep in Mind: Operational" section of my post: Native UTF-8 Support in SQL Server 2019: Savior or False Prophet?

| improve this answer | |
  • Any idea if TDS supports or will support UTF-8 directly? – David Browne - Microsoft Nov 1 '18 at 14:53
  • 1
    @DavidBrowne-Microsoft Great question. Looking at the TDS spec (upd 2018-09-12), it seems that data is always sent to SQL Server as UCS-2 LE. See section 2.2.1.4 SQL Batch. The Unicode link there goes to a general definition, but for TDS, Unicode is defined in section 2.2.5.1 General Rules at the bottom. It's possible they might try sending string results back as UTF-8. The doc doesn't say, but it should be similar to sending back a double-byte character set. But I haven't tested – Solomon Rutzky Nov 1 '18 at 15:43
  • 1
    I was looking at this to try and reduce size of data sent to ADF for a case where columns containing mainly latin text were declared as nvarchar. My quick test in SSMS with client statistics switched on indicated data size for "Bytes received from server" was exactly the same for SELECT CAST([EmailAddress] COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8 AS varchar(200)) FROM [AdventureWorks2017].[Person].[EmailAddress], (select top 50 * from sys.objects) d as standard nvarchar so I gave up on that. – Martin Smith Jan 30 at 18:18
  • @MartinSmith Thanks for confirming that. Not sure if COMPRESS / DECOMPRESS is an option as I have never even looked at ADF. Are you able to transform the data in any way? Or, actually, now that I think about it, can you convert the UTF-8 data into VARBINARY and send that? If the binary data is UTF-8 encoded values, and the destination column is UTF-8, then it should be able to take a direct insert of that VARBINARY value: SELECT CONVERT(VARBINARY(MAX), CONVERT(VARCHAR(MAX), N'ဪ' COLLATE Latin1_General_100_CI_AS_SC_UTF8));. And that would not be converted internally to UTF-16. – Solomon Rutzky Jan 30 at 19:22
  • This might be a viable approach. The copy activity is currently writing it to a UTF-8 encoded flat file just as a string with the hex representation but I'll dig around to see if there are any options available to avoid this – Martin Smith Jan 31 at 10:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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