Before doing anything, please consider the questions posed by @RDFozz in a comment on the question, namely:
Are there any other sources besides [Q].[G]
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.
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. [Q].[G]
) 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.
[G]
is ETLed over to[P]
. If[G]
isvarchar
, 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 bevarchar
doesn't mean thatnvarchar
data couldn't be added tomorrow. Similarly, it's possible that whatever is consuming the data in[P]
needsnvarchar
data.