Ogar's Blog

Collationi (upoređivanja) određuju način na koji će se podaci čuvati u SQL Serverovim bazama podataka i određuju pravila na osnovu kojih će karakteri (slova, brojevi i specijalni simboli) biti sortirani i poređeni prilikom njihovog korišćenja.

Predstavljanje karaktera kodnim stranama

Svaki karakter je predstavljen jedinstvenim rasporedom “nula i jedinica” u obliku 8-cifrenih ili 16-cifrenih binarnih brojeva (setovani – ON ili nesetovani – OFF bitovi), u jednom ili dva bajta (bajt ima 8 bitova – binarnih cifara). Takav pojedinačni raspored zastavica (flags – zajedničko ime i za nulu i za jedinicu) se često naziva kombinacijom bitova (iako je u pitanju varijacija dve cifre na 8 ili 16 mesta, jer mesta imaju svoje različite težine, te matematički gledano pojam kombinacije nije ispravan) ili jednostavno kombinacijom.

Takođe, svaka kombinacija se može čitati kao binarni, oktalni ili heksadekadni broj, ili nama omiljeni dekadni broj. Tako je reprezentacija slova A (“veliko a”) jednaka dekadnoj vrednosti 65.

Jednobajtne kodne stranice imaju 256 (2⁸) različitih kombinacija (od čega treba oduzeti prve 32 kombinacije jer se ne koriste za karaktere) kojima su jedinstveno predstavljena velika i mala slova, cifre i specijalni simboli. Iako su kombinacije bitova kojima se predstavljaju sva latinična engleska slova jednake u svim kodnim stranicama, postoje slova i simboli koji su morali biti različito interpretirani i/ili dodani u različitim kodnim stranicama (između ostalog sva naša ćirilična slova, kao i sva akcentovana slova – č ć ž).

Pored toga, sa obzirom da postoji mnogo jezika na svetu koji imaju više od 256 slova, za njih je moralo da se obezbede dvobajtne kodne stranice koje mogu da predstave 65,536 (216) karaktera.

SQL Server podržava rad sa sledećim kodnim stranama (zvezdicom su obeležene dvobajtne kodne strane:

Više collationa može koristiti istu kodnu stranu za non-Unicode podatke (npr. kodna strana 1251 definiše skup ćirilčnih karaktera), ali pravila za sortiranje i poređenje se razlikuju kroz različite kulture i jezike i potrebna im je definicija rečnika da bi rezultati bili korektni.

Pohranjivanje podataka iz više jezika u jednu bazu može predstavljati problem, ukoliko koristite samo jednobajtne kodne strane i njima definisane skupove podataka. Pre svega, zato što je ponekad izuzetno teško (nemoguće) naći jednu kodnu stranu kojom će biti definisani svi potrebni karakteri. I na sve to još se mogu pojaviti problemi u prevođenju karaktera ukoliko klijentska aplikacija mora koristiti različite kodne strane.

Zbog toga, baze podataka koje podržavaju rad sa višejezičnim podacima treba da koriste Unicode tipove podataka nchar, nvarchar i nvarchar(max), umesto njihovih non-Unicode ekvivalenata char, varchar i text.

Predstavljanje karaktera Unicodeom

Unicode je standard za preslikavanje dvobajtnog koda u karaktere. Obzirom da je projektovan tako da sadrži (podržava) sve karaktere, ne postoji potreba za rad sa kodnim stranama. SQL Server podržava Unicode Standard, Version 3.2. Ukoliko klijentske aplikacije podržavaju rad sa Unicode podacima i promenljivama, u tom rešenju nikada neće doći do prevođenja karaktera, što će popraviti performanse sistema i omogućiti jednoznačnu reprezentaciju svih podataka kroz ceo sistem.

Međutim, veliki broj klijenata ili ne podržava Unicode podatke ili ih ne šalje kao podrazumevani oblik za SQL Server (Unicode UCS-2). Što znači da će doći do prevođenja podataka u Unicode podatke. Za detaljnije informacije pogledajte BOL: Working with Unicode Data.

Da biste umanjili gubitak resursa, zbog prevođenja karaktera, možete interakciju sa klijentom učiniti svesnom o Unicodeu tako što ćete:

• promeniti sve non-Unicode tipove podataka u kolonama, i funkcijama CONVERT() i CAST(), u Unicode tipove podataka,
• umesto funkcija ASCII() i CHAR(), koristiti UNICODE() i NCHAR(),
• definisati promenljive i parametre u smeštenim procedurama i trigerima Unicode tipovima, i
• ispred svake Unicode string konstante dodati veliko slovo N, da se string ne bi konvertovao u podrazumevanu kodnu stranu baze podataka.

Gubitak performansi možete izazvati na još jedan način. U našem regionu vrlo je čest slučaj da se SQL Serveri instaliraju sa SQL collationom umesto sa Windows collationom, pre svega greškom, a ne realnom potrebom. Konkretnije, ukoliko su vaši podaci pohranjeni u bazama koje nisu starije od verzije 7.0, onda ne postoji nikakva prednost (naprotiv) ukoliko koristite SQL collation. On je imao smisla za rad sa našim podacima samo pre verzije 7.0. Ukoliko su baze podataka nasleđene sa SQL collationom vreme je da ih “pomerite” u SQL collation, bez obzira na količinu posla koju će takvo pomeranje izazvati.

Pored gore opisanih razloga postoji još jedan koji se na prvi pogled ne vidi. Ne bi trebalo da bude previše zbunjujuće kada kažem da sortiranje po dictionary (po rečniku) ne može biti brže (zapravo, za potrebe korišćenja naših podataka to je mnogo sporije) nego binarno (po vrednosti koda koji predstavlja karakter)  i da je sortiranje po Unicodeu mnogo sporije nego po non-Unicodeu. jer SQL Server primenjuje veoma kompleksna pravila Unicode sortiranja na Unicode tipove podataka, Ali tu ne možemo ništa, ukoliko nam trebaju "naša" slova i "naše" sortiranje.

Problem leži u tome što SQL Server primenjuje ista ta pravila i na non-Unicode tipove podataka ukoliko su oni sortirani po SQL collationu, i umesto bržeg sortiranja kako bi se dalo očekivati, naročito ukoliko ste svesno izabrali non-Unicode tip podatka da ubrzate sortiranje, imate najgoru moguću opciju.

Jedino kada ne možete izgubiti na performansama pri sortiranju non-Unicode podataka je kada koristite Windows collation!

Da ne “pobrkam lončiće”. Zahtev za ozbiljnim čuvanjem prostora na storageu (spremištu) zbog hardverskih ograničenja će proizvesti nužnost za izbegavanje Unicode tipova podataka, odnosno potrebu da se većina stringova definišu kao non-Unicode stringovi. No, istovremeno, ozbiljna poslovna potreba u kojoj je razmena podataka nužnost (juče, danas, sutra), kao i saradnja sa različitim klijentima, gotovo sigurno pretvara sve stringove u neophodne Unicode stringove. Ovakav “trade-off” (nešto za nešto) ni ne bi bio trade-off kada bi mogla postojati preporučena praksa. To mora biti rešavano od slučaja do slučaja, od strane odgovornih za ispravno i poslovno funkcionisanje SQL Servera.

Ali sa druge strane to nikako ne utiče na “apsolutnu” preporuku o korišćenju Windows collationa.

Serbian collation

Dolaskom SQL Servera 2008, konačno će nam biti na raspolaganju (Windows) Serbian collation, ili preciznije dva: latinični i ćirilični. Kada rade sa klijentima koji prosleđuju očekivani Unicode (UCS-2) podatak, rade (bar na prvi pogled) istovetno i savršeno. Nije, nažalost, sve savršeno u ovom trenutku (na sceni je SQL Server 2008 RC0). SQL Server 2008 RC0 podignut na Windows Serveru 2008 RTM, sa Serbian_Latin_100_CI_AS SQL collationom ima moguće sledeće greške:

komand prompt alatke (sqlcmd, osql, ...)  se ne mogu koristiti,
vežbovne baze AdventureWorksDw i AdventureWorksLt ne prihvataju da se instaliraju na RC0
(najverovatnije treba namestiti da putanja C:\Program Files\Microsoft SQL Server\100\Tools\Binn bude pre svih sličnih u environment varijabli PATH),

Office 2007 (Excel) nije u stanju da napravi konekciju na SQL Server
(najverovatnije vam treba hotfixovana biblioteka sqlncli.dll verzijom mlađom od 2005.90.3235.0 - ne postoji regularan download).

Očekujem da su sve to “male boginje” i da će SQL Server 2008 RTM imati bezgrešan odnos sa Serbian collationom.

U nastavku sledi kod kojim možete testirati Serbian collation (naravno obavezan je RC0).

001 Create AwDemo.sql

USE master
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'AwDemo')
DROP DATABASE AwDemo
GO

CREATE DATABASE AwDemo
ON
(NAME = AwDemo_Data, FILENAME = 'C:\Databases\AwDemo_Data.mdf')
LOG ON
(NAME = AwDemo_Log, FILENAME = 'C:\Databases\AwDemo_Log.ldf')
COLLATE Latin1_General_100_CI_AS
GO

SELECT name, collation_name
FROM sys.databases
WHERE name = 'AwDemo'
GO

002 Create and Load TestTable.sql

USE AwDemo
GO

SELECT name, Description
FROM fn_helpcollations()
--WHERE name LIKE 'Serbian%'
--OR name LIKE 'Croatian%'
--OR name LIKE 'Bosnian%'
--OR name LIKE 'Slovenian%'
--OR name LIKE 'Macedonian%'
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.TestTable') AND type in (N'U'))
DROP TABLE dbo.TestTable
GO

CREATE TABLE dbo.TestTable
(
TestID int IDENTITY(1,1) PRIMARY KEY NOT NULL
, GeneralLatin nvarchar(50) NOT NULL
, CP1250 nvarchar(50) COLLATE SQL_Latin1_General_CP1250_CI_AS NOT NULL
, CP1251 nvarchar(50) COLLATE SQL_Latin1_General_CP1251_CI_AS NOT NULL
, SerbianCyrillic nvarchar(50) COLLATE Serbian_Cyrillic_100_CI_AS NOT NULL
, SerbianLatin nvarchar(50) COLLATE Serbian_Latin_100_CI_AS NOT NULL
, BosnianCyrillic nvarchar(50) COLLATE Bosnian_Cyrillic_100_CI_AS NOT NULL
, BosnianLatin nvarchar(50) COLLATE Bosnian_Latin_100_CI_AS NOT NULL
, Croatian100 nvarchar(50) COLLATE Croatian_100_CI_AS NOT NULL
, Croatian nvarchar(50) COLLATE Croatian_CI_AS NOT NULL
, Slovenian100 nvarchar(50) COLLATE Slovenian_100_CI_AS NOT NULL
, Slovenian nvarchar(50) COLLATE Slovenian_CI_AS NOT NULL
, Macedonian100 nvarchar(50) COLLATE Macedonian_FYROM_100_CI_AS NOT NULL
, Macedonian nvarchar(50) COLLATE Macedonian_FYROM_90_CI_AS NOT NULL
)
GO

INSERT INTO dbo.TestTable
( GeneralLatin, CP1250, CP1251, SerbianCyrillic, SerbianLatin, BosnianCyrillic, BosnianLatin
, Croatian100, Croatian, Slovenian100 , Slovenian, Macedonian100 , Macedonian)
VALUES
(N'aaL', N'aaL', N'aaL', N'aaL', N'aaL', N'aaL', N'aaL', N'aaL', N'aaL', N'aaL', N'aaL', N'aaL', N'aaL')
, (N'bbL', N'bbL', N'bbL', N'bbL', N'bbL', N'bbL', N'bbL', N'bbL', N'bbL', N'bbL', N'bbL', N'bbL', N'bbL')
, (N'ccL', N'ccL', N'ccL', N'ccL', N'ccL', N'ccL', N'ccL', N'ccL', N'ccL', N'ccL', N'ccL', N'ccL', N'ccL')
, (N'ččL', N'ččL', N'ččL', N'ččL', N'ččL', N'ččL', N'ččL', N'ččL', N'ččL', N'ččL', N'ččL', N'ččL', N'ččL')
, (N'ććL', N'ććL', N'ććL', N'ććL', N'ććL', N'ććL', N'ććL', N'ććL', N'ććL', N'ććL', N'ććL', N'ććL', N'ććL')
, (N'dzbL', N'dzbL', N'dzbL', N'dzbL', N'dzbL', N'dzbL', N'dzbL', N'dzbL', N'dzbL', N'dzbL', N'dzbL', N'dzbL', N'dzbL')
, (N'džaL', N'džaL', N'džaL', N'džaL', N'džaL', N'džaL', N'džaL', N'džaL', N'džaL', N'džaL', N'džaL', N'džaL', N'džaL')
, (N'đeL', N'đeL', N'đeL', N'đeL', N'đeL', N'đeL', N'đeL', N'đeL', N'đeL', N'đeL', N'đeL', N'đeL', N'đeL')
, (N'leaL', N'leaL', N'leaL', N'leaL', N'leaL', N'leaL', N'leaL', N'leaL', N'leaL', N'leaL', N'leaL', N'leaL', N'leaL')
, (N'ljaL', N'ljaL', N'ljaL', N'ljaL', N'ljaL', N'ljaL', N'ljaL', N'ljaL', N'ljaL', N'ljaL', N'ljaL', N'ljaL', N'ljaL')
, (N'neaL', N'neaL', N'neaL', N'neaL', N'neaL', N'neaL', N'neaL', N'neaL', N'neaL', N'neaL', N'neaL', N'neaL', N'neaL')
, (N'njaL', N'njaL', N'njaL', N'njaL', N'njaL', N'njaL', N'njaL', N'njaL', N'njaL', N'njaL', N'njaL', N'njaL', N'njaL')
, (N'seL', N'seL', N'seL', N'seL', N'seL', N'seL', N'seL', N'seL', N'seL', N'seL', N'seL', N'seL', N'seL')
, (N'šaL', N'šaL', N'šaL', N'šaL', N'šaL', N'šaL', N'šaL', N'šaL', N'šaL', N'šaL', N'šaL', N'šaL', N'šaL')
, (N'zeL', N'zeL', N'zeL', N'zeL', N'zeL', N'zeL', N'zeL', N'zeL', N'zeL', N'zeL', N'zeL', N'zeL', N'zeL')
, (N'žaL', N'žaL', N'žaL', N'žaL', N'žaL', N'žaL', N'žaL', N'žaL', N'žaL', N'žaL', N'žaL', N'žaL', N'žaL')
, (N'ааЦ', N'ааЦ', N'ааЦ', N'ааЦ', N'ааЦ', N'ааЦ', N'ааЦ', N'ааЦ', N'ааЦ', N'ааЦ', N'ааЦ', N'ааЦ', N'ааЦ')
, (N'ббЦ', N'ббЦ', N'ббЦ', N'ббЦ', N'ббЦ', N'ббЦ', N'ббЦ', N'ббЦ', N'ббЦ', N'ббЦ', N'ббЦ', N'ббЦ', N'ббЦ')
, (N'ввЦ', N'ввЦ', N'ввЦ', N'ввЦ', N'ввЦ', N'ввЦ', N'ввЦ', N'ввЦ', N'ввЦ', N'ввЦ', N'ввЦ', N'ввЦ', N'ввЦ')
, (N'ггЦ', N'ггЦ', N'ггЦ', N'ггЦ', N'ггЦ', N'ггЦ', N'ггЦ', N'ггЦ', N'ггЦ', N'ггЦ', N'ггЦ', N'ггЦ', N'ггЦ')
, (N'ддЦ', N'ддЦ', N'ддЦ', N'ддЦ', N'ддЦ', N'ддЦ', N'ддЦ', N'ддЦ', N'ддЦ', N'ддЦ', N'ддЦ', N'ддЦ', N'ддЦ')
, (N'ђђЦ', N'ђђЦ', N'ђђЦ', N'ђђЦ', N'ђђЦ', N'ђђЦ', N'ђђЦ', N'ђђЦ', N'ђђЦ', N'ђђЦ', N'ђђЦ', N'ђђЦ', N'ђђЦ')
, (N'ееЦ', N'ееЦ', N'ееЦ', N'ееЦ', N'ееЦ', N'ееЦ', N'ееЦ', N'ееЦ', N'ееЦ', N'ееЦ', N'ееЦ', N'ееЦ', N'ееЦ')
, (N'жжЦ', N'жжЦ', N'жжЦ', N'жжЦ', N'жжЦ', N'жжЦ', N'жжЦ', N'жжЦ', N'жжЦ', N'жжЦ', N'жжЦ', N'жжЦ', N'жжЦ')
, (N'ззЦ', N'ззЦ', N'ззЦ', N'ззЦ', N'ззЦ', N'ззЦ', N'ззЦ', N'ззЦ', N'ззЦ', N'ззЦ', N'ззЦ', N'ззЦ', N'ззЦ')
, (N'ииЦ', N'ииЦ', N'ииЦ', N'ииЦ', N'ииЦ', N'ииЦ', N'ииЦ', N'ииЦ', N'ииЦ', N'ииЦ', N'ииЦ', N'ииЦ', N'ииЦ')
, (N'јјЦ', N'јјЦ', N'јјЦ', N'јјЦ', N'јјЦ', N'јјЦ', N'јјЦ', N'јјЦ', N'јјЦ', N'јјЦ', N'јјЦ', N'јјЦ', N'јјЦ')
, (N'ккЦ', N'ккЦ', N'ккЦ', N'ккЦ', N'ккЦ', N'ккЦ', N'ккЦ', N'ккЦ', N'ккЦ', N'ккЦ', N'ккЦ', N'ккЦ', N'ккЦ')
, (N'ллЦ', N'ллЦ', N'ллЦ', N'ллЦ', N'ллЦ', N'ллЦ', N'ллЦ', N'ллЦ', N'ллЦ', N'ллЦ', N'ллЦ', N'ллЦ', N'ллЦ')
, (N'љљЦ', N'љљЦ', N'љљЦ', N'љљЦ', N'љљЦ', N'љљЦ', N'љљЦ', N'љљЦ', N'љљЦ', N'љљЦ', N'љљЦ', N'љљЦ', N'љљЦ')
, (N'ммЦ', N'ммЦ', N'ммЦ', N'ммЦ', N'ммЦ', N'ммЦ', N'ммЦ', N'ммЦ', N'ммЦ', N'ммЦ', N'ммЦ', N'ммЦ', N'ммЦ')
, (N'ннЦ', N'ннЦ', N'ннЦ', N'ннЦ', N'ннЦ', N'ннЦ', N'ннЦ', N'ннЦ', N'ннЦ', N'ннЦ', N'ннЦ', N'ннЦ', N'ннЦ')
, (N'њњЦ', N'њњЦ', N'њњЦ', N'њњЦ', N'њњЦ', N'њњЦ', N'њњЦ', N'њњЦ', N'њњЦ', N'њњЦ', N'њњЦ', N'њњЦ', N'њњЦ')
, (N'ооЦ', N'ооЦ', N'ооЦ', N'ооЦ', N'ооЦ', N'ооЦ', N'ооЦ', N'ооЦ', N'ооЦ', N'ооЦ', N'ооЦ', N'ооЦ', N'ооЦ')
, (N'ппЦ', N'ппЦ', N'ппЦ', N'ппЦ', N'ппЦ', N'ппЦ', N'ппЦ', N'ппЦ', N'ппЦ', N'ппЦ', N'ппЦ', N'ппЦ', N'ппЦ')
, (N'ррЦ', N'ррЦ', N'ррЦ', N'ррЦ', N'ррЦ', N'ррЦ', N'ррЦ', N'ррЦ', N'ррЦ', N'ррЦ', N'ррЦ', N'ррЦ', N'ррЦ')
, (N'ссЦ', N'ссЦ', N'ссЦ', N'ссЦ', N'ссЦ', N'ссЦ', N'ссЦ', N'ссЦ', N'ссЦ', N'ссЦ', N'ссЦ', N'ссЦ', N'ссЦ')
, (N'ттЦ', N'ттЦ', N'ттЦ', N'ттЦ', N'ттЦ', N'ттЦ', N'ттЦ', N'ттЦ', N'ттЦ', N'ттЦ', N'ттЦ', N'ттЦ', N'ттЦ')
, (N'ћћЦ', N'ћћЦ', N'ћћЦ', N'ћћЦ', N'ћћЦ', N'ћћЦ', N'ћћЦ', N'ћћЦ', N'ћћЦ', N'ћћЦ', N'ћћЦ', N'ћћЦ', N'ћћЦ')
, (N'ууЦ', N'ууЦ', N'ууЦ', N'ууЦ', N'ууЦ', N'ууЦ', N'ууЦ', N'ууЦ', N'ууЦ', N'ууЦ', N'ууЦ', N'ууЦ', N'ууЦ')
, (N'ффЦ', N'ффЦ', N'ффЦ', N'ффЦ', N'ффЦ', N'ффЦ', N'ффЦ', N'ффЦ', N'ффЦ', N'ффЦ', N'ффЦ', N'ффЦ', N'ффЦ')
, (N'ххЦ', N'ххЦ', N'ххЦ', N'ххЦ', N'ххЦ', N'ххЦ', N'ххЦ', N'ххЦ', N'ххЦ', N'ххЦ', N'ххЦ', N'ххЦ', N'ххЦ')
, (N'ццЦ', N'ццЦ', N'ццЦ', N'ццЦ', N'ццЦ', N'ццЦ', N'ццЦ', N'ццЦ', N'ццЦ', N'ццЦ', N'ццЦ', N'ццЦ', N'ццЦ')
, (N'ччЦ', N'ччЦ', N'ччЦ', N'ччЦ', N'ччЦ', N'ччЦ', N'ччЦ', N'ччЦ', N'ччЦ', N'ччЦ', N'ччЦ', N'ччЦ', N'ччЦ')
, (N'џџЦ', N'џџЦ', N'џџЦ', N'џџЦ', N'џџЦ', N'џџЦ', N'џџЦ', N'џџЦ', N'џџЦ', N'џџЦ', N'џџЦ', N'џџЦ', N'џџЦ')
, (N'шшЦ', N'шшЦ', N'шшЦ', N'шшЦ', N'шшЦ', N'шшЦ', N'шшЦ', N'шшЦ', N'шшЦ', N'шшЦ', N'шшЦ', N'шшЦ', N'шшЦ')
GO

003 Test TestTable.sql

USE AwDemo
GO

WITH OrderedByCollation(Rn, TestID) AS
(
--SELECT ROW_NUMBER() OVER(ORDER BY GeneralLatin
--SELECT ROW_NUMBER() OVER(ORDER BY CP1250
--SELECT ROW_NUMBER() OVER(ORDER BY CP1251
SELECT ROW_NUMBER() OVER(ORDER BY SerbianLatin
--SELECT ROW_NUMBER() OVER(ORDER BY SerbianCyrillic
--SELECT ROW_NUMBER() OVER(ORDER BY BosnianLatin
--SELECT ROW_NUMBER() OVER(ORDER BY BosnianCyrillic
--SELECT ROW_NUMBER() OVER(ORDER BY Croatian100
--SELECT ROW_NUMBER() OVER(ORDER BY Croatian
--SELECT ROW_NUMBER() OVER(ORDER BY Slovenian100
--SELECT ROW_NUMBER() OVER(ORDER BY Slovenian
--SELECT ROW_NUMBER() OVER(ORDER BY Macedonian100
--SELECT ROW_NUMBER() OVER(ORDER BY Macedonian
) AS Rn, TestID
ROM dbo.TestTable
)

SELECT Rn, t.TestID, GeneralLatin
FROM dbo.TestTable AS t INNER JOIN OrderedByCollation AS o
ON t.TestID = o.TestID
WHERE Rn <> t.TestID
GO

Serbian collation – zaista je sjajan.