Makale Özeti

SQL Server da Collation, 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. Bu yazımda bir database in collation ın nasıl değiştirileceğini anlatıyor olacağım.

Makale

Database Collation ını Değiştirmek

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 bu işlem aşağıdaki script kadar kolaydır.

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.

1.       Constraint leri drop et

2.       Index leri drop et

3.       DB nin collation ını değiştir.

4.       Column collation ları değiştir. Alter column

5.       Index leri create et

6.       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 ..... yazısındaki 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
www.turgaysahtiyan.com

 

Kaynaklar:

·         http://www.sqlservercentral.com

·         http://www.codeguru.com