SQL Server Collection ayarları verilerin karşılaştırma kuralları, büyük/küçük harf duyarlılığı ve aksanlı harflerin duyarlılık işlemlerinde kullanılan ayarlardır. Collection ayarlarından etkilenen veri türleri, metinsel ifade içeren char ve varchar veri türleridir.
SQL Server kurulumunda, yeni veri tabanı oluşturmada veya bir veri tabanı yedekten dönüldüğünde üzerinde çalışılacak verilerin büyük/küçük harf duyarlılığı, aksan duyarlılığı gibi durumlara toleransını bilmek sistemin hatasız çalışması üzerinde önemli bir şarttır. Bu durumda sisteme tanımlı collection ayarlarını bilmek sistemi ayarlamada kolaylık sağlayacaktır. sys.fn_helpcollations() fonksiyonu ile sistemde yüklü bütün collection listesine ulaşılabilir.
SQL Server, veri tabanı, kolon veya bir sorgu için collection ataması yapıldığında bu işlem veriler üzerinde yapılan sorgulama sonuçlarını değiştirir. Örneğin; Metinsel ifadelerin karşılaştırma, sıralama ve gruplama işlemleri bu ayardan etkilenen temel işlemlerdir.
Collection, verilerin kaydedilirken tutulduğu bit düzeyinde bilginin nasıl tutulacağını ve metinlerin sıralama ve karşılaştırma ayarlarını belirler. Bu ayarlar veri tabanı düzeyinde belirtilenebileceği gibi her kolona ayrı collection’da atanabilir. Collection’nın bu özelliğinden dolayı farklı collection ayarına sahip iki veri tabanı üzerinde çalıştırılan aynı sorguların sonuçları farklılık gösterebilir. Böyle bir durumla karşılaşmamak için mümkün olduğu sürece kurum içerisinde kullanılacak collection ile ilgili bir standart geliştirilmelidir.
SQL Server collection ayarlanırken kullanılan özellikler aşağıdaki tabloda göründüğü gibidir;
| Case-sensitive (_CS) | Büyük/küçük harf ayrımını yapar. Bu seçenek seçili iken küçük harfler büyük harflerden önce sıralanır. Bu seçenek seçili değil iken büyük / küçük harfler karşılaştırma ve sıralamada eşit sayılır. Seçili olmayan hali _CI ile ifade edilir. |
| Accent-sensitive (_AS) | Aksanlı ve aksansız harfleri ayırt eder. Bu seçenek seçili iken ‘a’ ve ‘â’ harfleri eşit sayılmaz. Bu seçenek seçili değil iken ise karşılaştırma ve sıralama işlemlerinde ‘a’ ve ‘â’ gibi harfler eşit sayılır. Seçili olmayan hali _AI ile ifade edilir. |
| Kana-sensitive (_KS) | Hiragana ve Katakana Japon karakterlerini ayırt eder. |
| Width-sensitive (_WS) | Özellikle matematiksel ifade içeren metinlerde tam ve yarım yazılan karakterleri ayır eder. Örneğin; Seçili iken: E=mc2ve E=mc2 ifadeleri eşit sayılmazken, seçili olmadığı durumda da bu iki ifade eşit kabul edilir. |
| Variation-selector-sensitive (_VSS) | Japoncaya özgü bazı karakterlerin ayırt edilmesini sağlar. |
| Binary (_BIN) | SQL Server tablolarındaki verileri, her karakter için tanımlanan bit kalıplarına göre sıralar ve karşılaştırır. İkili sıralama düzeni büyük / küçük harfe ve aksan yapısına duyarlıdır. Aynı zamanda en hızlı şekilde sıralamayı sağlar. |
| Binary-code point (_BIN2)1 | Unicode verileri için Unicode kod noktalarını temel alarak SQL Server tablolarındaki verileri sıralar ve karşılaştırır. Unicode olmayan veriler için, İkili kod noktası ikili türlerle aynı karşılaştırmaları kullanır. |
| UTF-8 (_UTF8) | UTF-8 kodlu verilerin SQL Server üzerinde saklanmasını sağlar. |
Collation Seviyeleri
SQL Server üzerinde dört seviyede collation ayarı tutulabilmektedir. Bu seviyeler;
- Sunucu seviyesinde
- Veri tabanı seviyesinde
- Kolon seviyesinde
- Sorgu seviyesinde
Sunucu Seviyesinde
SQL Server kurulumu sırasında seçilir ve diğer bütün veri tabanı ve kullanıcıların varsayılan collection ayarı olur.
Kurulumdan sonra bu ayar değiştirilmek istenirse öncelikle bütün veri tabanlarının nesneleri ve verilerini dışarı aktardıktan sonra master veri tabanının rebuild işlemine tabi tutulması gerekmektedir. master veri tabanı rebuild edildikten sonra veri tabanları ve veriler tekrardan içeri aktarılır. Bütün bir sunucu seviyesinde collection ayarını değiştirmek yerine veri tabanı oluştururken veri tabanı seviyesinde de collection ayarı seçilebilir.
SELECT CONVERT(varchar, SERVERPROPERTY('collation'));Yukarıdaki kod ile sunucunun mevcut collection ayarı sorgulanabilir.
Sunucu Colletion Ayarının Değiştirilmesi
Sunucu colletion ayarını değiştirmek için aşağıdaki adımlar izlenir;
- Veri tabanları ve nesnelerini yeniden oluşturmak için gerekli scriptler alınır.
- Veri tabanlarındaki bütün veriler dışarı alınır. (export)
- Bütün kullanıcı veri tabanları silinir.
- master veri tabanı yeni colletion ayarına göre rebuild edilir. Bu işlem için aşağıdaki setup komutu ile yapılabilir.
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]
/SQLCOLLATION=CollationName5. Veri tabanları ve nesneleri scriptler yardımı ile yeniden oluşturulur.
6. Veriler tekrar içeri alınır. (import)
Veri Tabanı Seviyesinde Collection
Veri tabanı oluşturulurken seçilir. Veri tabanı oluşturma sırasında seçilmezse varsayılan olarak sunucu seviyesinde seçilen collection seçilir. Veri tabanı oluşturulma veya değiştirilme işlemlerinde COLLATE komutu ile seçilebilir.
Sunucu seviyeside colletion değiştirilmeden sistem veri tabanlarına ait colletion ayararı değiştirilemez.
Veri tabanı colletion ayarı, veri tabanına ait bütün bilgilerde geçerli olur. Bir veri tabanına ait colletion ayarı değiştirildiğinde geçici tablolarla çalışan sorgularında hata alınabilmektedir. Alınan bu hatanın sebebi, geçici tabloların tempbd veri tabanında tutulmasında dolayıdır. Bu tür alınabilecek hataların önüne geçmek için COLLATE komutu ile sorgu düzeyinde colletion ayarı yapılmalıdır.
Veri tabanı oluşturulurken colletion ayarının belirtilmesi ve oluşturulan veri tabanına ait bilgilerin sorgulanması;
USE master;
GO
CREATE DATABASE TestDb
COLLATE Latin1_General_100_CS_AS_SC;
GO
--Veri tabanı bilgilerinin alınması.
SELECT name, collation_name
FROM sys.databases
WHERE name = N'TestDb';
GO
Aşağıdaki örnek kodda olduğu gibi veri tabanına ait colletion ayarı değiştirilebilir. Veri tabanı seviyesinde yapılan colletion ayar değişikliği kolon seviyesinde yapılmış olan ayarları etkilememektedir.
ALTER DATABASE myDB COLLATE Greek_CS_AI;Aşağıdaki kod ile de belli bir veri tabanına ait colletion ayarına ulaşılabilir.
SELECT CONVERT (VARCHAR(50), DATABASEPROPERTYEX('database_name','collation'));Kolon Seviyesinde Colletion Ayarı
Tablo oluşturulurken COLLATE komutu ile her bir kolonun colletion ayarı ayrı ayrı olarak ayarlanabilir, ayarlanmaz ise veri tabanına ait colletion ayarı varsayılan olarak seçilir.
ALTER TABLE ile mevcutta olan kolonların ayarları değiştirilebilir.
ALTER TABLE tabloAd ALTER COLUMN kolonAd NVARCHAR(10) COLLATE Greek_CS_AI;Sorgu Bazlı Colletion Ayarı
Sorgu bazlı colletion ayarında sorgu çalışma zamanında COLLATE komutu ile ayarlanır. Özellikle ORDER BY işlemlerinde kullanılır.
SELECT isim FROM urunler ORDER BY name COLLATE Latin1_General_CS_AI;Collation ayarı veri tabanında sorguların doğru sonuç döndürmesi için önemli olduğu kadar performans için de önemli olduğundan daha projelerin kavramsal çalışmalarında karar verilmesi gereken bir konudur.