Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Globalization in SQL Server

By Dinesh Asanka,

Introduction

Globalization or Internationalization has become a buzzword in many places such as political circles, business forums, and elsewhere. Because of this mega trend, software also needs to cater to globalization. What are the aspects you need to cover in globalization? If you are a vendor who is supporting software packages for countries like China, Korea, Japan in Asia, or Germany, Spain, France, etc. in Europe, you will have to give support for your software in the native language. These languages are stored in Unicode characters.

Different time zones are another issue if you are dealing with multiple countries in a single project. This article does not address to that issue as it has already discussed in the article http://www.sqlservercentral.com/columnists/dasanka/datetimevaluesandtimezones.asp. This article describes only how to cater to different languages.

Multi Language

The Unicode specification defines a single encoding scheme for most characters widely used in businesses around the world. All computers consistently translate the bit patterns in Unicode data into characters using the single Unicode specification. This ensures that the same bit pattern is always converted to the same character on all computers. Data can be freely transferred from one database or computer to another without concern that the receiving system will translate the bit patterns into characters incorrectly.

One problem with data types that use 1 byte to encode each character is that the data type can only represent 256 different characters. This forces multiple encoding specifications (or code pages) for different alphabets such as European alphabets, which are relatively small. It is also impossible to handle systems such as the Japanese Kanji or Korean Hangul alphabets that have thousands of characters.

Each Microsoft SQL Server collation has a code page that defines what patterns of bits represent each character in char, varchar, and text values. Individual columns and character constants can be assigned a different code page. Client computers use the code page associated with the operating system locale to interpret character bit patterns.

There are many different code pages. Some characters appear on some code pages, but not on others. Some characters are defined with one bit pattern on some code pages, and with a different bit pattern on other code pages. When you build international systems that must handle different languages, it becomes difficult to pick code pages for all the computers that meet the language requirements of multiple countries/regions. It is also difficult to ensure that every computer performs the correct translations when interfacing with a system using a different code page. A few of the code pages and their values are shown below.

Code page Description
1258 Vietnamese
1257 Baltic
1256 Arabic
1255 Hebrew
1254 Turkish
1253 Greek
1252 Latin1 (ANSI)
1251 Cyrillic
1250 Central European
950 Chinese (Traditional)
949 Korean
936 Chinese (Simplified)
932 Japanese
874 Thai
850 Multilingual (MS-DOS Latin1)
437 MS-DOS U.S. English


The Unicode specification addresses this problem by using 2 bytes to encode each character. There are enough different patterns (65,536) in 2 bytes for a single specification covering the most common business languages. Because all Unicode systems consistently use the same bit patterns to represent all characters, there is no problem with characters being converted incorrectly when moving from one system to another. You can minimize character conversion issues by using Unicode data types throughout your system.

In Microsoft SQL Server the nchar, nvarchar, and ntext data types support Unicode data. Those are the replacement for the char, varchar and text date types.

Note that the 'n' prefix for these data types come from the SQL-92 standard for National (Unicode) data types.

However, you should use Unicode characters only if it is going to store the Unicode characters. If not there will be unnecessary performance losses. Another point that you should remember is , the maximum size of nchar and nvarchar columns is 4,000 characters, not 8,000 characters like in char and varchar.

SET LANGUAGE

According to BOL, the Set Language Specifies the language environment for the session. The session language determines the date time formats and system messages. If users need to change the language to us_english following T-SQL has to be executes;

SET LANGUAGE us_english

The following table contains the language names and its other parameters.

 
Name Alias Date Format Date First Months Short Months Days
us_english English mdy 7 January, February, March, April, May, June, July, August, September, October, November, December Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
Deutsch German dmy 1 Januar, Februar, März, April, Mai, Juni, Juli, August, September, Oktober, November, Dezember Jan, Feb, Mär, Apr, Mai, Jun, Jul, Aug, Sep, Okt, Nov, Dez Montag, Dienstag, Mittwoch, Donnerstag, Freitag, Samstag, Sonntag
Français French dmy 1 janvier, février, mars, avril, mai, juin, juillet, août, septembre, octobre, novembre, décembre janv, févr, mars, avr, mai, juin, juil, août, sept, oct, nov, déc lundi, mardi, mercredi, jeudi, vendredi, samedi, dimanche
日 本 語 Japanese ymd 7 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12 月 曜 , 火 曜 , 水 曜 , 木 曜 , 金 曜 , 土 曜 , 日 曜
Dansk Danish dmy 1 januar, februar, marts, april, maj, juni, juli, august, september, oktober, november, december jan, feb, mar, apr, maj, jun, jul, aug, sep, okt, nov, dec mandag, tirsdag, onsdag, torsdag, fredag, lordag, sondag
Español Spanish dmy 1 Enero, Febrero, Marzo, Abril, Mayo, Junio, Julio, Agosto, Septiembre, Octubre, Noviembre, Diciembre Ene, Feb, Mar, Abr, May, Jun, Jul, Ago, Sep, Oct, Nov, Dic Lunes, Martes, Miércoles, Jueves, Viernes, Sábado, Domingo
Italiano Italian dmy 1 gennaio, febbraio, marzo, aprile, maggio, giugno, luglio, agosto, settembre, ottobre, novembre, dicembre gen, feb, mar, apr, mag, giu, lug, ago, set, ott, nov, dic lunedì, martedì, mercoledì, giovedì, venerdì, sabato, domenica
Nederland Dutch dmy 1 januari, februari, maart, april, mei, juni, juli, augustus, september, oktober, november, december jan, feb, mrt, apr, mei, jun, jul, aug, sep, okt, nov, dec maandag, dinsdag, woensdag, donderdag, vrijdag, zaterdag, zondag
Norsk Norwegian dmy 1 januar, februar, mars, april, mai, juni, juli, august, september, oktober, november, desember jan, feb, mar, apr, mai, jun, jul, aug, sep, okt, nov, des mandag, tirsdag, onsdag, torsdag, fredag, laurdag, sundag
Português Portuguese (Brasil) dmy 7 janeiro, fevereiro, março, abril, maio, junho, julho, agosto, setembro, outubro, novembro, dezembro jan, fev, mar, abr, mai, jun, jul, ago, set, out, nov, dez segunda-feira, terça-feira, quarta-feira, quinta-feira, sexta-feira, sábado, domingo
Suomi Finnish dmy 1 tammikuuta, helmikuuta, maaliskuuta, huhtikuuta, toukokuuta, kesäkuuta, heinäkuuta, elokuuta, syyskuuta, lokakuuta, marraskuuta, joulukuuta tammi, helmi, maalis, huhti, touko, kesä, heinä, elo, syys, loka, marras, joulu maanantai, tiistai, keskiviikko, torstai, perjantai, lauantai, sunnuntai
Svenska Swedish ymd 1 januari, februari, mars, april, maj, juni, juli, augusti, september, oktober, november, december jan, feb, mar, apr, maj, jun, jul, aug, sep, okt, nov, dec måndag, tisdag, onsdag, torsdag, fredag, lördag, söndag
č eština Czech dmy 1 leden, únor, bř ezen, duben, kvě ten, č erven, č ervenec, srpen, zář í, ř íjen, listopad, prosinec I, II, III, IV, V, VI, VII, VIII, IX, X, XI, XII pondě lí, úterý, stř eda, č tvrtek, pátek, sobota, nedě le
magyar Hungarian ymd 1 január, február, március, április, május, június, július, augusztus, szeptember, október, november, december jan, febr, márc, ápr, máj, jún, júl, aug, szept, okt, nov, dec hétfő , kedd, szerda, csütörtök, péntek, szombat, vasárnap
polski Polish dmy 1 styczeń , luty, marzec, kwiecień , maj, czerwiec, lipiec, sierpień , wrzesień , paź dziernik, listopad, grudzień I, II, III, IV, V, VI, VII, VIII, IX, X, XI, XII poniedział ek, wtorek, ś roda, czwartek, pią tek, sobota, niedziela
română Romanian dmy 1 ianuarie, februarie, martie, aprilie, mai, iunie, iulie, august, septembrie, octombrie, noiembrie, decembrie Ian, Feb, Mar, Apr, Mai, Iun, Iul, Aug, Sep, Oct, Nov, Dec luni, marţ i, miercuri, joi, vineri, sîmbă tă , duminică
hrvatski Croatian ymd 1 siječ anj, veljač a, ožujak, travanj, svibanj, lipanj, srpanj, kolovoz, rujan, listopad, studeni, prosinac sij, vel, ožu, tra, svi, lip, srp, kol, ruj, lis, stu, pro ponedjeljak, utorak, srijeda, č etvrtak, petak, subota, nedjelja
slovenč ina Slovak dmy 1 január, február, marec, apríl, máj, jún, júl, august, september, október, november, december I, II, III, IV, V, VI, VII, VIII, IX, X, XI, XII pondelok, utorok, streda, štvrtok, piatok, sobota, nedeľ a
slovenski Slovenian dmy 1 januar, februar, marec, april, maj, junij, julij, avgust, september, oktober, november, december jan, feb, mar, apr, maj, jun, jul, avg, sept, okt, nov, dec ponedeljek, torek, sreda, č etrtek, petek, sobota, nedelja
ε λ λ η ν ι κ ά Greek dmy 1 Ι α ν ο υ α ρ ί ο υ , Φ ε β ρ ο υ α ρ ί ο υ , Μ α ρ τ ί ο υ , Α π ρ ι λ ί ο υ , Μ α _ο υ , Ι ο υ ν ί ο υ , Ι ο υ λ ί ο υ , Α υ γ ο ύ σ τ ο υ , Σ ε π τ ε μ β ρ ί ο υ , Ο κ τ ω β ρ ί ο υ , Ν ο ε μ β ρ ί ο υ , Δ ε κ ε μ β ρ ί ο υ Ι α ν , Φ ε β , Μ α ρ , Α π ρ , Μ α ϊ , Ι ο υ ν , Ι ο υ λ , Α υ γ , Σ ε π , Ο κ τ , Ν ο ε , Δ ε κ Δ ε υ τ έ ρ α , Τ ρ ί τ η , Τ ε τ ά ρ τ η , Π έ μ π τ η , Π α ρ α σ κ ε υ ή , Σ ά β β α τ ο , Κ υ ρ ι α κ ή
б ъ л г а р с к и Bulgarian dmy 1 я н у а р и , ф е в р у а р и , м а р т , а п в и л , м а й , ю н и , ю л и , а в г у с т , с е п т е м в р и , о к т о м в р и , н о е м в р и , д е к е м в р и я н у а р и , ф е в р у а р и , м а р т , а п р и л , м а й , ю н и , ю л и , а в г у с т , с е п т е м в р и , о к т о м в р и , н о е м в р и , д е к е м в р и п о н е д е л н и к , в т о р н и к , с р я д а , ч е т в ъ в т ъ к , п е т ъ к , с ъ б о т а , н е д е л я
р у с с к и й Russian dmy 1 Я н в а р ь , Ф е в р а л ь , М а р т , А п р е л ь , М а й , И ю н ь , И ю л ь , А в г у с т , С е н т я б р ь , О к т я б р ь , Н о я б р ь , Д е к а б р ь я н в , ф е в , м а р , а п р , м а й , и ю н , и ю л , а в г , с е н , о к т , н о я , д е к п о н е д е л ь н и к , в т о р н и к , с р е д а , ч е т в е р г , п я т н и ц а , с у б б о т а , в о с к р е с е н ь е
Türkçe Turkish dmy 1 Ocak, Ş ubat, Mart, Nisan, Mayı s, Haziran, Temmuz, Ağ ustos, Eylül, Ekim, Kası m, Aralı k Oca, Ş ub, Mar, Nis, May, Haz, Tem, Ağ u, Eyl, Eki, Kas, Ara Pazartesi, Salı , Çarş amba, Perş embe, Cuma, Cumartesi, Pazar
British British English dmy 1 January, February, March, April, May, June, July, August, September, October, November, December Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
eesti Estonian dmy 1 jaanuar, veebruar, märts, aprill, mai, juuni, juuli, august, september, oktoober, november, detsember jaan, veebr, märts, apr, mai, juuni, juuli, aug, sept, okt, nov, dets esmaspäev, teisipäev, kolmapäev, neljapäev, reede, laupäev, pühapäev
latviešu Latvian ymd 1 janvā ris, februā ris, marts, aprī lis, maijs, jū nijs, jū lijs, augusts, septembris, oktobris, novembris, decembris jan, feb, mar, apr, mai, jū n, jū l, aug, sep, okt, nov, dec pirmdiena, otrdiena, trešdiena, ceturtdiena, piektdiena, sestdiena, svē tdiena
lietuvių Lithuanian ymd 1 sausis, vasaris, kovas, balandis, gegužė , birželis, liepa, rugpjū tis, rugsė jis, spalis, lapkritis, gruodis sau, vas, kov, bal, geg, bir, lie, rgp, rgs, spl, lap, grd pirmadienis, antradienis, treč iadienis, ketvirtadienis, penktadienis, šeštadienis, sekmadienis
Português (Brasil) Brazilian dmy 7 Janeiro, Fevereiro, Março, Abril, Maio, Junho, Julho, Agosto, Setembro, Outubro, Novembro, Dezembro Jan, Fev, Mar, Abr, Mai, Jun, Jul, Ago, Set, Out, Nov, Dez Segunda-Feira, Terça-Feira, Quarta-Feira, Quinta-Feira, Sexta-Feira, Sábado, Domingo
繁 體 中 文 Traditional Chinese ymd 7 一 月 , 二 月 , 三 月 , 四 月 , 五 月 , 六 月 , 七 月 , 八 月 , 九 月 , 十 月 , 十 一 月 , 十 二 月 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12 星 期 一 , 星 期 二 , 星 期 三 , 星 期 四 , 星 期 五 , 星 期 六 , 星 期 日
한 국 어 Korean ymd 7 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12 월 요 일 , 화 요 일 , 수 요 일 , 목 요 일 , 금 요 일 , 토 요 일 , 일 요 일
简 体 中 文 Simplified Chinese ymd 7 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12 星 期 一 , 星 期 二 , 星 期 三 , 星 期 四 , 星 期 五 , 星 期 六 , 星 期 日
Arabic Arabic dmy 1 Muharram , Safar , Rabie I  , Rabie II , Jumada I , Jumada II , Rajab , Shaaban , Ramadan , Shawwal , Thou Alqadah , Thou Alhajja Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
ไ ท ย Thai dmy 7 ม ก ร า ค ม , ก ุ ม ภ า พ ั น ธ ์ , ม ี น า ค ม , เ ม ษ า ย น , พ ฤ ษ ภ า ค ม , ม ิ ถ ุ น า ย น , ก ร ก ฎ า ค ม , ส ิ ง ห า ค ม , ก ั น ย า ย น , ต ุ ล า ค ม , พ ฤ ศ จ ิ ก า ย น , ธ ั น ว า ค ม ม .ค ., ก .พ ., ม ี .ค ., เ ม .ย ., พ .ค ., ม ิ .ย ., ก .ค ., ส .ค ., ก .ย ., ต .ค ., พ .ย ., ธ .ค . จ ั น ท ร ์ , อ ั ง ค า ร , พ ุ ธ , พ ฤ ห ั ส บ ด ี , ศ ุ ก ร ์ , เ ส า ร ์ , อ า ท ิ ต ย ์

Conclusion

Microsoft SQL Server 2000 has support for multiple languages in different ways. Users can select the best option that fits their need or that will fit to their environments.

Total article views: 12897 | Views in the last 30 days: 8
 
Related Articles
FORUM

Unable to translate Unicode character

Unable to translate Unicode character

FORUM

Differences between Unicode Text and ASCII text file

Differences between Unicode Text and ASCII text file

FORUM

How to Store and Retrive the Non English (some other language) Characters in Database.

Storing NON - English (some other language) Characters in DB

FORUM

Foreign Language Support.

Foreign Language Support.

FORUM

db is unicode?

db is unicode?

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones