SQL-Server collation, what is it and how to change db/column collation

时间:2022-05-06 12:30:42

The thing about collations is that although database have it's own collation, every table, and every column can have it's own collation. If not specified it takes the default of it's parent object, but can be different.

When you change collation of database, it will be new default for all new tables and columns, but it doesn't change the collation of existing objects inside database. You have to go and change manually the collation of every table and column.

Luckily there are scripts available on the internet that can do the job. I am not going to recommend any as I haven't tried them but here are few links:

http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database

Update Collation of all fields in database on the fly

http://www.sqlservercentral.com/Forums/Topic820675-146-1.aspx

If you need to have different collation on two objects or can't change collations - you can still JOIN between them using COLLATE command, and choosing the collation you want for join.

SELECT * FROM A JOIN B ON A.Text = B.Text COLLATE Latin1_General_CI_AS 

or using default database collation:

SELECT * FROM A JOIN B ON A.Text = B.Text COLLATE DATABASE_DEFAULT
 
BUT when I executing
ALTER DATABASE [optimiser] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [optimiser] COLLATE SQL_Latin1_General_CP1_CI_AS
ALTER DATABASE [optimiser] SET MULTI_USER
i got below error: 
Msg 1205, Level 13, State 68, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
 
the solution is:
     in db configuration manager, disable TCP/IP protocol or something similar to cut down all connections, then try the script again.

SQL-Server collation, what is it and how to change db/column collation的更多相关文章

  1. To fix sql server 2008 r2 Evaluation period has expired by change the key

    PTTFM-X467G-P7RH2-3Q6CG-4DMYB 数据中心版:PTTFM-X467G-P7RH2-3Q6CG-4DMYB   测试可用 开 发者 版:MC46H-JQR3C-2JRHY-XY ...

  2. System Databases in SQL Server

    https://docs.microsoft.com/en-us/sql/relational-databases/databases/system-databases SQL Server incl ...

  3. 在64位SQL Server中创建Oracle的链接服务器

    当我们同时使用SQL Server和Oracle来存储数据时,经常会用到跨库查询.为了方便使用跨库查询,一个最好的办法就是通过创建链接服务器来实现.既可以在SQL Server中创建Oracle的链接 ...

  4. SQL Server Analysis Services SSAS Processing Error Configurations

    转载:https://www.mssqltips.com/sqlservertip/3476/sql-server-analysis-services-ssas-processing-error-co ...

  5. SQL: enable sa Account in SQL Server

    Link: http://sudeeptaganguly.wordpress.com/2010/04/20/how-to-enable-sa-account-in-sql-server/ 引用: Wh ...

  6. SQL Server中使用正则表达式

    SQL Server 2005及以上版本支持用CLR语言(C# .NET.VB.NET)编写过程.触发器和函数,因此使得正则匹配,数据提取能够在SQL中灵活运用,大大提高了SQL处理字符串,文本等内容 ...

  7. SQL Server的链接服务器(MySQL、Oracle、Ms_sql、Access、SYBASE)

    原文:SQL Server的链接服务器(MySQL.Oracle.Ms_sql.Access.SYBASE) 一.使用 Microsoft OLE DB Provider For ODBC 链接MyS ...

  8. SQL Server 数据库部分常用语句小结(二)

    9. 查询备份还原数据库的进度. select command ,percent_complete ,est_time_to_go=convert(varchar,(estimated_complet ...

  9. 链接SQL Server服务器

    链接SQL Server服务器:      1.使用 ODBC 的 Microsoft OLE DB 提供程序         EXEC sp_addlinkedserver '别名','','MSD ...

随机推荐

  1. Linux中的命令 make -f 是什么意思

    出处:http://cache.baiducontent.com/c?m=9f65cb4a8c8507ed4fece7631046893b4c4380146d96864968d4e414c422460 ...

  2. Linux for QQ 安装

    1.sudo apt-get install wine 2.打开网址http://www.ubuntukylin.com/application/show.php?lang=cn&id=279 ...

  3. 接口测试从未如此简单 - Postman (Chrome插件)【转】

    一个非常有力的Http Client工具用来测试Web服务的, 我这里来介绍如何用它测试restful web service 注:转载请注明出处http://www.cnblogs.com/wade ...

  4. Spring-data-redis操作redis cluster

    Redis 3.X版本引入了集群的新特性,为了保证所开发系统的高可用性项目组决定引用Redis的集群特性.对于Redis数据访问的支持,目前主要有二种方式:一.以直接调用jedis来实现:二.使用sp ...

  5. [转载]解决在win10中webstrom无法使用命令行(Terminal)

    转载地址:https://qiaolevip.iteye.com/blog/2217688 原因:计算机从win7更新到win10,webstorm9命令框无法输入,以为是webstorm问题和win ...

  6. js 实现json数组集合去重,差集,并集,交集。

    let list = [ { id: "1", content: "A" }, { id: "2", content: "B&qu ...

  7. input 随笔

    1,input 点击出现蓝色外边框 解决:outline:none

  8. 喵哈哈村的魔法考试 Round #19 (Div.2) 题解

    题解: 喵哈哈村的魔力源泉(1) 题解:签到题. 代码: #include<bits/stdc++.h> using namespace std; int main(){ long lon ...

  9. 高性能mysql-----MySQL&lowbar;explain关键字分析查询语句&lpar;一&rpar;

    转载地址:https://www.cnblogs.com/xpp142857/p/7373005.html   MySQL_explain关键字分析查询语句 通过对查询语句的分析,可以了解查询语句的执 ...

  10. 搜索菜单栏侧滑效果控件SearchView

    搜索菜单栏侧滑效果控件SearchView 本人视频教程系类   iOS中CALayer的使用 效果1: 效果2: 项目中用到的图片 bgImg@2x.png: 源码: SearchView.h + ...