What does the collation influence the database ?

What does the collation influence the database ?

 

--在insert数据时，一般的，字符会根据该column的collation进行解析，随后将解析后varbinary存入数据库

----如果插入的字符无法在collation对应的page中查找到，则会变为'?'(3F)

--随后在读取数据时，一般的，字符会根据该column的collation进行解析，随后将解析后的结果返回。

----当字符type为nvarchar等时，字符会根据unicode统一字符集解析，随后将结果返回

--testing

--创建一个表，将c2的collation设为Latin1_General_CI_AS，随后将'中国'插入；

--看数据库中实际的存储结果。

drop table t5

create table t5

(

    c1 int,

    c2 varchar(20)collate Latin1_General_CI_AS

)

insert into t5values(0,'中国')

--打印'中国'，会发现在Latin1_General_CI_AS下中国不被识别，所以编码变为0x3F3F('??')

print convert(varbinary,'中国'collate Latin1_General_CI_AS)

select CONVERT(varbinary,c2),*from t5

--所以以下三个结果是返回的结果是一样的

select CONVERT(varbinary,c2),*from t5 where c2='中国'

select CONVERT(varbinary,c2),*from t5 where c2='中央'

select CONVERT(varbinary,c2),*from t5 where c2='??'

--现在将字符集改掉,改掉之前先看一下日志和内存里存的东西

dbcc log(Testdb1,4)

DBCC IND('TESTDB1', t5,-1)

DBCC PAGE('TESTDB1',1,78,3)withtableresults

alter table t5altercolumn c2varchar(20)collate Chinese_PRC_CI_AS

--修改以后再看一下日志，发现它留下了54条记录，做了很多事，其中有一些删除添加和修改ROW的操作。--还有一些是SET操作，但不太明白具体的意思

dbcc log(Testdb1,4)

--再看page中的数据，有所变化，但终究还是3F3F

DBCC PAGE('TESTDB1',1,78,3)withtableresults

select CONVERT(varbinary,c2),*from t5 --原先的还是无法显示，因为当初存的时候就是3F3F，果断只能显示??

--但是新加进去的是可以的正确显示的

insert into t5values(2,'中国')

select CONVERT(varbinary,c2),*from t5

dbcc log(Testdb1,4)

DBCC PAGE('TESTDB1',1,78,3)withtableresults

--但是如果collation改成Latin1_General_CI_AS

alter table t5altercolumn c2varchar(20)collate Latin1_General_CI_AS

select CONVERT(varbinary,c2),*from t5 --原先的数据变成了3F3F

DBCC PAGE('TESTDB1',1,78,3)withtableresults--从PAGE中可以看到数据被改为了3F3F

--说明SQLSERVER在修改collation的时候的确会对原先的数据进行校验

--如果发现不认识的字符，还是会变为3F3F。

--所以，预测在这时候，即使把collation改回Chinese_PRC_CI_AS也无济于事

alter table t5altercolumn c2varchar(20)collate Chinese_PRC_CI_AS

select CONVERT(varbinary,c2),*from t5

DBCC PAGE('TESTDB1',1,78,3)withtableresults--只不过是让后面又多了一个3F3F

 

How to modify database’s collation?

 

alter database ___ collate ___

但是通过上面的实验可知，这样修改已有数据的数据库的collation其实会有潜在的风险。

因为数据库在修改collation的时候就会对原先的数据进行校验和修改，因此，推荐的做法是

新建一个表然后把原来的全都复制过去。

在网上可以找到这种方式的批处理指令。（建议SQL SERVER可以把它加入到数据库操作的选项中去。XD）

What will happen if the tempdb’s collation is different from user database’s?

 

--testcollation among tempdb and userdb

--创建一个usertable,collation是根据userdb默认的Chinese_PRC_CI_AS

create table T_user

(

    username varchar(50),

    alias varchar(50)

)

--插入一条数据

insert into T_uservalues('aaa111','a1')

--创建一个和usertable一样的temptable

CREATE TABLE #TestTempT1(usernamevarchar(50), aliasvarchar(50));

INSERT INTO #TestTempT1SELECT*FROM T_User;

--作连接，发现会报collation conflict between两个不同的collations

select * from T_user inner join#TestTempT1 on T_user.alias= #TestTempT1.alias;

 

--这时候可以在expression level设置collate database_default

select * from T_user inner join#TestTempT1 on T_user.alias= #TestTempT1.aliascollate database_default;

--或者将temptable的column改为database_default

alter table #TestTempT1altercolumn aliasvarchar(50)collate database_default;

select * from T_user inner join#TestTempT1 on T_user.alias= #TestTempT1.alias;

--或者将user table的column改为和tempdb一样的collation

alter table #TestTempT1altercolumn aliasvarchar(50)collate Latin1_General_CI_AS;

alter table t_useraltercolumn aliasvarchar(50)collate Latin1_General_CI_AS

select * from T_user inner join#TestTempT1 on T_user.alias= #TestTempT1.alias

--总之让他们一致就行了.当然前提是改collate不会对原先的数据造成影响。

 

参考资料

 

 

·        http://msdn.microsoft.com/en-us/library/ms190920.aspx

·        http://msdn.microsoft.com/en-us/library/ms190920.aspx

·        http://www.cnblogs.com/Ammy/archive/2008/08/22/1273904.html