Wykryj, czy jakiekolwiek wartości w kolumnach NVARCHAR są w rzeczywistości Unicode

Odziedziczyłem niektóre bazy danych SQL Server. Jest jedna tabela (nazywam „G”), z około 86,7 milionami wierszy i 41 kolumnami, ze źródłowej bazy danych (nazywam „Q”) w SQL Server 2014 Standard, która przechodzi do ETL docelowa baza danych (nazywam „P”) o tej samej nazwie tabeli w SQL Server 2008 R2 Standard.

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

EDYCJA: 3/20/2017: Niektóre osoby zapytały, czy tabela źródłowa jest TYLKO źródłem tabeli docelowej. Tak, jest to jedyne źródło. Jeśli chodzi o ETL, nie zachodzi żadna prawdziwa transformacja; w rzeczywistości ma to być kopia danych źródłowych 1: 1. Dlatego nie ma planów dodawania dodatkowych źródeł do tej tabeli docelowej.

Nieco ponad połowa kolumn w [Q]. [G] to VARCHAR (tabela źródłowa):

• 13 kolumn to VARCHAR (80)
• 9 kolumn to VARCHAR (30)
• 2 kolumny to VARCHAR (8).

Podobnie, te same kolumny w [P]. [G] to NVARCHAR (tabela docelowa), z tą samą liczbą kolumn o tych samych szerokościach. (Innymi słowy, ta sama długość, ale NVARCHAR).

• 13 kolumn to NVARCHAR (80)
• 9 kolumn to NVARCHAR (30)
• 2 kolumny to NVARCHAR (8).

To nie jest mój projekt.

Chciałbym ZMIENIĆ [P]. [G] (docelowe) typy danych kolumn od NVARCHAR do VARCHAR. Chcę to zrobić bezpiecznie (bez utraty danych z konwersji).

Jak mogę spojrzeć na wartości danych w każdej kolumnie NVARCHAR w tabeli docelowej, aby potwierdzić, czy kolumna rzeczywiście zawiera dane Unicode?

Zapytanie (DMVs?), Które może sprawdzić każdą wartość (w pętli?) Każdej kolumny NVARCHAR i powiedzieć mi, czy DOWOLNA z wartości jest oryginalnym Unicode, byłoby idealnym rozwiązaniem, ale inne metody są mile widziane.

Załóżmy, że jedna z kolumn nie zawiera żadnych danych Unicode. Aby sprawdzić, czy musisz odczytać wartość kolumny dla każdego wiersza. O ile nie masz indeksu w kolumnie, z tabelą magazynu wierszy będziesz musiał odczytać każdą stronę danych z tabeli. Mając to na uwadze, myślę, że sensowne jest połączenie wszystkich kontroli kolumn w jedno zapytanie względem tabeli. W ten sposób nie będziesz czytał danych tabeli wiele razy i nie będziesz musiał kodować kursora ani innej pętli.

Aby sprawdzić jedną kolumnę, uwierz, że możesz to po prostu zrobić:

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

Rzut z NVARCHARna VARCHARpowinien dać taki sam wynik, chyba że występują znaki Unicode. Znaki Unicode zostaną przekonwertowane na ?. Tak więc powyższy kod powinien NULLpoprawnie obsługiwać przypadki. Masz 24 kolumny do sprawdzenia, więc sprawdzasz każdą kolumnę w jednym zapytaniu, używając agregatów skalarnych. Jedna implementacja jest poniżej:

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

Dla każdej kolumny otrzymasz wynik, 1jeśli którakolwiek z jej wartości zawiera Unicode. Dzięki 0temu wszystkie dane można bezpiecznie konwertować.

Zdecydowanie zalecam wykonanie kopii tabeli z nowymi definicjami kolumn i skopiowanie tam danych. Będziesz robił drogie konwersje, jeśli zrobisz to na miejscu, więc tworzenie kopii może nie być o wiele wolniejsze. Posiadanie kopii oznacza, że ​​możesz łatwo sprawdzić, czy wszystkie dane nadal tam są (jednym ze sposobów jest użycie słowa kluczowego EXCEPT ) i możesz bardzo łatwo cofnąć operację.

Pamiętaj też, że obecnie nie możesz mieć żadnych danych Unicode. Możliwe, że przyszły ETL może załadować Unicode do poprzednio czystej kolumny. Jeśli nie ma zaznaczenia tego w procesie ETL, powinieneś rozważyć dodanie tego przed wykonaniem tej konwersji.

Zanim cokolwiek zrobisz, rozważ pytania postawione przez @RDFozz w komentarzu do pytania, a mianowicie:

1. Czy istnieją jakieś inne źródła oprócz [Q].[G]wypełniania tej tabeli?

   Jeśli odpowiedź jest inna niż „Jestem w 100% pewien, że jest to jedyne źródło danych dla tej docelowej tabeli”, nie wprowadzaj żadnych zmian, niezależnie od tego, czy dane znajdujące się obecnie w tabeli mogą zostać przekonwertowane bez utrata danych.

2. Czy są jakieś plany / dyskusje związane z dodawaniem dodatkowych źródeł w celu zapełnienia tych danych w najbliższej przyszłości?

   I chciałbym dodać powiązane pytanie: Czy była jakakolwiek dyskusja wokół obsługi wielu języków w tabeli źródła prądu (tj [Q].[G]) poprzez przekształcenie go do NVARCHAR?

   Będziesz musiał zapytać, aby poznać te możliwości. Zakładam, że obecnie nie powiedziano ci niczego, co wskazywałoby w tym kierunku, inaczej nie zadawałbyś tego pytania, ale jeśli założono, że pytania te brzmią „nie”, należy je zadać i zadać wystarczająco szeroka publiczność, aby uzyskać najbardziej dokładną / kompletną odpowiedź.

Głównym problemem tutaj jest nie tyle posiadanie punktów kodowych Unicode, których nie można przekonwertować (kiedykolwiek), ale przede wszystkim posiadanie punktów kodowych, które nie wszystkie pasują do jednej strony kodowej. To miła rzecz w Unicode: może przechowywać znaki ze WSZYSTKICH stron kodowych. Jeśli konwertujesz z NVARCHAR- gdzie nie musisz się martwić o strony kodowe - na VARCHAR, to musisz upewnić się, że sortowanie w docelowej kolumnie używa tej samej strony kodowej, co w kolumnie źródłowej. Zakłada się, że ma ono jedno źródło lub wiele źródeł korzystających z tej samej strony kodowej (choć niekoniecznie tego samego sortowania). Ale jeśli istnieje wiele źródeł z wieloma stronami kodowymi, możesz potencjalnie napotkać następujący 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;

Zwraca (2. zestaw wyników):

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

Jak widać, wszystkie z tych znaków można przekonwertować VARCHAR, ale nie w tej samej VARCHARkolumnie.

Użyj następującego zapytania, aby ustalić, jaka jest strona kodowa dla każdej kolumny tabeli źródłowej:

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

TO POWIEDZIAŁO ....

Wspomniałeś, że jesteś na SQL Server 2008 R2, ALE, nie powiedziałeś, która edycja. JEŚLI akurat korzystasz z wersji Enterprise Edition, zapomnij o tych wszystkich rzeczach związanych z konwersją (ponieważ prawdopodobnie robisz to tylko w celu zaoszczędzenia miejsca) i włącz kompresję danych:

Implementacja kompresji Unicode

Jeśli używasz Wersji standardowej (a teraz wydaje się, że masz 😞), istnieje jeszcze jedna długa szansa: uaktualnienie do SQL Server 2016, ponieważ dodatek SP1 umożliwia wszystkim wersjom korzystanie z kompresji danych (pamiętaj, powiedziałem „dalekie ujęcie” „😉).

Oczywiście teraz, gdy zostało już wyjaśnione, że istnieje tylko jedno źródło danych, nie masz się czym martwić, ponieważ źródło nie może zawierać żadnych znaków tylko Unicode lub znaków poza określonym kodem strona. W takim przypadku jedyną rzeczą, na którą powinieneś zwrócić uwagę, jest użycie tego samego sortowania co kolumny źródłowej lub przynajmniej takiego, który używa tej samej strony kodowej. Oznacza to, że jeśli używasz kolumny źródłowej SQL_Latin1_General_CP1_CI_AS, możesz użyć jej Latin1_General_100_CI_ASw miejscu docelowym.

Gdy już wiesz, z którego sortowania korzystać, możesz:

• ALTER TABLE ... ALTER COLUMN ...być VARCHAR(należy podać bieżące NULL/ NOT NULLustawienie), co wymaga trochę czasu i dużo miejsca w dzienniku transakcji dla 87 milionów wierszy, LUB

• Utwórz nowe kolumny „ColumnName_tmp” dla każdej z nich i powoli wypełniaj poprzez UPDATEwykonanie TOP (1000) ... WHERE new_column IS NULL. Po wypełnieniu wszystkich wierszy (i sprawdzeniu, czy wszystkie zostały poprawnie skopiowane! Może być potrzebny wyzwalacz do obsługi UPDATE, jeśli istnieją), w jawnej transakcji, użyj sp_renamedo zamiany nazw kolumn „bieżących” kolumn na „ _Old ”, a następnie nowe kolumny„ _tmp ”, aby po prostu usunąć„ _tmp ”z nazw. Następnie wywołaj sp_reconfiguretabelę, aby unieważnić wszelkie buforowane plany odwołujące się do tabeli, a jeśli są jakieś widoki odwołujące się do tabeli, musisz zadzwonić sp_refreshview(lub coś w tym rodzaju). Po sprawdzeniu poprawności aplikacji i ETL działa z nią poprawnie, możesz upuścić kolumny.

Mam z tym pewne doświadczenie, kiedy miałem prawdziwą pracę. Ponieważ w tym czasie chciałem zachować dane podstawowe, a także musiałem uwzględnić nowe dane, które mogą zawierać znaki, które zgubiłyby się w tasowaniu, wybrałem kolumnę obliczeniową, która nie przetrwała.

Oto szybki przykład użycia kopii bazy danych Super User z zrzutu danych SO .

Od razu widzimy, że istnieją DisplayNames ze znakami Unicode:

Dodajmy więc kolumnę obliczeniową, aby dowiedzieć się, ile! Kolumna DisplayName to 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

Liczba zwraca ~ 3000 wierszy

Plan wykonania jest jednak trochę trudny. Zapytanie kończy się szybko, ale ten zestaw danych nie jest strasznie duży.

Ponieważ kolumny obliczane nie muszą być utrwalane w celu dodania indeksu, możemy wykonać jedną z następujących czynności:

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

Co daje nam nieco bardziej uporządkowany plan:

Rozumiem, że jeśli nie jest to odpowiedź, ponieważ wiąże się to ze zmianami architektonicznymi, ale biorąc pod uwagę rozmiar danych, prawdopodobnie próbujesz dodać indeksy, aby poradzić sobie z zapytaniami, które i tak same dołączają do tabeli.

Mam nadzieję że to pomoże!

Korzystając z przykładu Jak sprawdzić, czy pole zawiera dane Unicode , możesz odczytać dane w każdej kolumnie i wykonać CASTponiższe czynności:

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