UTF-8支持、SQL Server 2012和UTF8字符串UDT

人气:216 发布:2022-10-16 标签: utf-8 sql-server unicode sql-server-2012 user-defined-types

问题描述

在研究针对我的特定应用程序的SQL Server的VARCHAR和NVARCHAR的优缺点时,我意识到如果SQL Server本机支持UTF8将是最理想的。一些SO帖子表明它不是这样的,例如:

Is VARCHAR like totally 1990s?

What are the main performance differences between varchar and nvarchar SQL Server data types?

然而,后来我在SQL Server 2012的MSDN文档中看到了这篇文章,其中介绍了如何创建UTF8字符串用户定义的数据类型:

http://msdn.microsoft.com/en-us/library/ff877964(v=sql.110).aspx

UDT似乎允许每个字符8位的空间(内存、磁盘)优势,同时足够灵活地存储可以用UTF-8表示的任何字符串。对吗?此策略是否有不足之处(例如,为每行执行托管代码的性能成本...)?

SQLCLR

通过推荐答案创建自定义用户定义类型是而不是,无论如何,它都会为您带来任何本机类型的替换。对于创建处理特定数据的东西来说,它非常方便。但是,即使是不同编码的字符串,也远远不是专门化的。使用此方法获取字符串数据将破坏系统的任何可用性,更不用说性能了,因为您将无法使用任何内置字符串函数。

如果您能够在磁盘空间上节省任何东西,那么这些收益将被您在总体性能上的损失所抹去。存储UDT的方法是将其序列化为VARBINARY。因此,为了进行任何字符串比较或排序,在"二进制"/"序数"比较之外,您必须将所有其他值逐个转换回UTF-8,然后进行可以考虑语言差异的字符串比较。而这一转换需要在UDT内完成。这意味着,与XML数据类型一样,您将创建UDT以保存特定值,然后公开该UDT的方法以接受字符串参数进行比较(即Utf8String.Compare(alias.field1),或者,如果为该类型定义运算符,则Utf8string1 = Utf8string2并使=运算符获取UTF-8编码的字符串,然后执行CompareInfo.Compare())。

除了上述注意事项外,您还需要考虑通过SQLCLR API来回传送值是有代价的,特别是在使用NVARCHAR(MAX)VARBINARY(MAX)而不是分别使用NVARCHAR(1 - 4000)VARBINARY(1 - 4000)时(请不要将这种区别混淆为使用SqlChars/SqlBytesvsSqlString/SqlBinary)。

最后(至少就使用UDT而言),请不要忽视所查询的UDT是示例代码这一事实。唯一提到的测试是纯粹的功能性测试,不是关于可伸缩性的,也不是"使用一年后学到的教训"。下面的CodePlex页面显示了功能测试代码,在继续进行此决策之前应先查看该代码,因为它使您了解需要如何编写查询才能与其交互(对于一两个字段是可以的,但对于大多数/所有字符串字段不是):

http://msftengprodsamples.codeplex.com/SourceControl/latest#Kilimanjaro_Trunk/Programmability/CLR/UTF8String/Scripts/Test.sql

考虑到添加的持久化计算列和索引的数量,是否真的节省了空间?;-)

考虑空间(磁盘、内存等)的情况下,您有三种选择:

如果您使用的是SQL Server 2008或更高版本,并且使用的是Enterprise Edition,则可以启用Data Compression。数据压缩可以(但不会总是)压缩NCHARNVARCHAR字段中的Unicode数据。决定因素是:

NCHAR(1 - 4000)NVARCHAR(1 - 4000)使用Standard Compression Scheme for Unicode,但仅从SQL Server2008 R2开始,且仅用于IN行数据,不能溢出!这似乎比常规的行/页压缩算法要好。 NVARCHAR(MAX)XML(我猜还包括VARBINARY(MAX)TEXTNTEXT)在行中的数据(不在LOB或溢出页中的行外)至少可以进行页面压缩,可能也可以进行行压缩(不确定最后一个)。 任何行外数据、LOB或OVERLOW=无需压缩!

如果在Enterprise Edition上使用的版本早于2008或不是,您可以有两个字段:一个VARCHAR和一个NVARCHAR。例如,假设您存储的URL大多都是基本ASCII字符(值0-127),因此适合VARCHAR,但有时包含Unicode字符。您的架构可以包括以下3个字段:

  ...
  URLa VARCHAR(2048) NULL,
  URLu NVARCHAR(2048) NULL,
  URL AS (ISNULL(CONVERT(NVARCHAR([URLa])), [URLu])),
  CONSTRAINT [CK_TableName_OneUrlMax] CHECK (
                    ([URLa] IS NOT NULL OR [URLu] IS NOT NULL)
                AND ([URLa] IS NULL OR [URLu] IS NULL))
);

在此模型中,仅从[URL]计算列中选择。对于插入和更新,您可以通过查看转换是否会改变传入的值来确定要使用的字段,该值必须是NVARCHAR类型:

INSERT INTO TableName (..., URLa, URLu)
VALUES (...,
        IIF (CONVERT(VARCHAR(2048), @URL) = @URL, @URL, NULL),
        IIF (CONVERT(VARCHAR(2048), @URL) <> @URL, NULL, @URL)
       );

如果您的字段只应包含适合扩展ASCII字符集的特定代码页的字符,则只需使用VARCHAR

附注:仅为清楚起见:SQL Server 2012中引入的新_SC排序规则仅允许:

正确处理补充字符/代理项对的内置函数,以及 用于排序和比较的补充字符的语言规则 但是,即使没有新的_SC排序规则,您仍然可以将任何Unicode字符存储为XML或N前缀类型,并在不丢失数据的情况下检索它。但是,当使用较旧的归类时(即名称中没有版本号),所有补充字符彼此相等。您需要使用_90_100归类,它们至少可以进行二进制/码位比较和排序;它们不能考虑语言规则,因为它们没有补充字符的特定映射(因此没有权重或标准化规则)。

尝试以下操作:

IF (N'' = N'') SELECT N'' AS [TheLiteral], NCHAR(150150) AS [Generated];
IF (N'' = N'') SELECT N'' AS [TheLiteral], NCHAR(150151) AS [Generated];
IF (N'' COLLATE Tatar_90_CI_AI = N'' COLLATE Tatar_90_CI_AI)
       SELECT N' COLLATE Tatar_90_CI_AI' AS [TheLiteral], NCHAR(150151) AS [Generated];
IF (N'' = N'?') SELECT N'?';

在默认排序规则以_SC结尾的数据库中,只有第一个IF语句将返回结果集,并且"已生成"字段将正确显示字符。

但是,如果数据库没有以_SC结尾的默认排序规则,并且该排序规则不是_90_100系列排序规则,则前两个IF语句将返回结果集,其中"已生成"字段将返回NULL,而"文字"字段将正确显示。

对于Unicode数据,排序规则与物理存储无关。

更新2018-10-02

虽然这还不是一个可行的选择,但SQL Server 2019在VARCHAR/CHAR数据类型中引入了对UTF-8的本机支持。目前它有太多的错误,无法使用,但如果这些错误得到修复,则这是某些场景的一个选项。有关这一新功能的详细分析,请参阅我的帖子"Native UTF-8 Support in SQL Server 2019: Savior or False Prophet?"。

295