I'm already quite comfortable with using
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?
I'm already quite comfortable with using
migrated from stackoverflow.com Nov 1 '18 at 13:14
This question came from our site for professional and enthusiast programmers.
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:
- 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).
- Column is currently (or otherwise would be) NVARCHAR(MAX) (meaning, data won’t fit into NVARCHAR(4000)).
- There is a lot of data for this column or set of columns (1 GB or more when stored in NVARCHAR).
- 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.
- 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.
trying to make the database as efficient as possible
There are at least two different types of efficiency that are really at play here:
- space (disk and memory)
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.
Also keep in mind that there are several bugs in the current UTF-8 implementation that make it very much not ready for Production use. They have supposedly fixed two of the minor ones in the next CTP, but there is still a major issue with
NULLs not always being handled properly by drivers (i.e. client connectivity) released prior to SQL Server 2019.
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.