Sorting vs collation

Alaphelyzet a következő: van egy adatbázisunk, szép nagy táblákkal, Latin1_General_CI_AS collationnel, és egy napon a fejlesztő, aki megörökölte a rendszert, rájön, hogy az tulajdonképpen baj, hogy az SQL a számok elé sorolja az aláhúzást, azaz az AA01 előtt van az AA_ss01.
Megkérdezett az ember tehát, hogy át lehet-e alakítani ezt mezőt, mert ő a unicode rendezési elvét szokta meg, és abban gondolkodik. Mondtam, hogy meg lehet változtatni a mezőt nvarcharra, ami hirtelen megeszik egy csomó helyet, mert az UCS2-LE (amit az MS használ) mindig két byte. Meg lehet változtatni annak az egy oszlopnak a collationjét valami másra, ami esetleg úgy rendez, ahogy szeretné, de akkor egész érdekes módokon fog kiszúrni saját magával, ha véletlen pl. tempdb-t akar használni.
Úgyhogy gondolkoztam: A unicode jó neki – a unicode binárisan rendez, tehát egy bináris collation jó lehet. lehet akár a lekérdezésben is konvertálni – ezzel egy baj van: kicsit költséges művelet minden mezőt konvertálni… Na álljunk csak meg… miért kell konvertálni? A collation a tárolást, a rendezést és az összehasonlítást határozza meg. Tehát elég a rendezés-összehasonlítás részt módosítani, a tárolás nem változik, és így lehet, hogy jól is járunk. Akkor a Latin1_General részt leszögezzük, és BIN kell a végére – ilyen collation pont egy van. Alább a proof-of-concept scriptem látható, a Latin1_General_CI_AS a default collation (a b oszlop csak önellenőrzésnek kellett a kitakart bénázáshoz).

use tempdb
create table a(a varchar(30), b varchar(30) COLLATE Latin1_General_BIN)

insert into a values('AA01', 'AA01')
insert into a values('AA02', 'AA02')
insert into a values('AA03', 'AA03')
insert into a values('AA_ss01', 'AA_ss01')

select * from a order by a 

Az eredmény:

a b
AA_ss01 AA_ss01
AA01 AA01
AA02 AA02
AA03 AA03

És aztán…

select * from a order by a collate Latin1_General_BIN

Tádá!:

a b
AA01 AA01
AA02 AA02
AA03 AA03
AA_ss01 AA_ss01

A collate clause költsége egy darab compute scalar operátor, 0%-ért. Ma is boldoggá tettem egy embert. Meg magamat, mert én üzemeltetem az adatbázist :)

4 Comments

  1. zsorzs:

    Szia!
    Tetszik a blogod.
    A Sorting vs collation témához lenne egy kapcsolódó témám (és kérném benne a szakmai véleményedet is):
    A probléma: van egy nvarchar(512) -es mező, ami partnerneveket tartalmaz egy partneradatokat tartalmazó táblában. Maga az adatbázis collation-je: SQL_Hungarian_CP1250_CS_AS vagyis, ami a lényeg: case szenzitív. És van mindehez egy alkalamzás, amelyben kezdő karakterekkel lehet rákeresni a partnernevekre.
    A fejlesztő a felhasználót segítve a keresést úgy oldotta meg, hogy a beírt szövegrészt upper-rel keresi: select .. from Partner where UPPER(Partnernev) like ‘MICRO%’ Ezzel ugyan segíti a usert a pötyögésben (hogy ne vacakoljon a kis-nagybetűkkel), de ezzel szembe is köpte a Partnernev -re készített indexet. Szegény SQL kénytelen fullscan-nel keresni. Ezzel a keresés ideje akkora, hogy nagy eséllyel timeouttal elszáll a keresés a kliens oldalon.
    Nyerő Megoldás: computed field használata: alter table Partner add UPartnernev as UPPER(Partnernev) , és erre egy sima index készítése: create index IDX_UPartnerNev on Partner( UPartnernev ).
    Miért? Mert:
    1. Mert nincs plusz tárhely költsége, a tábla machinációk nem lassultak csak annyival, hogy van egy új index.
    2. A select .. from Partner where UPPER(Partnernev) like ‘MICRO%’ esetén az Optimizer van annyira okos, hogy észreveszi, hogy nédda nekem van egy pont ilyenféle indexem, és fel is használja (az execution plan-ban tisztán látszik, hogy erre a computed field-es indexre gyúr)!
    Alternatív Megoldás: függvény szerinti indexelés: na aztat elvből nem használunk, meg amúgy is szintatktikai hibát kapok arra hogy : create index IDX_UpperPartnernev on Partner( UPPER(Partnernev) ).
    Rossz Megoldás: addig ütni a fejlesztőt, amíg kiszedi az UPPERt és a felhasználókat kioktatni: hogy Béláim az Úrban, vagy kényelem, vagy gyorsaság.
    Vélemény?
    MS SQL 2005 9.00.1406.00

  2. erik:

    Szia Zsorzs!

    Köszi, örülök, hogy van haszna a blognak :) Úgy néz ki, hogy megtaláltad a legjobb megoldást, mert az MS is ezt blogolja itt: http://blogs.msdn.com/mssqlisv/archive/2007/01/04/case-insensitive-search-operations.aspx.
    Személyes, témához nem kapcsolódó ajánlatom még, hogy tegyél fel SP3-at, abból ritkán van baj.

    Erik

  3. erik:

    Ja, közben a kollégákkal elkeztdünk eszmét cserélni a function-based indexekről, és hát tulajdonképpen amit tervezel az pont az. Szóval nem maradt ki a MSSQL-ből, mint sokan állítják. És pont így készül el Oracle-ben is, csak ott van neki külön neve, MSSQL-ben meg csak computed columnra index. Majd biztos belehúznak a marketingesek a SQL 2011-re.

  4. zsorzs:

    Szia Erik!
    Köszi a válaszokat.

Leave a comment