SQL Server Language ve Collation Nedir ?

SQL Server Language ve Collation Nedir ?

http://www.ahmetkaymaz.com/2006/12/29/sql-server-language-ve-collation-kavrami/

Bu makalede SQL Server’da language ve collation kavramlarının farklılıklarından bahseceğiz. Language kavramı, mesajlar, date/time, ay/gün isimleri, para formatı ve birimi gibi yerel bilgileri desteklemekle sınırlıdır. SQL Server, kendi içerisinde birçok dile ait yerel bilgileri taşır. Bu bilgiler, kullanıcıların oturumlarına bağlı olarak uygulamalarda farklı dil değerlerini göstermelerini sağlamaktadır.
SQL Server’in desteklediği dil veya diller hakkında ayrıntılı bilgi almak için sp_helplanguageprocedure kullanılabilir. Bu procedure, @language parametresi girilmezse tüm dillerin ayrıntısı listelenir. Language tanımlamaları, sys şemasına bağlı syslanguages tablosunda saklıdır.

1 2 3 4 5 6

--Bütün dilleri listeleyelim. EXEC sp_helplanguage --Sadece Türkçe dilinin bilgilerini görelim EXEC sp_helplanguage turkish --Doğrudan syslanguages tablosunu sorgulayalım. SELECT * FROM sys.syslanguages WHERE alias='turkish'

SQL Server’da dil ayarı sunucu ve kullanıcı(oturum) bazında etkili olur. SQL Server’in instanceni kurarken language değerini de set edebiliriz. Sunucunun default dil değerini Sever Propertiespenceresindeki Advanced sekmesindeki Default Language bölümünden görebilir ve değiştirebiliriz. Programatik olarak T-SQL ile değiştirmek için sp_configure yordamı kullanılır.Bilindiği gibi sunucuyla ilgili yapılandırma bilgileri master veritabanı altındaki sys.sysconfigurestablosunda tutulmaktadır. Bu tablodaki default language değerini okuyarak sunucunun aktif language bilgisine ulaşabiliriz. Bu tabloyu doğrudan update etmek yerine sp_configureyordamını kullanarak sunucunun dilini değiştirebiliriz. Bu yordamı çalıştırdıktan sonra sunucuyu yeniden yapılandırmak için RECONFIGURE ifadesini kullanmalıyız. Bu ifade, stop-restart gerektirmeyen değişiklikleri o anda aktifleştirir.

1 2	sp_configure 'default language', 22--turkish yani reconfigure with override--Aksi durumda SQL restart olduktan sonra aktifleşir

Bağlı olduğumuz kullanıcının, oturumun dilini öğrenmek için @@LANGUAGE isimli global değişkeni kullanılır. Kullanıcının o anki oturumunda geçici olarak dili değiştirmek için SET LANGUAGE kullanılır. SET LANGUAGE { [ N ] 'language' | @language_var } Bunu genellikle o anda tarih veya para birimini düzenlemek için kullanırız.

1 2 3 4

SELECT @@language --us_english döndürdü --Bu oturumda geçici olarak dili değiştirelim. SET LANGUAGE turkish SELECT @@language--Türkçe döndürdü

Yeri gelmişken söyleyelim oturumumuzda tarih formatını değiştirmek için SET DATEFORMATdeyimi de kullanılabilir.SET DATEFORMAT { format | @format_var } Böylece kalıcı değil sadece o oturumda tarih formatını değiştirmiş oluruz.

1 2 3 4 5

--Tarih formatını day, month, year olarak düzenleyelim. SET DATEFORMAT dmy DECLARE @Tarih DATETIME SET @Tarih = '31/12/2006' SELECT @Tarih AS Tarih

Eğer burada DATEFORMAT ile geçici olarak tarih formatını değiştirmeseydik SQL Server, default olarak MM/DD/YY formatını kullandığı için T-SQL’in klasik hatası The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. mesajıyla karşılaşırdık.
Bir kullanıcının kalıcı olarak dil ayarını değiştirmek için sp_defaultlanguage procedure kullanılır.sp_defaultlanguage [ @loginame= ] 'login'
[ , [ @language= ] 'language' ] Microsoft, SQL Server’in 2005’ten sonraki versiyonlarında bu özelliği kaldıracağını söylemektedir.

1	EXEC sp_defaultlanguage 'Ayse', 'Turkish'

Buna alternatif olarak ALTER LOGIN ifadesiyle güncellemenin yapılması tavsiye edilir. Nitekim,sp_defaultlanguage yordamı da ALTER LOGIN . . . komut yapısını çalıştırmaktadır.

1	ALTER LOGIN Ayse WITH DEFAULT_LANGUAGE = Turkish

Bu ifade, Ayse isimli login için default language değerini Türkçe olarak set eder.
Collation, işletim sisteminden bağımsız olup bir dil veya alfabenin karakter kurallarını tanımlar. Örneğin Türkçe dilinde küçük “i” ile büyük “İ”nin aynı olmaması bu dilin collation yapısıyla ilgilidir. Bununla birlikte bir dil, farklı milletler tarafından farklı lehçelerle konuşulabiliyor. Windows, dilleri ve lehçelerini birer 32 bitlik Language ID Reference Number(LCID) koduyla saklar. Bu numaralandırma aynı dilleri veya alfabeleri aynı çatıda toplamak için kullanılan bir yöntemdir.
Collationlar, karakterlerin doğru yazılıp okunması ve karşılaştırılmasından sorumludur.
SQL Server, iki tür collation yapısı sunar;

Windows collations

SQL collations
SQL Server collation, Unicode (nchar,nvarchar ve ntext) ve non-Unicode(char,varchar vetext) veri türlerinin sıralaması ve karşılaştırılmasında önemli rol oynar. SQL server’da collation tanımları 4 bölümden oluşur;

Sıralama Kuralları – Dil veya alfabe adı.

Üstünlük – Büyük küçük karakter önceliği(seçimli)

CodePage – ASCII code page(seçimli)

CaseSensitivity + AccentSensitivity veya BIN – Aksan veya binary kodCaseSensitivity CI (case insensitive) veya CS (case sensitive) değerlerini alabilir
AccentSensitivity AI (accent insensitive) veya AS (accent sensitive) olabilir.
BIN ifadesi, text sıralama yerine binary sort algoritmasına göre sıralamanın yapılacağını bildirir.
Örneğin SQL_Latin1_General_Pref_CP437_CI_AS ifadesi Latin1_General(alphabet), Pref(Uppercase önceliği), CP437(437 nolu code page), CI(Case Insensitive) ve AS(Accent Sensitive) özelliklerinin kullanılacağı anlamına gelir. Aynı şekilde Türkçe databaseler için kullanılabilecek SQL_Latin1_General_CP1254_CI_AS ifadesi de Latin alfabesinde 1254 Page Code’un(256 karakter ki Türkçe bu aileye dahildir) Case Insensitive ve Accent Sensitive özellikleriyle kullanılacağı anlamına gelmektedir.
Buşekilde SQL Server collationlar aşağıdaki suffixlerle tanımlanmıştır.

_BIN (Binary)

_CI/_CS (Case Sensitivity)

_AI/_AS(Accent Sensitivity)

_KS(Kana character Sensitivity, Japonca kana karakter seti) (hiragana/katakana)

_WS(Width Sensitivity)(full[double-byte character]/half width[single-byte character]) SQL Server’da iki kaydın sıralama veya karşılaştırılmasında 5 ihtimal vardır.

Ascending or descending? (is ‘a’>’b’?)

Case-sensitive? (is ‘a’>’A’?)

Accent-sensitive? (is ‘a’=’á’?)

Character width? (is ‘a’>’aa’?)

Kana character types? (is ‘?’=’?’?)SQL Server’daki collation ad ve açıklamalarına erişmek için fn_helpcollations fonksiyonu kullanılabilir.

1	SELECT * FROM fn_helpcollations()

SQL Server’da collation ayarları,

Server

Database

Column

Expression bazında gerçekleştirilebilir.
Server bazında collation ayarlaması, oluşturulacak databaseleri etkiler ve onların default collation değeri olmuş olur. SQL Server, ilk kurulduğunda Windows’un yerel ayarlarına(Regional and Language Options) uygun collation değerini set eder. Serverin collation bilgisi, Management Studio’da Object Explorer penceresinde ilgili serverin Database Engine’nine sağ tıklayıp properties bölümünden General sekmesindeki Server Collation bölümünden görülebilir. Ayrıca SERVERPROPERTY fonksiyonuna collationparametresi gönderilerek te öğrenilebilir.

1	SELECT SERVERPROPERTY('collation')

Bu query, benim makinemde Turkish_CI_AS değerini döndürdü. Makinemizdeki SQL Server instance’in hangi karakter sıralaması(sort order) ve karakter seti(character set) kullandığını öğrenmenin daha açık yolu sp_helpsort procedureni kullanmaktır. Bu procedure, benim makinem için Turkish, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitivedeğerini döndürdü.Server’in collation bilgisi, ../Tools/BINN klasörü altındaki RebuildM.exearacıyla değiştirilebilir.
Database seviyesinde collation tanımlaması yapılabilir. Bütün databaseler default olarak server collation olmak üzere bir collation değerine sahiptir. Management Studio ortamında yeni bir database oluşturduğumuzda Options sekmesinde Collation bölümünden seçim yapılabilir veya olarak bırakılabilir. Veya T-SQL yardımıyla database oluşturulurken COLLATE sözcüğüyle collation değeri girilebilir.

1	CREATE DATABASE FrDatabase COLLATE French_CI_AI

Sunucu üzerindeki databaselerin collation değerlerini görmek için sys.databases tablosu sorgulanabilir veya

1 2	--Sistemdeki databaselerin collation bilgilerini listeyelim SELECT name, collation_name FROM sys.databases

Veritabanındaki nesne sorgulamalarında collation değeri rol oynadığı için Turkish_CI_ASdeğerine sahip bir databasede Musteri isimli bir tablomuz olsun. Bunu hem Musteri hem de Musterİ olarak çağırabiliriz. Çünkü bu collation, Türkçe dilinin büyük-küçük karektere duyarlı olmayan şeklini temsil etmektedir, Türkçe’de küçük “i” ile büyük “İ” birbirine eşit olduğu için tablo ismi sorun çıkarmamaktadır. Bu tabloyu MusterI diye büyük “ı” ile çağıramayız bu şekilde çağırmak için collation tipinin İngilizce(SQL_Latin1_General_CP1_CI_AS) olması gerekmektedir. SQL Server’da bir database’in Collation değerlerini değiştirmek için o database’i single user moda getiripALTER DATABASE . . . ifadesi kullanılır.
Veritabanı üzerinde oluşturulmuş tablo kolonları seviyesinde de collation tanımlaması yapılır. Aynı şekilde yukarıda bahsettiğimiz veri türündeki kolon için özel bir collation tanımlaması yapılmazsa database için geçerli collation değerini override edilir. Management Studio’da Table Designer bölümünden kolonun collation değer set edilir. Aynı şekilde CREATE TABLE aşamasında da bu değer set edilebilir.

1 2 3

CREATE TABLE [dbo].[OGRENCI](     [AdSoyad] [char](10) COLLATE Turkish_CI_AS NULL ) ON [PRIMARY]

Sanırım en ilginç yanı SQL Server’da özellikle koşul ve sıralama esnasında deyimler seviyesinde de collation değeri girebiliyor olmamızdır. Bunu sorgulamalarda yine aynı şekilde COLLATE ifadesiyle sağlıyoruz. COLLATE [Windows_Collation_name|SQL_Collation_Name]Yukarıda bahsettiğimiz tablo ismindeki büyük-küçük harf duyarlılığı tablo içerisindeki veriler için de geçerlidir. Örneğin ACicekci şeklinde bir kayıt için sorgulamalarda hemACicekci hem de ACICEKCI durumunda gelinmesi isteniyorsa aşağıdaki gibi bir sorgulama yapmamız gerekir. UYE isimli tablonun UserNameEng ve UserNameTr isimli kolonları bulunsun. UserNameEng kolonunun collation değeri SQL_Latin1_General_CP1_CI_ASUserNameTr kolonunun collation değeri de Turkish_CI_AS olsun. Her ikisini de char(10) olarak set edip her iki kolona da ACicekci bilgisini girelim.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

--Kayıt gelir SELECT * FROM UYE WHERE UserNameTr='MCicekci' --Kayıt gelmez SELECT * FROM UYE WHERE UserNameTr='MCICEKCI' --Kayıt gelir SELECT * FROM UYE WHERE UserNameEng='MCicekci' --Kayıt gelir SELECT * FROM UYE WHERE UserNameEng='MCICEKCI' --Eğer TR collation'da her iki kaydında gelinmesi isteniyorsa -- COLLATE ifadesi kullanılır. SELECT * FROM UYE WHERE UserNameTr= 'MCIcekcI' COLLATE SQL_Latin1_General_CP1_CI_AS

Tablodaki kolonun collation değerini değiştirmek için ALTER TABLE . . . ifadeesi kullanılır.

1 2 3 4

ALTER TABLE Son olarak önemli bir not ile konuyu kapatalım. <strong>Farklı collation türündeki kolonları birbirleriyle karşılaştıramayız.</strong> Yani örneğimizdeki <em>UserNameTr</em> ile <em>UserNameEng</em> kolonlarını birbiriyle karşılaştırmak istediğimizde <font color="#ff0000">Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Turkish_CI_AS" in the equal to operation.</font> hatası oluşur. SELECT * FROM UYE WHERE UserNameTr=UserNameEng

Bu yazı C#, VB.NET, ASP.NET kategorisine 29 Aralık 2006 tarihinde tarafından gönderildi.