14

I have inherited some SQL Server databases. There is one table (I'll call "G"), with about 86.7 million rows, and 41 columns wide, from a source database (I'll call "Q") on SQL Server 2014 Standard that gets ETL'd over to a target database (I'll call "P") with the same table name on SQL Server 2008 R2 Standard.

i.e. [Q].[G] ---> [P].[G]

EDIT: 3/20/2017: Some people have asked if the source table is the ONLY source to the target table. Yes, it is the only source. As far as the ETL goes, there isn't any real transformation happening; it effectively is intended to be a 1:1 copy of the source data. Therefore, there are no plans to add additional sources to this target table.

A little over half of the columns in [Q].[G] are VARCHAR (source table):

  • 13 of the columns are VARCHAR(80)
  • 9 of the columns are VARCHAR(30)
  • 2 of the columns are VARCHAR(8).

Similarly, the same columns in [P].[G] are NVARCHAR (target table), with the same # of columns with the same widths. (In other words, same length, but NVARCHAR).

  • 13 of the columns are NVARCHAR(80)
  • 9 of the columns are NVARCHAR(30)
  • 2 of the columns are NVARCHAR(8).

This is not my design.

I'd like to ALTER [P].[Q] (target) columns data types from NVARCHAR to VARCHAR. I want to do it safely (without data loss from conversion).

How can I look at the data values in each NVARCHAR column in the target table to confirm whether or not the column actually contains any Unicode data?

A query (DMVs?) that can check each value (in a loop?) of each NVARCHAR column and tell me if ANY of the values is genuine Unicode would be the ideal solution, but other methods are welcome.

  • 2
    First, consider your process, and how the data is used. The data in [G] is ETLed over to [P]. If [G] is varchar, and the ETL process is the only way data comes into [P], then unless the process adds true Unicode characters, there shouldn't be any. If other processes add or modify data in [P], you need to be more careful - just because all the current data can be varchar doesn't mean that nvarchar data couldn't be added tomorrow. Similarly, it's possible that whatever is consuming the data in [P] needs nvarchar data. – RDFozz Mar 17 '17 at 20:53
10

Suppose one of your columns does not contain any unicode data. To verify that you would need to read the column value for every row. Unless you have an index on the column, with a rowstore table you will need to read every data page from the table. With that in mind I think it makes a lot of sense to combine all of the column checks into a single query against the table. That way you won't be reading the table's data many times and you don't have to code a cursor or some other kind of loop.

To check a single column believe that you can just do this:

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

A cast from NVARCHAR to VARCHAR should give you the same result except if there are unicode characters. Unicode characters will be converted to ?. So the above code should handle NULL cases correctly. You have 24 columns to check, so you check each column in a single query by using scalar aggregates. One implementation is below:

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];

For each column you will get a result of 1 if any of its values contain unicode. A result of 0 means that all data can be safely converted.

I strongly recommend making a copy of the table with the new column definitions and copying your data there. You'll be doing expensive conversions if you do it in place so making a copy might not be all that much slower. Having a copy means that you can easily validate that all of the data is still there (one way is to use the EXCEPT keyword) and you can undo the operation very easily.

Also, be aware that you might not have any unicode data currently it's possible that a future ETL could load unicode into a previously clean column. If there is not a check for this in your ETL process you should consider adding that before doing this conversion.

  • While the answer and discussion from @srutzky was quite good and had helpful info, Joe provided me with what my question was asking for: a query to tell me if any values in columns actually have Unicode. Therefore I marked Joe's answer as the accepted answer. I up-voted the other answers that also helped me. – John G Hohengarten Mar 20 '17 at 20:36
  • @JohnGHohengarten and Joe: That's fine. I didn't mention the query since it was in this answer as well as Scott's. I would just say that there is no need to convert the NVARCHAR column to NVARCHAR as it is already that type. And not sure how you determined the unconvertable character, but you can convert the column to VARBINARY to get the UTF-16 byte sequences. And UTF-16 is reverse byte order, so p = 0x7000 and then you reverse those two bytes to get Code Point U+0070. But, if the source is VARCHAR, then it can't be a Unicode character. Something else is going on. Need more info. – Solomon Rutzky Mar 21 '17 at 1:51
  • @srutzky I added the cast to avoid data type precedence issues. You may be correct that it isn't needed. For the other question, I suggested UNICODE() and SUBSTRING(). Does that approach work? – Joe Obbish Mar 21 '17 at 2:37
  • @JohnGHohengarten and Joe: data type precedence shouldn't be an issue as VARCHAR will implicitly convert to NVARCHAR, but it might be better to do CONVERT(NVARCHAR(80), CONVERT(VARCHAR(80), column)) <> column. SUBSTRING sometimes works, but it doesn't work with Supplementary Characters when using Collations that don't end in _SC, and the one John is using doesn't, though not likely an issue here. But converting to VARBINARY always works. And CONVERT(VARCHAR(10), CONVERT(NVARCHAR(10), '›')) doesn't result in ?, so I would want to see the bytes. ETL process might have converted it. – Solomon Rutzky Mar 21 '17 at 5:07
5

Before doing anything, please consider the questions posed by @RDFozz in a comment on the question, namely:

  1. Are there any other sources besides [G].[Q] populating this table?

    If the response is anything outside of "I am 100% certain that this is the only source of data for this destination table", then don't make any changes, regardless of whether or not the data currently in the table can be converted without data loss.

  2. Are there any plans / discussions related to adding additional sources to populate this data in the near future?

    And I would add a related question: Has there been any discussion around supporting multiple languages in the current source table (i.e. [G].[Q]) by converting it to NVARCHAR?

    You will need to ask around to get a sense of these possibilities. I assume you haven't currently been told anything that would point in this direction else you wouldn't be asking this question, but if these questions have been assumed to be "no", then they need to be asked, and asked of a wide-enough audience to get the most accurate / complete answer.

The main issue here is not so much having Unicode code points that can't convert (ever), but more so having code points that won't all fit onto a single code page. That is the nice thing about Unicode: it can hold characters from ALL code pages. If you convert from NVARCHAR – where you don't need to worry about code pages – to VARCHAR, then you will need to make sure that the Collation of the destination column is using the same code page as the source column. This assumes having either one source, or multiple sources using the same code page (not necessarily the same Collation, though). But if there are multiple sources with multiple code pages, then you can potentially run into the following problem:

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;

Returns (2nd result set):

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

As you can see, all of those characters can convert to VARCHAR, just not in the same VARCHAR column.

Use the following query to determine what the code page is for each column of your source table:

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';

THAT BEING SAID....

You mentioned being on SQL Server 2008 R2, BUT, you didn't say what Edition. IF you happen to be on Enterprise Edition, then forget about all this conversion stuff (since you are likely doing it just to save space), and enable Data Compression:

Unicode Compression Implementation

If using Standard Edition (and it now seems that you are 😞 ) then there is another looooong-shot possibility: upgrade to SQL Server 2016 since SP1 includes the ability for all Editions to use Data Compression (remember, I did say "long-shot" 😉 ).

Of course, now that it has just been clarified that there is only one source for the data, then you don't have anything to worry about since the source couldn't contain any Unicode-only characters, or characters outside of its specific code page. In which case, the only thing you should need to be mindful of is using the same Collation as the source column, or at least one that is using the same Code Page. Meaning, if the source column is using SQL_Latin1_General_CP1_CI_AS, then you could use Latin1_General_100_CI_AS at the destination.

Once you know what Collation to use, you can either:

  • ALTER TABLE ... ALTER COLUMN ... to be VARCHAR (be sure to specify the current NULL / NOT NULL setting), which requires a bit of time and a lot of transaction log space for 87 million rows, OR

  • Create new "ColumnName_tmp" columns for each one and slowly populate via UPDATE doing TOP (1000) ... WHERE new_column IS NULL. Once all rows are populated (and validated that they all copied over correctly! you might need a trigger to handle UPDATEs, if there are any), in an explicit transaction, use sp_rename to swap the column names of the "current" columns to be "_Old" and then the new "_tmp" columns to simply remove the "_tmp" from the names. Then call sp_reconfigure on the table to invalidate any cached plans referencing the table, and if there are any Views referencing the table you will need to call sp_refreshview (or something like that). Once you have validated the app and ETL is working correctly with it, then you can drop the columns.

  • I ran the CodePage query you provided on both source and target, and the CodePage is 1252 and collation_name is SQL_Latin1_General_CP1_CI_AS on BOTH source AND target. – John G Hohengarten Mar 20 '17 at 16:01
  • @JohnGHohengarten I just updated again, at the bottom. To be easy you can keep the same Collation, even though Latin1_General_100_CI_AS is much better than the one you are using. Easy meaning that the sorting and comparison behavior will be the same between them, even if not as good as the newer Collation I just mentioned. – Solomon Rutzky Mar 20 '17 at 16:24
4

I have some experience with this from back when I had a real job. Since at the time I wanted to preserve the base data, and I also had to account for new data that could possibly have characters that would get lost in the shuffle, I went with a non-persisted computed column.

Here's a quick example using a copy of the Super User database from the SO data dump.

We can see right off the bat that there are DisplayNames with Unicode characters:

Nuts

So let's add a computed column to figure out how many! The DisplayName column is 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

The count returns ~3000 rows

Nuts

The execution plan is a bit of a drag, though. The query finishes fast, but this data set isn't terribly large.

Nuts

Since computed columns don't need to be persisted to add an index, we can do one of these:

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

Which gives us a slightly tidier plan:

Nuts

I understand if this isn't the answer, since it involves architectural changes, but considering the size of the data, you're probably looking at adding indexes to cope with queries that self join the table anyway.

Hope this helps!

1

Using the example in How to check if a field contains unicode data, you could read the data in each column and do the CAST and check below:

--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

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.