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.
- For norske data gir
Danish_Norwegian_CI_ASellerNorwegian_100_CI_ASriktig alfabet: æ, ø og å kommer sist. - Med
Latin1_Generallagres æøå fint, men de sorteres feil etter norske regler. - Bruk
nvarcharogN'...'-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:
| Del | Betydning |
|---|---|
| Danish_Norwegian | Språkreglene for sortering og sammenligning, og hvilken tegntabell (kodeside 1252) varchar bruker. |
| _100 | Versjonen av sorteringsreglene. Nyere versjoner (100, 140) kjenner flere tegn og har riktigere regler. |
| CI / CS | Case insensitive / sensitive: om 'Oslo' = 'OSLO'. |
| AI / AS | Accent insensitive / sensitive: om 'é' = 'e'. |
| _SC | Støtte for supplerende tegn (blant annet emoji) som ett tegn i strengfunksjoner. |
| _UTF8 | varchar 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)
| Sortering | Kommentar |
|---|---|
| Danish_Norwegian_CI_AS | Den klassiske «norske» sorteringen, standard på norske Windows-installasjoner. Gammel regelversjon (80), men svært utbredt. |
| Norwegian_100_CI_AS | Nyere regler for norsk. Godt valg for nye installasjoner. Finnes også som Norwegian_100_CI_AS_SC_UTF8. |
| Danish_Greenlandic_100_CI_AS | Etterfølgeren for dansk. Sorterer æøå likt som norsk. |
| Latin1_General_100_CI_AS | Nøytral vesteuropeisk sortering. Lagrer æøå riktig, men sorterer dem som varianter av a og o. |
| SQL_Latin1_General_CP1_CI_AS | Amerikansk 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 medSQL_Latin1_General_CP1_CI_AS. - Midlertidige tabeller (
#temp) opprettes itempdbog får serverens sortering. - En
JOINmellom 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
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» trengervarchar(6). - For tekst med mange ikke-latinske tegn er
nvarcharofte 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.