主页 / dba / 问题 / 167489
Accepted
John G Hohengarten
Asked: 2017-03-18 11:58:44 +0800 CST

检测 NVARCHAR 列中的任何值是否实际上是 unicode

  • 772

我继承了一些 SQL Server 数据库。在 SQL Server 2014 Standard 上的源数据库（我称为“Q”）中有一个表（我称为“G”），大约有 8670 万行和 41 列宽，可以将 ETL 转移到在 SQL Server 2008 R2 Standard 上具有相同表名的目标数据库（我将称为“P”）。

即 [Q].[G] ---> [P].[G]

编辑：2017 年 3 月 20 日：有人问源表是否是目标表的唯一源。是的，它是唯一的来源。就 ETL 而言，并没有发生任何真正的转变。它实际上旨在成为源数据的 1:1 副本。因此，没有计划向此目标表添加其他源。

[Q].[G] 中超过一半的列是 VARCHAR（源表）：

  • 13 列是 VARCHAR(80)
  • 9 列是 VARCHAR(30)
  • 其中 2 列是 VARCHAR(8)。

同样，[P].[G] 中的相同列是 NVARCHAR（目标表），具有相同的列数和相同的宽度。（换句话说，长度相同，但 NVARCHAR）。

  • 13 列是 NVARCHAR(80)
  • 其中 9 列是 NVARCHAR(30)
  • 其中 2 列是 NVARCHAR(8)。

这不是我的设计。

我想将 [P].[G]（目标）列数据类型从 NVARCHAR 更改为 VARCHAR。我想安全地做到这一点（不会因转换而丢失数据）。

如何查看目标表中每个 NVARCHAR 列中的数据值，以确认该列是否实际包含任何 Unicode 数据？

可以检查每个 NVARCHAR 列的每个值（在循环中？）并告诉我是否有任何值是真正的 Unicode 的查询（DMV？）将是理想的解决方案，但也欢迎使用其他方法。

sql-server datatypes

4 个回答

  1. Best Answer

    假设您的一列不包含任何 unicode 数据。要验证您是否需要读取每一行的列值。除非您在列上有索引，否则对于行存储表，您将需要从表中读取每个数据页。考虑到这一点，我认为将所有列检查组合到针对表的单个查询中是很有意义的。这样您就不会多次读取表的数据，也不必编写游标或其他类型的循环。

    要检查单个列，请相信您可以这样做：

    SELECT COLUMN_1
FROM [P].[Q]
WHERE CAST(COLUMN_1 AS VARCHAR(80)) <> CAST(COLUMN_1 AS NVARCHAR(80));

    转换from NVARCHARtoVARCHAR应该给你相同的结果，除非有 unicode 字符。Unicode 字符将转换为?. 所以上面的代码应该NULL正确处理案例。您有 24 列要检查，因此您可以使用标量聚合检查单个查询中的每一列。一种实现如下：

    SELECT 
  MAX(CASE WHEN CAST(COLUMN_1 AS VARCHAR(80)) <> CAST(COLUMN_1 AS NVARCHAR(80)) THEN 1 ELSE 0 END) COLUMN_1_RESULT
...
, MAX(CASE WHEN CAST(COLUMN_14 AS VARCHAR(30)) <> CAST(COLUMN_14 AS NVARCHAR(30)) THEN 1 ELSE 0 END) COLUMN_14_RESULT
...
, MAX(CASE WHEN CAST(COLUMN_23 AS VARCHAR(8)) <> CAST(COLUMN_23 AS NVARCHAR(8)) THEN 1 ELSE 0 END) COLUMN_23_RESULT
FROM [P].[Q];

    对于每一列，您将获得其1任何值是否包含 unicode 的结果。结果0意味着可以安全地转换所有数据。

    我强烈建议使用新列定义制作表的副本并将数据复制到那里。如果您在适当的位置进行转换，您将进行昂贵的转换，因此制作副本可能不会那么慢。拥有一个副本意味着您可以轻松地验证所有数据是否仍然存在（一种方法是使用EXCEPT关键字）并且您可以非常轻松地撤消操作。

    另外，请注意，您目前可能没有任何 unicode 数据，未来的 ETL 可能会将 unicode 加载到以前干净的列中。如果在您的 ETL 过程中没有对此进行检查，您应该考虑在进行此转换之前添加它。

    • 10

  2. 在做任何事情之前，请考虑@RDFozz 在对该问题的评论中提出的问题，即：

    1. 除了填充此表之外，还有其他来源吗？[Q].[G]

      如果响应是“我 100% 确定这是此目标表的唯一数据源”之外的任何内容，则不要进行任何更改，无论当前表中的数据是否可以在没有的情况下进行转换数据丢失。

    2. 是否有任何与在不久的将来添加其他来源以填充此数据相关的计划/讨论？

      [Q].[G]我会添加一个相关的问题：是否有任何讨论通过将当前源表（即）中的多种语言转换为支持多种语言NVARCHAR？

      您需要四处打听才能了解这些可能性。我假设你目前没有被告知任何指向这个方向的东西，否则你不会问这个问题，但如果这些问题被认为是“不”，那么他们需要被问到，并被问到足够广泛的受众以获得最准确/完整的答案。

    这里的主要问题与其说是无法转换（永远）的 Unicode 代码点，不如说是拥有不适合单个代码页的代码点。这就是 Unicode 的好处：它可以保存所有代码页中的字符。如果您从NVARCHAR（无需担心代码页）转换为VARCHAR，则需要确保目标列的排序规则使用与源列相同的代码页。这假设有一个源或多个源使用相同的代码页（但不一定是相同的排序规则）。但是，如果有多个源代码页，那么您可能会遇到以下问题：

    DECLARE @Reporting TABLE
(
  ID INT IDENTITY(1, 1) PRIMARY KEY,
  SourceSlovak VARCHAR(50) COLLATE Slovak_CI_AS,
  SourceHebrew VARCHAR(50) COLLATE Hebrew_CI_AS,
  Destination NVARCHAR(50) COLLATE Latin1_General_CI_AS,
  DestinationS VARCHAR(50) COLLATE Slovak_CI_AS,
  DestinationH VARCHAR(50) COLLATE Hebrew_CI_AS
);

INSERT INTO @Reporting ([SourceSlovak]) VALUES (0xDE20FA);
INSERT INTO @Reporting ([SourceHebrew]) VALUES (0xE820FA);

UPDATE @Reporting
SET    [Destination] = [SourceSlovak]
WHERE  [SourceSlovak] IS NOT NULL;

UPDATE @Reporting
SET    [Destination] = [SourceHebrew]
WHERE  [SourceHebrew] IS NOT NULL;

SELECT * FROM @Reporting;

UPDATE @Reporting
SET    [DestinationS] = [Destination],
       [DestinationH] = [Destination]

SELECT * FROM @Reporting;

    返回（第二个结果集）：

    ID    SourceSlovak    SourceHebrew    Destination    DestinationS    DestinationH
1     Ţ ú             NULL            Ţ ú            Ţ ú             ? ?
2     NULL            ט ת             ? ?            ט ת             ט ת

    如您所见，所有这些字符都可以转换为VARCHAR，只是不在同一VARCHAR列中。

    使用以下查询来确定源表的每一列的代码页是什么：

    SELECT OBJECT_NAME(sc.[object_id]) AS [TableName],
       COLLATIONPROPERTY(sc.[collation_name], 'CodePage') AS [CodePage],
       sc.*
FROM   sys.columns sc
WHERE  OBJECT_NAME(sc.[object_id]) = N'source_table_name';

    话虽如此....

    您提到在 SQL Server 2008 R2 上，但是，您没有说是什么版本。如果您碰巧使用的是企业版，那么请忘记所有这些转换内容（因为您可能只是为了节省空间而这样做），并启用数据压缩：

    Unicode 压缩实现

    如果使用标准版（现在看来您是?），那么还有另一种可能性：升级到 SQL Server 2016，因为 SP1 包括所有版本使用数据压缩的能力（记住，我确实说过“远射“?）。

    当然，既然刚刚澄清了数据只有一个来源，那么您不必担心，因为该来源不能包含任何仅 Unicode 的字符，或者其特定代码之外的字符页。在这种情况下，您唯一需要注意的是使用与源列相同的排序规则，或者至少使用相同的代码页。意思是，如果源列正在使用SQL_Latin1_General_CP1_CI_AS，那么您可以Latin1_General_100_CI_AS在目的地使用。

    一旦您知道要使用什么排序规则，您可以：

    • ALTER TABLE ... ALTER COLUMN ...是VARCHAR（一定要指定当前NULL/NOT NULL设置），这需要一点时间和大量的事务日志空间来存储 8700 万行，或者

    • 为每个列创建新的“ColumnName_tmp”列，并通过UPDATEdoing慢慢填充TOP (1000) ... WHERE new_column IS NULL。一旦填充了所有行（并验证它们都正确复制！您可能需要一个触发器来处理更新，如果有的话），在显式事务中，使用sp_rename将“当前”列的列名交换为“ _Old”，然后是新的“_tmp”列，以简单地从名称中删除“_tmp”。然后调用sp_reconfigure该表以使引用该表的任何缓存计划无效，如果有任何引用该表的视图，您将需要调用sp_refreshview（或类似的东西）。一旦您验证了应用程序并且 ETL 可以正常使用它，您就可以删除这些列。

    • 5

  3. 当我有一份真正的工作时，我有一些经验。由于当时我想保留基础数据，而且我还必须考虑新数据可能包含在随机播放中丢失的字符，因此我使用了非持久化计算列。

    这是一个使用来自SO 数据转储的超级用户数据库副本的快速示例。

    我们可以立即看到存在带有 Unicode 字符的 DisplayName：

    坚果

    所以让我们添加一个计算列来计算有多少！DisplayName 列是NVARCHAR(40)。

    USE SUPERUSER

ALTER TABLE dbo.Users
ADD DisplayNameStandard AS CONVERT(VARCHAR(40), DisplayName)

SELECT COUNT_BIG(*)
FROM dbo.Users AS u
WHERE u.DisplayName <> u.DisplayNameStandard

    计数返回约 3000 行

    坚果

    不过，执行计划有点拖累。查询完成很快，但这个数据集并不是很大。

    坚果

    由于不需要持久化计算列来添加索引，我们可以执行以下操作之一：

    CREATE UNIQUE NONCLUSTERED INDEX ix_helper
ON dbo.Users(DisplayName, DisplayNameStandard, Id)

    这给了我们一个稍微整洁的计划：

    坚果

    我知道这是否不是答案，因为它涉及架构更改，但考虑到数据的大小，您可能正在考虑添加索引以应对自加入表的查询。

    希望这可以帮助！

    • 4

  4. 使用如何检查字段是否包含 unicode 数据中的示例，您可以读取每列中的数据并执行CAST以下操作并检查：

    --Test 1:
DECLARE @text NVARCHAR(100)
SET @text = N'This is non-Unicode text, in Unicode'
IF CAST(@text AS VARCHAR(MAX)) <> @text
PRINT 'Contains Unicode characters'
ELSE
PRINT 'No Unicode characters'
GO

--Test 2:
DECLARE @text NVARCHAR(100)
SET @text = N'This is Unicode (字) text, in Unicode'
IF CAST(@text AS VARCHAR(MAX)) <> @text
PRINT 'Contains Unicode characters'
ELSE
PRINT 'No Unicode characters'

GO
    • 1

相关问题