Norske tegn og sortering i SQL Server: Collation forklart

«Hvorfor står Ålesund øverst i lista?», «Hvorfor blir ø til et spørsmålstegn?» og «Cannot resolve the collation conflict» – alle tre handler om det samme: sortering, eller collation som det heter i SQL Server. Sorteringen bestemmer hvordan tekst sammenlignes og sorteres, om store og små bokstaver regnes som like, og hvilke tegn en varchar-kolonne kan lagre. Valget gjøres ofte i en fart under installasjonen og er tungvint å endre senere. Denne guiden forklarer hva du bør velge for norske data, og hvordan du løser problemene når valget allerede er gjort.

Kort fortalt
  • For norske data gir Danish_Norwegian_CI_AS eller Norwegian_100_CI_AS riktig alfabet: æ, ø og å kommer sist.
  • Med Latin1_General lagres æøå fint, men de sorteres feil etter norske regler.
  • Bruk nvarchar og N'...'-prefiks for tekst som kan inneholde andre tegn enn vesteuropeiske.
  • Sorteringskonflikter mot tempdb løses med COLLATE DATABASE_DEFAULT.

Hva bestemmer en sortering?

Navnet på en sortering forteller det meste. Danish_Norwegian_CI_AS betyr:

DelBetydning
Danish_NorwegianSpråkreglene for sortering og sammenligning, og hvilken tegntabell (kodeside 1252) varchar bruker.
_100Versjonen av sorteringsreglene. Nyere versjoner (100, 140) kjenner flere tegn og har riktigere regler.
CI / CSCase insensitive / sensitive: om 'Oslo' = 'OSLO'.
AI / ASAccent insensitive / sensitive: om 'é' = 'e'.
_SCStøtte for supplerende tegn (blant annet emoji) som ett tegn i strengfunksjoner.
_UTF8varchar lagres som UTF-8 (SQL Server 2019 og nyere).

Det finnes også eldre SQL-sorteringer med navn som SQL_Latin1_General_CP1_CI_AS. De er beholdt for bakoverkompatibilitet og er standard på servere med engelsk regional innstilling. Velg heller en Windows-sortering (uten SQL_-prefiks) for nye installasjoner.

Norsk alfabet: hvilken sortering bør du velge?

Forskjellen synes først når du sorterer:

SELECT Navn
FROM (VALUES (N'Ålesund'), (N'Oslo'), (N'Ørsta'), (N'Arendal'), (N'Ærø'), (N'Zürich')) AS t(Navn)
ORDER BY Navn COLLATE Latin1_General_100_CI_AS;
-- Ærø, Ålesund, Arendal, Ørsta, Oslo, Zürich  (feil for norsk: æ = «ae», å og ø som a og o)

SELECT Navn
FROM (VALUES (N'Ålesund'), (N'Oslo'), (N'Ørsta'), (N'Arendal'), (N'Ærø'), (N'Zürich')) AS t(Navn)
ORDER BY Navn COLLATE Norwegian_100_CI_AS;
-- Arendal, Oslo, Zürich, Ærø, Ørsta, Ålesund  (riktig)
SorteringKommentar
Danish_Norwegian_CI_ASDen klassiske «norske» sorteringen, standard på norske Windows-installasjoner. Gammel regelversjon (80), men svært utbredt.
Norwegian_100_CI_ASNyere regler for norsk. Godt valg for nye installasjoner. Finnes også som Norwegian_100_CI_AS_SC_UTF8.
Danish_Greenlandic_100_CI_ASEtterfølgeren for dansk. Sorterer æøå likt som norsk.
Latin1_General_100_CI_ASNøytral vesteuropeisk sortering. Lagrer æøå riktig, men sorterer dem som varianter av a og o.
SQL_Latin1_General_CP1_CI_ASAmerikansk standard. Unngå for nye norske løsninger.

Se alle tilgjengelige sorteringer med:

SELECT name, description
FROM sys.fn_helpcollations()
WHERE name LIKE N'Norwegian%' OR name LIKE N'Danish%';

Fella: «aa» er det samme som «å»

I norsk og dansk sortering behandles dobbel-a som den gamle skrivemåten for å. Det er riktig for «Aasen» og «Aalborg», men overrasker når du søker:

SELECT CASE WHEN N'Aase' = N'Åse' COLLATE Danish_Norwegian_CI_AS
            THEN 'Like' ELSE 'Ulike' END;    -- Like

SELECT CASE WHEN N'Aase' = N'Åse' COLLATE Latin1_General_100_CI_AS
            THEN 'Like' ELSE 'Ulike' END;    -- Ulike

Det gjelder også LIKE, ORDER BY, GROUP BY, DISTINCT og unike indekser: «Aas» og «Ås» kan regnes som duplikater. Ord som «Kraakeslottet» havner blant ordene på å. Lagrer du koder, brukernavn eller e-postadresser der «aa» bare er to bokstaver, gi kolonnen en binær eller nøytral sortering. Test gjerne sorteringen du velger med spørringen over før du bestemmer deg.

Server, database og kolonne

Sorteringen settes på tre nivåer, og hvert nivå arver fra nivået over når ingenting annet er angitt:

  • Server: Velges under installasjonen. Gjelder systemdatabasene, inkludert tempdb, og er standard for nye databaser.
  • Database: Settes med CREATE DATABASE ... COLLATE. Gjelder nye kolonner, variabler og strengkonstanter i databasen.
  • Kolonne: Kan settes per kolonne, for eksempel en case-sensitiv kolonne for passordhasher eller koder.
-- Hva bruker serveren, databasene og kolonnene?
SELECT SERVERPROPERTY('Collation') AS ServerSortering;

SELECT name, collation_name FROM sys.databases;

SELECT t.name AS Tabell, c.name AS Kolonne, c.collation_name
FROM sys.columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
WHERE c.collation_name IS NOT NULL
ORDER BY t.name, c.column_id;

«Cannot resolve the collation conflict»

Feilmeldingen kommer når to tekstverdier med ulik sortering sammenlignes, og SQL Server ikke vet hvilke regler som skal gjelde. Den klassiske årsaken er en database som er flyttet fra en annen server:

  • Databasen bruker Danish_Norwegian_CI_AS, men den nye serveren er installert med SQL_Latin1_General_CP1_CI_AS.
  • Midlertidige tabeller (#temp) opprettes i tempdb og får serverens sortering.
  • En JOIN mellom en vanlig tabell og en temptabell på en tekstkolonne feiler.

Løsningen i koden er å oppgi sorteringen eksplisitt:

-- Gi temptabellen databasens sortering når den opprettes
CREATE TABLE #Kunder
(
    Kundenr  int,
    Navn     nvarchar(100) COLLATE DATABASE_DEFAULT
);

-- Eller løs konflikten i selve sammenligningen
SELECT k.Navn
FROM dbo.Kunde AS k
JOIN #Import AS i ON i.Navn = k.Navn COLLATE DATABASE_DEFAULT;

Den varige løsningen er at serveren og databasene har samme sortering. Tenk på dette før du flytter en database til en ny server. I en contained database får temptabeller databasens sortering automatisk.

varchar, nvarchar og N-prefikset

Sorteringen bestemmer også hvilke tegn en varchar-kolonne kan lagre. Med vesteuropeiske sorteringer bruker varchar kodeside 1252, som har plass til æ, ø og å, men ikke for eksempel samiske tegn som ŋ og ŧ, polske tegn eller emoji. Slike tegn blir stille erstattet med ?.

DECLARE @v varchar(20)  = N'Čáhcesuolu ŋ';
DECLARE @n nvarchar(20) = N'Čáhcesuolu ŋ';
SELECT @v AS VarcharVerdi, @n AS NvarcharVerdi;
-- VarcharVerdi: Cáhcesuolu ?   NvarcharVerdi: Čáhcesuolu ŋ

-- Uten N-prefiks konverteres konstanten til databasens kodeside FØR den lagres
INSERT INTO dbo.Kunde (Navn) VALUES ('Łukasz');   -- lagres som «Lukasz» eller «?ukasz»
INSERT INTO dbo.Kunde (Navn) VALUES (N'Łukasz');  -- riktig
Navn, adresser og fritekst fra brukere bør være nvarchar. Norge har samiske språk og mange innbyggere med navn fra hele verden. Les mer om valget i guiden om datatyper.

Pass også på at applikasjonen sender parametere med riktig type. Sender den nvarchar mot en varchar-kolonne, kan SQL Server måtte konvertere kolonnen i stedet for parameteren, og indeksen blir ikke brukt. Det er en vanlig felle med Entity Framework Core.

UTF-8-sorteringer

Fra SQL Server 2019 finnes sorteringer med _UTF8-endelse, for eksempel Norwegian_100_CI_AS_SC_UTF8. Da lagrer varchar UTF-8 og kan inneholde alle Unicode-tegn. Det kan spare plass når teksten for det meste er ASCII, men husk:

  • Lengden i varchar(n) er i byte, ikke tegn. Æ, ø og å tar to byte hver, så «Bærum» trenger varchar(6).
  • For tekst med mange ikke-latinske tegn er nvarchar ofte like kompakt.
  • Endringen må gjøres per kolonne. Å endre databasens sortering konverterer ikke eksisterende data.

Bytte sortering

Kolonne

ALTER TABLE dbo.Kunde
ALTER COLUMN Navn nvarchar(100) COLLATE Norwegian_100_CI_AS NOT NULL;

Indekser, statistikk, fremmednøkler og sjekkbegrensninger som bruker kolonnen må fjernes først og opprettes igjen etterpå.

Database

ALTER DATABASE Salg COLLATE Norwegian_100_CI_AS;

Dette endrer standarden for nye kolonner og for strengkonstanter, men eksisterende kolonner beholder sin sortering. Krever at ingen andre er koblet til databasen. For å endre alt må hver kolonne endres, eller databasen bygges opp på nytt og dataene flyttes over.

Server

Serverens sortering endres ved å bygge systemdatabasene på nytt med installasjonsprogrammet (setup.exe /ACTION=REBUILDDATABASE /SQLCOLLATION=...). Da forsvinner pålogginger, jobber og annen konfigurasjon i master og msdb, og brukerdatabasene må kobles til igjen. Ta backup av alt, og skript ut pålogginger og Agent-jobber først. Ofte er det enklere å installere en ny instans med riktig sortering.

Vanlige spørsmål

Hvilken sortering bør jeg velge for en ny norsk server?

Velg samme sortering som databasene du skal ha på serveren. For helt nye løsninger er Norwegian_100_CI_AS_SC (eller _UTF8-varianten) et godt valg. Kjører du programvare fra en leverandør, følg leverandørens krav – mange krever en bestemt sortering.

Hvorfor blir æøå til rare tegn som «Ã¸»?

Det er nesten alltid et tegnsettproblem utenfor databasen: UTF-8-tekst lest som Windows-1252 i en importfil, et skript eller en tilkobling. Sjekk kodingen i filen du importerer, og angi den eksplisitt, for eksempel CODEPAGE = '65001' i BULK INSERT.

Kan jeg sortere norsk i én spørring uten å endre tabellen?

Ja, med ORDER BY Navn COLLATE Norwegian_100_CI_AS. Men en indeks på kolonnen kan da ikke brukes til sorteringen eller til søk, så det passer for rapporter, ikke for store oppslag.

Er Azure SQL Database annerledes?

Standard er SQL_Latin1_General_CP1_CI_AS, men du kan velge sortering når databasen opprettes. Den kan ikke endres etterpå. Se guiden om Azure SQL.

Les også