Over collations, codepages en Unicode

Dit artikel gaat over een onderwerp (of eigenlijk onderwerpen) waarover velen de klok wel hebben horen luiden, maar lang niet iedereen goed weet waar de klepel precies hangt. Het gaat over collations, code pages, UTF-8, UTF-16, Unicode en dat in zowel de SQL Server database engine als Integration Services. Wanneer je op één SQL Server omgeving werkt, is het allemaal niet zo spannend. Binnen één bedrijf op één locatie waar alle servers door één beheerder op dezelfde manier zijn ingericht, is het ook (meestal) geen issue. En daarom kennen veel mensen die met SQL Server werken, hooguit het begrip collation wanneer ze wel eens zelf een installatie van SQL Server hebben gedaan en het een keer langs hebben zien komen.  Maar wanneer je in een internationale omgeving werkt waar SQL Server instances in bijvoorbeeld China staan, kan het wel een issue worden.

Collation

Eén van de redenen waarom dit onderwerp in mijn optiek een hoog klok en klepel gehalte heeft, is de spraakverwarring rond een tweetal belangrijke termen: collation vs. code page. Twee verschillende dingen, maar Microsoft voegt ze in SQL Server samen. Collation heeft in theorie alleen te maken met de manier waarop tekst vergeleken wordt en de manier waarop tekst gesorteerd wordt. Is bijvoorbeeld ‘Aap’ hetzelfde als ‘aap’? En als die twee woorden in een rijtje voorkomen, komt ‘Aap’ dan voor of na ‘aap’?

Code page

Een computer slaat gegevens op als getallen, of eigenlijk als bitpatronen. Met één byte oftewel 8 bits kunnen 256 combinaties worden gemaakt, oftewel de getallen 0 – 255. Door aan ieder karakter een getal toe te wijzen, kun je met één byte 256 karakters coderen. Een code page is dan ook niet meer dan een tabel met 256 tekens en de bijbehorende getallen waarmee die tekens gerepresenteerd worden. Het probleem bij deze code pages is dat je lang niet alle tekens in alle in de wereld gebruikte talen kunt coderen met één code page met 256 tekens. Vandaar dat er verschillende code pages zijn. Bij het opslaan van tekst in een computer, moet je dus weten met welke code page die tekst is gecodeerd om bij het weer uitlezen van de tekst de juiste presentatie te kunnen doen. Voorbeelden van code pages zijn 1252 (de Windows code page voor op Latijn gebaseerde talen, alle Westerse talen dus) of 936 voor simplified Chinese.

Collations en code pages in SQL Server

Zoals al gezegd, combineert Microsoft in SQL Server collations en code pages tot één begrip wat gezamenlijk weer collation wordt genoemd. Er zijn hierbij een groot aantal collations waarbij de collation de gebruikte code page bepaalt. Eén code page kan in heel veel collations gebruikt worden. De te gebruiken collation (en dus code page) kan op een aantal niveaus worden opgegeven: server, database, kolom en expressie. Wanneer je op een bepaald niveau geen collation kiest, wordt de collation van het bovenliggende niveau gebruikt.

LET OP: de server collation kan maar op één moment worden opgegeven: tijdens installatie! Hierbij lijkt het tijdens installatie alsof er een default collation is, maar deze is afhankelijk van de landinstellingen van de machine waarop de installatie wordt uitgevoerd. Hierdoor kunnen er zeer gemakkelijk verschillende collations op verschillende database omgevingen ontstaan wat tot hoofdpijn veroorzakende problemen kan leiden! Om de collation van een server te veranderen, moet je een herinstallatie doen, of alle database objecten en data exporteren, de master database herbouwen en alle database objecten en data weer importeren. Erg belangrijk om dus even op te letten tijdens een installatie van SQL Server!!!

Voorbeelden van collations in SQL Server zijn Latin1_General_CI_AS of Chinese_PRC_BIN. Als je wilt weten welke code page er door een collation wordt gebruikt, kun je de functie COLLATIONPROPERTY gebruiken. Het statement SELECT COLLATIONPROPERTY(‘Latin1_General_CI_AS’, ‘CodePage’) geeft bijvoorbeeld 1252 terug. Let op dat sommige collations veel op elkaar lijken maar toch net iets anders zijn. Naast Latin1_General_CI_AS is er bijvoorbeeld ook SQL_Latin1_General_CP1_CI_AS. Deze collation gebruikt dezelfde code page, maar de regels voor sorteren wijken net iets af. De collations waarvan de naam begint met SQL_, zijn SQL Server specifieke collations. Collations zonder deze prefix, zijn Windows collations. De SQL Server specifieke collations bestaan alleen nog voor backward compatibility met oudere SQL Server versies. Voor nieuwe omgevingen verdient het gebruik van de Windows collations de voorkeur in verband met compatibiliteit met Windows applicaties.

Unicode

Alle verschillende code pages die er zijn kunnen tot problemen leiden wanneer tekst uit verschillende bronnen op één plek samenkomt waar het vergeleken of samengevoegd moet worden. Hoe is de tekst gecodeerd? Welke code page is er gebruikt? De Unicode standaard lost dit probleem op. In plaats van een code voor een karakter dat afhankelijk is van de gebruikte code page, is er één unieke code per karakter, onafhankelijk van taal, land van herkomst, gebruikte computeromgeving, etc. Er is dus ook slechts één code page waarin alle karakters, leestekens, wiskundige symbolen, technische symbolen, wingdings, emoticons, etc. uit alle belangrijke talen in de wereld worden gecodeerd.

UTF-8, UTF-16 en UTF-32

Voor de daadwerkelijke vertaling van karakter naar bitpatroon, zijn er binnen de unicode standaard een drietal encoding forms gedefinieerd. Alle drie de formaten zijn volledig compatibel. Transformaties tussen de formaten kunnen, zonder verlies van data, worden uitgevoerd. De drie formaten zijn als volgt gedefinieerd (bron: http://www.unicode.org/standard/principles.html):

UTF-8 is popular for HTML and similar protocols. UTF-8 is a way of transforming all Unicode characters into a variable length encoding of bytes. It has the advantages that the Unicode characters corresponding to the familiar ASCII set have the same byte values as ASCII, and that Unicode characters transformed into UTF-8 can be used with much existing software without extensive software rewrites.

UTF-16 is popular in many environments that need to balance efficient access to characters with economical use of storage. It is reasonably compact and all the heavily used characters fit into a single 16-bit code unit, while all other characters are accessible via pairs of 16-bit code units.

UTF-32 is useful where memory space is no concern, but fixed width, single code unit access to characters is desired. Each Unicode character is  encoded in a single 32-bit code unit when using UTF-32.

Merk op dat, om alle karakters in de wereld te coderen, zelfs de 8-bit codering gebruikt in UTF-8 meer dan 8 bits per karakter kan gebruiken. Voor zowel UTF-8 als UTF-16 geldt dan ook dat de daadwerkelijke grootte van een karakter varieert. Alleen bij UTF-32 weet je zeker dat ieder karakter 4 bytes opslag vraagt. In Windows omgevingen, en dus ook SQL Server, wordt bij gebruik van Unicode UTF-16 gebruikt. UTF-32 wordt alleen in Unix omgevingen gebruikt.

Gebruik van Unicode

Gezien de vele voordelen van Unicode, is het sterk aan te bevelen altijd Unicode te gebruiken. Concreet: gebruik in SQL Server altijd nchar of nvarchar als datatype voor tekst kolommen, nooit char of varchar. Wanneer je non-unicode tekst in een Unicode kolom in een SQL Server tabel opslaat (bijvoorbeeld via SSIS), gaat de conversie ook altijd goed en raak je geen informatie kwijt.

SSIS en code pages

Ook binnen SSIS kom je het concept code page op meerdere plekken tegen. Zo heeft iedere data flow source en destination component code page informatie in de metadata van de kolommen staan. In onderstaand voorbeeld is een OLE DB source component op een database tabel geconfigureerd met één kolom van het type varchar waarbij de betreffende database een collation heeft waar code page 936 bij hoort (de metadata die SSIS laat zien, wordt alleen bij het aanmaken van een component bepaald).

Wanneer deze source component wordt verbonden met een destination component naar een database met een andere code page, krijg je een foutmelding: “The column “Chinees” cannot be processed because more than one code page (936 and 1252) are specified for it.”.

Om deze fout te vermijden, zijn een aantal oplossingen denkbaar:

1. De makkelijkste oplossing is ervoor te zorgen dat de collations voor source en destination identiek zijn… Niet altijd een realistische oplossing.
2. De beste oplossing is zowel in source als destination Unicode tekst te gebruiken, dus nvarchar als datatype. Niet altijd een haalbare of gewenste oplossing.
3. Als oplossing 1 en 2 niet haalbaar zijn, kan het datatype van de destination kolom gewijzigd worden in nvarchar waarna een data conversion component gebruikt wordt om de input van varchar naar nvarchar te converteren.

   
   Merk op dat wanneer je met Unicode tekst werkt, de code page niet meer gekozen kan worden. Er is immers maar één unieke code page!

4. Als ook oplossing 3 niet haalbaar is omdat het datatype van de destination al vast staat op varchar, kan het datatype van de source ook gecast worden naar de collation van de destination. Let wel dat hierbij informatie verloren kan gaan! Deze oplossing kan weer op meerdere manieren worden geïmplementeerd. Je kunt bijvoorbeeld in de query in de source component het volgende opnemen:

   SELECT Chinees COLLATE Latin1_General_CI_AS
   FROM Chinees

   Dat je hierbij informatie kwijt raakt, blijkt wel uit het resultaat waar in de eerste rij de kolom eigenlijk Chinese tekst bevat:

   

   Als deze methode acceptabel is, ook al gaat er tekst verloren, is er een andere manier. De source component in SSIS heeft twee properties die in dit verband van belang zijn: DefaultCodePage en AlwaysUseDefaultCodePage. De eerste property wordt gebruikt wanneer de bron geen code page informatie terug geeft. Bij SQL Server is dat niet het geval waardoor deze property niet direct gebruikt wordt. De tweede property is namelijk default False, waardoor de code page van de bron gebruikt wordt. Door deze property op True te zetten en als default code page 1252 te gebruiken, doe je feitelijk hetzelfde als wat het COLLATE statement in de SQL code doet, met in ieder geval één verschil: in de SQL code, moet de gebruikte collation beschikbaar zijn op de source server. Daarmee is deze tweede manier veiliger om te gebruiken.

   

Conclusie

Collations en code pages zijn twee verschillende dingen die gemakkelijk met elkaar verward worden. Zeker omdat de collation in SQL Server de gebruikte code page bepaald. Collation verschillen in verschillende SQL Server databases, kunnen leiden tot hoofdpijn veroorzakende issues bij het vergelijken, sorteren of kopiëren van tekst. Daarom is het van belang dat iedereen die met SQL Server werkt weet wat collations zijn en doen! Het simpelste medicijn tegen de hoofdpijn is altijd Unicode gebruiken. Als dat, om wat voor reden dan ook, niet mogelijk is, biedt casten naar de gewenste collation in T/SQL mogelijkheden. Daarnaast kunnen de DefaultCodePage en AlwaysUseDefaultCodePage properties van SSIS data source componenten van pas komen.