Bu yazımda bir database in collation ın nasıl değiştirileceğini anlatıyor olacağım. Bu işleme başlamadan önce ilk olarak genel hatlarıyla collation nedir diye bakalım.
Collation, SQL Server da character set anlamına gelmektedir. Eşitliklerde, order işleminde, büyük küçük harf ayrımında karakterlerin hangi mantıkta kullanılacağını belirler.
Örneğin genelde Türkçe database lerde kullanılan TURKISH_CI_AS collation ında ki CI ifadesi Case Insensitive(Büyük küçük harf ayrımı olmasın) anlamına gelmektedir.
Collation ın ne şekilde bir fark oluşturduğunu görmek için aşağıdaki örneklere bakalım.
if upper('filiz') = 'FİLİZ' collate Turkish_CI_AS select 'eşit' else select 'eşit deðil'
if upper('filiz') = 'FILIZ' collate Turkish_CI_AS select 'eşit' else select 'eşit deðil'
if upper('filiz') = 'FİLİZ' collate SQL_Latin1_General_CP1_CI_AS select 'eşit' else select 'eşit değil'
if upper('filiz') = 'FILIZ' collate SQL_Latin1_General_CP1_CI_AS select 'eşit' else select 'eşit değil'
Gördüğünüz gibi Türkçe de I ve i birbirinden farklı 2 karakter iken ingilizce de bu 2 karakter birbirinin aynısıdır.
SQL Server da 3 level da collation verilebilmektedir.
- Server Collation
- Database Collation
- Column Collation
- Expression Collation
Server ın collation ın ne olduğuna bakmak için aşağıdaki script i kullanabilirsiniz.
SELECT SERVERPROPERTY('collation')
Yada SSMS üzerinden Server>>Properties ekranından bakabilirsiniz.
Database collation ınlarına sys.databases system table ından bakabilirsiniz.
select collation_name,* from sys.databases
Yada SSMS üzerinden Database>>Properties ekranından bakabilirsiniz.
Bu ön bilgilerden sonra şimdi DB collation nasıl değiştirelir konumuza geri dönelim.
İlk olarak şunu sorgulamak gerekiyor. Bir DB nin collation ınını niye değiştirmek zorunda olalım.
Örneğin Server collation ını Turkish_CI_AS olsun, bununla beraber TempDB collation ınada Turkish_CI_AS olduğunu düşünelim. İşlem yapmak istediğimiz DB de SQL_Latin1_General_CP1254_CI_AS olsun.
Bu DB üzerinde TempDB kullanacak herhangi bir sorgu çalıştırdığımızda (Where clause, order vb) script aşağıdaki hatayı verecektir.
Select *
from dbo.Table1 t1, dbo.Table2 t2
where t1.Column1 = t2.Column2
Msg 468, Level 16, State 9, Procedure ET_Update_Corporate_Customer_Limits, Line 41
Cannot resolve the collation conflict between "Turkish_CI_AS" and "SQL_Latin1_General_CP1254_CI_AS" in the equal to operation.
Where clause collation key i kullanarak bu hatayı aşmak mümkün.
Select *
from dbo.Table1 t1, dbo.Table2 t2
where t1.Column1 COLLATE TURKISH_CI_AS = t2.Column2 COLLATE TURKISH_CI_AS
Ama böyle bir kullanım bütün query lerde değişiklik yapmak gerektiğinden pek kullanışlıu değildir. Ayrıca bu kullanımda table scan yapıldığından dolayı performans olarakta tavsiye edilmez.
Dolayısıyla bizim ihtiyacımız olan DB nin collation ını değiştirmektir.
Eğer column bazında collation kullanılmadıysa aşağıdaki script i uygulamanız collation ı değiştirmek için yeterlidir.
ALTER DATABASE SampleDB COLLATE TURKISH_CI_AS
Lakin eğer column collation kullanıldıysa iş biraz daha karmaşık hale gelmektedir. Yapılması gereken adımlar sırayla aşağıdaki gibidir.
- Constraint leri drop et
- Index leri drop et
- DB nin collation ını değiştir.
- Column collation ları değiştir. Alter column
- Index leri create et
- Constraint leri create et.
Şimdi bu işlemleri hızlıca nasıl yaparız bunu görüyor olacağız.
1.Constraint Drop
Constraint leri drop etmeden önce daha sonra create edebilmek için sistemde ki constraint lerin create scriptleri generate etmemiz gerekiyor.
Bunun için daha önce yazmış olduğum Constraint ler için DROP ve CREATE DDL Scriptleri Generate Etme makalesinde ki SP yi kullanacağız.
Bu SP yi kullanarak aşağıdaki script aracılığıyla constrate create scriptlerini generate ediyoruz.
Use SampleDB
GO
create table #CreateScripts (ID INT , Constraint_Type VARCHAR(100), SQL VARCHAR(8000), Column_Name SYSNAME DEFAULT '')
declare @TableName sysname
declare curs cursor for
select name from sys.objects where type='U'
open curs
fetch next from curs into @TableName
while (@@fetch_status = 0)
begin
insert #CreateScripts
EXEC usp_Create_Table_DDLs @sTable_Name = @TableName, @Create_Table_Ind = 0, @PK_Ind = 1, @FK_Ind = 1, @Check_Ind = 1, @Default_Ind = 1
fetch next from curs into @TableName
end
close curs
deallocate curs
declare @sql varchar(max)=''
select @sql+=sql+char(10) from #CreateScripts
print @sql
drop table #CreateScripts
Messages kısmındaki sonlara doğru Alter ile başlayan satırların tamamını alıp kaydedin. Daha sonra bu scriptler ile constraint leri tekrar create edeceğiz.
Evet şimdi constraint leri drop edebiliriz. Bunun için aşağıdaki script i execute ediniz.
use SampleDB
declare @sql varchar(max)=''
select @sql+='ALTER TABLE ['+tab.name+'] DROP CONSTRAINT ['+cons.name+']; '+char(10)
from sys.objects cons,sys.objects tab
where cons.type in ('C', 'F', 'PK', 'UQ', 'D')
and cons.parent_object_id=tab.object_id and tab.type='U'
order by cons.type
exec(@sql)
2.Index Drop
Constraint te yaptığımız gibi Index te de drop etmeden önce generate scriptleri hazırlamamız gerekiyor ki daha sonra create edebilelim. Create script generate için aşağıdaki script i kullanabilirsiniz.
DECLARE @TabName varchar(100)=NULL
DECLARE @tableName varchar(100)
DECLARE TCur CURSOR FOR
SELECT '['+SCHEMA_NAME(t.schema_id)+'].['+t.name+']' FROM sys.tables t WHERE exists(SELECT TOP 1 1 FROM sys.indexes WHERE object_id=t.object_id and index_id>0)
AND (t.object_id=OBJECT_ID(@TabName) OR @TabName is null)
OPEN TCur
FETCH FROM TCur INTO @tableName
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE ICur CURSOR FOR
SELECT name,is_primary_key from sys.indexes i WHERE exists(SELECT TOP 1 1 FROM sys.index_columns ic
WHERE i.object_id=ic.object_id and i.index_id=ic.index_id) and
i.object_id=OBJECT_ID(@tableName)
OPEN ICur
DECLARE @IName VARCHAR(100),@IsPK BIT,@SQL VARCHAR(MAX),@CName varchar(100),@is_descending_key bit
FETCH FROM ICur INTO @IName,@IsPK
WHILE @@FETCH_STATUS=0
BEGIN
IF(@IsPK=1)
BEGIN
SET @SQL='ALTER TABLE '+@tableName+' ADD PRIMARY KEY'+CHAR(10)+'('+CHAR(10)
DECLARE CCur CURSOR FOR
SELECT COL_NAME(i.object_id,ic.column_id),ic.is_descending_key FROM sys.indexes i inner join sys.index_columns ic
ON i.object_id=ic.object_id and i.index_id=ic.index_id
WHERE i.object_id=OBJECT_ID(@tableName) and i.name=@IName
OPEN CCur
FETCH FROM CCur INTO @CName,@is_descending_key
SET @SQL+='['+@CName+'] '+CASE WHEN @is_descending_key=0 THEN 'ASC' ELSE 'DESC' END+CHAR(10)
FETCH NEXT FROM CCur INTO @CName,@is_descending_key
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL+=',['+@CName+'] '+CASE WHEN @is_descending_key=0 THEN 'ASC' ELSE 'DESC' END+CHAR(10)
FETCH NEXT FROM CCur INTO @CName,@is_descending_key
END
CLOSE CCur
DEALLOCATE CCur
SET @SQL+=');'
PRINT @SQL
END
ELSE
BEGIN
SET @SQL='CREATE '+(SELECT type_desc FROM sys.indexes WHERE object_id=OBJECT_ID(@tableName) and name=@IName)+' INDEX ['+@IName+'] ON '+@tableName+CHAR(10)+'('+CHAR(10)
DECLARE CCur CURSOR FOR
SELECT COL_NAME(i.object_id,ic.column_id),ic.is_descending_key FROM sys.indexes i inner join sys.index_columns ic
ON i.object_id=ic.object_id and i.index_id=ic.index_id
WHERE i.object_id=OBJECT_ID(@tableName) and i.name=@IName
OPEN CCur
FETCH FROM CCur INTO @CName,@is_descending_key
SET @SQL+='['+@CName+'] '+CASE WHEN @is_descending_key=0 THEN 'ASC' ELSE 'DESC' END+CHAR(10)
FETCH NEXT FROM CCur INTO @CName,@is_descending_key
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL+=',['+@CName+'] '+CASE WHEN @is_descending_key=0 THEN 'ASC' ELSE 'DESC' END+CHAR(10)
FETCH NEXT FROM CCur INTO @CName,@is_descending_key
END
CLOSE CCur
DEALLOCATE CCur
SET @SQL+=');'
PRINT @SQL
END
FETCH NEXT FROM ICur INTO @IName,@IsPK
END
CLOSE ICur
DEALLOCATE ICur
FETCH NEXT FROM TCur INTO @tableName
END
CLOSE TCur
DEALLOCATE TCur
Messages kısmındaki yazıları kopyalayıp kaydedelim. Daha sonra create index adımında bu create script ini kullanacağız.
Create script i hazır olduğuna göre artık index leri drop edebiliriz.
Bunun için aşağıdaki script i kullanabilirsiniz.
declare @str varchar(max)=''
select @str += 'DROP INDEX ['+i.name +'] ON ['+schema_name(t.schema_id)+'].['+t.name+']; '+CHAR(10)
from sys.indexes i
left join sys.objects t on t.object_id=i.object_id
where t.type='u' and i.index_id>0
exec(@str)
3.Change DB Collation
DB nin collation ını değiştirmek için aşağıdaki script i kullanabilirsiniz.
ALTER DATABASE SampleDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE SampleDB COLLATE TURKISH_CI_AS
ALTER DATABASE SampleDB SET MULTI_USER
4.Change Columns Collation
Bu adımda DB de default collation kullanmayan column ların collation ını değiştireceğiz. Hangi column ları değiştireceğimizi bulmak için aşağıdaki script i kullanabilirsiniz.
-------------------------------------------------------------
--- LM_ChangeCollation - Change collation in all tables
--- made by Luis Monteiro - ljmonteiro@eurociber.pt
--- modified by wilfred van dijk - wvand@wilfredvandijk.nl
-------------------------------------------------------------
DECLARE @new_collation varchar(100)
DECLARE @debug bit
DECLARE
@table sysname,
@previous sysname,
@column varchar(60),
@type varchar(20),
@legth varchar(4),
@nullable varchar(8),
@sql varchar(8000),
@msg varchar(8000),
@servercollation varchar(120)
/*
uncomment one of the following lines:
*/
set @new_collation = convert(sysname, databasepropertyex(DB_NAME(), 'collation'))
--- set @new_collation = convert(sysname, serverproperty('collation'))
/*
@debug = 0 to execute
*/
set @debug = 1
if @new_collation is null
begin
print 'which collation?'
goto einde
end
DECLARE C1 CURSOR FOR
select 'Table' = b.name,
'Column' = a.name,
'Type' = type_name(a.system_type_id),
'Length' = a.max_length,
'Nullable' = case when a.is_nullable = 0 then 'NOT NULL' else ' ' end
from sys.columns a
join sysobjects b on a.object_id = b.id
where b.xtype = 'U'
and b.name not like 'dt%'
and type_name(a.system_type_id) in ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
and a.[collation_name] <> @new_collation
order by b.name,a.column_id
OPEN C1
FETCH NEXT
FROM C1
INTO @table,@column,@type,@legth,@nullable
set @previous = @table
WHILE @@FETCH_STATUS = 0
BEGIN
if @table <> @previous print ''
set @sql = 'ALTER TABLE ' + QUOTENAME(@table) + ' ALTER COLUMN ' + QUOTENAME(@column) + ' '
set @sql = @sql + @type + '(' + @legth + ')' + ' COLLATE ' + @new_collation + ' ' + @nullable
print @SQL
if @debug = 0
begin
begin try
EXEC (@sql)
end try
begin catch
print 'ERROR:' + ERROR_MESSAGE()
print ''
end catch
end
set @previous = @table
FETCH NEXT
FROM C1
INTO @table,@column,@type,@legth,@nullable
END
CLOSE C1
DEALLOCATE C1
einde:
Bu script sonucununda çıkan alter script lerini kullanarak kolonları alter ederek collation larını değiştiriyoruz.
5.Constraint ReCreate
1 numaralı adımda oluşturduğumuz constraint create script ini kullanarak constraint leri tekrar create ediyoruz.
6. Index ReCreate
2 numaralı adımda oluşturduğumuz index create scriptini kullanarak index leri tekrar create ediyoruz.
Bu adımlardan sonra eğer hiç bir hata almadıysanız DB nin script i değişmiş olması gerekiyor. Kontrol etmek için 4 nolu adımdaki select işlemini tekrarlayabilirsiniz. Bu sorgu sonucunda kayıt dönmemesi gerekiyor.
İyi çalışmalar.
Turgay Sahtiyan
Kaynaklar:
2aea28f6-da05-4077-8238-bb3371870287|1|3.0