MSSQL Server Veritabanı ve Tabloların Collation Değişimi

Bu makalede veritabanı ve tabloların collation değiştirme işlemini ele alacağız.

SQL Server’da Collation (Karşılaştırma Düzeni), veritabanındaki verilerin nasıl saklanacağını, sıralanacağını ve karşılaştırılacağını belirleyen kurallar bütünüdür. Basit bir ifadeyle collation; “A harfi mi önce gelir, B mi?” veya ” ‘a’ harfi ile ‘A’ harfi aynı mıdır?” gibi soruların cevabını SQL Server’a öğretir.

Bir collation ayarı seçtiğinizde aslında şu üç temel kuralı belirlemiş olursunuz:

• Sıralama (Sorting): ORDER BY kullandığınızda verilerin hangi alfabetik sıraya göre dizileceği.
• Büyük/Küçük Harf Duyarlılığı (Case Sensitivity): “admin” ile “ADMIN” kelimelerinin aynı kabul edilip edilmeyeceği.
• Karakter Seti (Character Set): Hangi dilin özel karakterlerinin (ö, ç, ş, ğ, ı, İ) tanınacağı.

Collation isimlerinin sonunda genellikle bazı kısaltmalar görürsünüz. Bunlar veritabanınızın davranışını doğrudan etkiler:

Kısaltma	Açılımı	Açıklama
CS	Case Sensitive	Büyük ve küçük harfe duyarlıdır. (“a” ≠ “A”)
CI	Case Insensitive	Büyük ve küçük harfe duyarlı değildir. (“a” = “A”)
AS	Accent Sensitive	Aksan işaretlerine duyarlıdır. (“e” ≠ “é”)
AI	Accent Insensitive	Aksan işaretlerine duyarlı değildir. (“e” = “é”)

Büyük veritabanlarında ve özellikle yüklü tabloların bulunduğu ortamlarda Collation değişikliği yapmak, göründüğünden çok daha riskli ve maliyetli bir işlemdir. Bu işlem sadece bir “metadata” (tanım) değişikliği değil, verinin fiziksel olarak yeniden işlenmesi anlamına gelir.

Bir tablonun collation ayarını değiştirdiğinizde, SQL Server o tablodaki tüm CHAR, VARCHAR, NCHAR ve NVARCHAR kolonlarını tek tek elden geçirir.

• İndeksler: Eğer ilgili kolon üzerinde bir Index (özellikle Primary Key) varsa, bu indekslerin tamamen silinip yeniden oluşturulması gerekir. Çünkü collation değiştiğinde “sıralama” mantığı değişir.
• Disk Alanı: İşlem sırasında hem eski veri hem yeni yapı diskte yer kaplayacağı için tempdb ve Transaction Log dosyalarınız şişebilir.

Collation değişikliği işlemi tablo seviyesinde Schema Modification (Sch-M) Lock koyar.

• Bu kilit, işlem bitene kadar tabloya gelen tüm SELECT, INSERT, UPDATE ve DELETE isteklerini durdurur.
• Milyonlarca satırlık bir tabloda bu işlem saatler sürebilir, bu da uygulamanızın tamamen durması (downtime) demektir.

Eğer mevcut collation ile yeni geçeceğiniz collation farklı kod sayfalarına (Code Page) sahipse, bazı özel karakterler (Türkçe karakterler gibi) “soru işareti” (?) veya anlamsız karakterlere dönüşebilir.

Bazen kullanıcılar veritabanının yanlış collation ile oluşturulduğunu düşünürler. Bunun için veritabanının collation değişikliği için talep de bulunurlar. Ya da veritabanın da bulunan tablo veya ilgili kolonun collation yapısını veritabanından bağımsız bir şekilde değiştirilmesini isteyebilirler. Bu makalemizde böyle durumlarda neler yapılır değinelim.

Sonradan veritabanı collation değişikliği veritabanı altında bulunan tablolarda collation değişimine sebep olmaz.

Bu örneğimizi DENEME veritabanımız üzerinde uygulamalı bir şekilde görelim. Veritabanımızın hangi collation’da olduğunu properties ekranından General sekmesinde görebiliriz.

Veya aşağıdaki komutlar yardımıyla da öğrenebiliriz.

1. Komut

USE master
GO
SELECT DATABASEPROPERTYEX('AdventureWorks2012','COLLATION')

2. Komut

select collation_name from sys.databases where name='AdventureWorks2012'

İlk olarak veritabanı bazında Collation nasıl değiştirilir bunu görelim. Veritabanına sağ tıklanıp Properties ekranında Options kısmında Collation bölümünde yeni Collation değerimizi seçiyoruz.

Yeni collation değerini ayarladıktan sonra yaptığımız işlemin Script’ini alıyoruz. Kodumuzun başına veritabanımızı single user mod kod bloğunu ekliyoruz. Yoksa veritabanı kullanılıyor diye bir hata mesajı alırız.

USE master
GO
ALTER DATABASE [DENEME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [DENEME]
GO
ALTER DATABASE [DENEME] COLLATE Turkish_CI_AS
GO
GO
ALTER DATABASE [DENEME] SET MULTI_USER

Singel_user moda alınırken ilgili mod başka bir session tarafından alınabilir. Aşağıdaki komut aracılığıyla veritabanı üzerinde bağlı olan oturumları görebiliriz. Bu komutla ilgili oturumlar kill edilebilir.

SELECT spid, loginame, status, hostname, program_name FROM 
sys.sysprocesses WHERE dbid = DB_ID('DB_NAME');

İkinci bir komut:

select 'kill '+CAST(spid AS varchar(50)) from sys.sysprocesses where dbid=DB_ID('DB_NAME')

Ayrıca database’i singel user moda aldıktan sonra collation değişiminde şöyle bir hata mesajıyla karşılaşmış oluruz. SCHEMABINDING seçeneği kullanılarak oluşturulmuş bir VIEW veya FUNCTION, veritabanının collation ayarını kullanıyorsa, bu ayar değiştirilemez. ALTER DATABASE ile collation değiştirmek için, önce bu bağımlılığı kaldırmanız gerekir.

The object ‘stringSplitter’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5072, Level 16, State 1, Line 10
ALTER DATABASE failed. The default collation of database ‘DB_NAME’ cannot be set to Turkish_CI_AS.

İlgili function ve view altına bakarak belirtilen ifadenin silinip tekrardan oluşturulması gerekmektedir. Değişiklikten sonra.

Aşağıdaki sorgu, schema-bound nesneleri listeler. Gelen sonuca göre ilgili ifadeler drop edilir.

SELECT name, type_desc 
FROM sys.objects 
WHERE OBJECTPROPERTY(object_id, 'IsSchemaBound') = 1;

Bir diğer kullanacağımız komut:

SELECT name, definition
FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition LIKE '%SCHEMABINDING%';

Collation değişimi sırasında ikinci karşılaşacağımız hata veritabanı altında bulunan bazı constrant yapılarının collation ile bağlantısının olmasıdır. Öncelikle bu constrantların kaldırılması gerekmektedir.

Msg 5075, Level 16, State 1, Line 10
The object ‘CK_Product_ProductLine’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Collation değişikliği yaptıktan sonra veritabanımız üzerinden kontrol işlemi yapıyoruz.

Veritabanı bazında collation değişikliğinin gerçekleştiğini görüyoruz. Şimdi veritabanı altında bulunan tablomuzun Collation’ı değişmiş mi bunu gözlemleyelim. Aşağıdaki Kod bloğunda ilgili veritabanı altındaki tabloların Collation yapısını görebiliriz.

SELECT t.name TableName, c.name ColumnName, collation_name  
FROM sys.columns c  
inner join sys.tables t on c.object_id = t.object_id

Bir başka veritabanı altında bulunan tabloların collation yapısını bulmak için aşağıdaki komut kullanılmaktadır.

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME IS NOT NULL
ORDER BY TABLE_NAME;

Veritabanı değişikliği öncesinde oluşturulan tablonun Collation yapısı değişmemiş. Yukarıdaki resimde null ifadesi veri  türünün int olmasından kaynaklanmaktadır.

Şimdi aynı veritabanı altında yeni bir tablo oluşturalım. Yeni bir tablo oluşturduktan sonra collation yapısının güncel veritabanı collation yapısıyla aynı olduğu görülmektedir.

Önceki collation altında oluşturulmuş olan tabloları yeni collation yapısını geçirmek için aşağıdaki komut kullanılmaktadır. Burada yapmamız gereken NewCollation  kısmında veritabanı ile uyumlu collation yazmamız gerekiyor.

declare @NewCollation sysname
set @NewCollation = 'Turkish_CI_AS'
select
    'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(stable.schema_id)) + '.' + QUOTENAME(stable.name) +
    ' ALTER COLUMN ' + QUOTENAME(scol.name) + ' ' + stype.name + '(' +
    CASE WHEN scol.max_length = -1 THEN 'max' ELSE CONVERT(varchar(10),scol.max_length) END +
    ') collate ' + @NewCollation + '
    go
    '
from sys.columns scol inner join sys.tables stable    on scol.object_id = stable.object_id
        inner join sys.types stype on scol.user_type_id = stype.user_type_id
where  scol.collation_name is not null and OBJECTPROPERTY(stable.object_id,N'IsMSShipped')=0

Gelen ekranda her kolunla ilgili değişiklik yapacağımız Script geliyor. Bunların teker teker kopyalanıp çalıştırılması gerekmektedir.

Çalıştırıldıktan sonra ilgili tablolarımızın yeni Collation yapısına geçtiği gözlemlenir.

Aşağıdaki komut ile ilgili tablo altındaki herhangi bir kolunun Collation yapısını vermektedir.

USE deneme
GO
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TABLE_NAME')
AND name = 'COLUMN_NAME'

Ya da SSMS arayüzün de ilgili kolunun Properties ekranında görebilirsiniz.

Not: Bazı kolonlarda collation değişimi sırasında hata alınabilir. Belirtilen linkteki ilgili Makalenin okunması gerekmektedir.

Güvenli Bir Geçiş İçin Öneriler

Eğer bu işlemi mutlaka yapmanız gerekiyorsa şu adımları izlemek daha sağlıklıdır:

1. Script ile İlerleyin: Tüm veritabanını bir kerede değiştirmek yerine, tabloları ve kolonları tek tek değiştiren scriptler hazırlayın.
2. Constraint ve Index Yönetimi: Önce kısıtlamaları (Foreign Key, Check Constraint) ve indeksleri drop edin, collation değiştirin, sonra tekrar create edin.
3. TempDB’ye Dikkat: Büyük veri taşınacağı için tempdb boyutunuzun yeterli olduğundan emin olun.
4. Aşamalı Geçiş: Mümkünse boş bir veritabanı oluşturup (doğru collation ile), veriyi INSERT INTO … SELECT yöntemiyle parça parça taşımak, canlı tabloda ALTER yapmaktan bazen daha güvenli ve hızlı olabilir.

Bu makalede veritabanı Collation yapısı nasıl değiştirilir konusunu görmüş olduk.

Başka bir makalede görüşmek dileğiyle..

Onlar, ” Yalnızca Allah’a Dayanıp Güvenirler. ” Mücadele-10