将 SQL Server 排序规则从区分大小写更改为不区分大小写?

2021-12-13 00:00:00 sql-server-2008 sql-server collation

我最近安装了 SQL Server 2008，并选择了排序规则区分大小写.我想让整个实例不区分大小写(而不是该实例中的数据库).如果我更改排序规则，它会影响任何现有的数据库吗?如果是，以什么方式?

I've recently installed SQL Server 2008 and I selected collation as case sensitive. I want to make it case insensitive for the entire instance (not for a database in that instance). If I change the collation does it affect any existing databases? if so in what way?

推荐答案

您基本上需要再次运行安装以使用新的排序规则重建 master 数据库.您不能以任何其他方式更改整个服务器的排序规则.

You basically need to run the installation again to rebuild the master database with the new collation. You cannot change the entire server's collation any other way.

见:

  • MSDN:设置和更改服务器排序规则
  • 如何更改数据库或服务器排序规则(在页面中间)
  • MSDN: Setting and changing the server collation
  • How to change database or server collation (in the middle of the page)

更新:如果要更改数据库的排序规则，可以使用此 T-SQL 片段获取当前排序规则:

Update: if you want to change the collation of a database, you can get the current collation using this snippet of T-SQL:

SELECT name, collation_name 
FROM sys.databases
WHERE name = 'test2'   -- put your database name here

这将产生一个类似于:

Latin1_General_CI_AS

_CI 的意思是不区分大小写"——如果你想区分大小写，用 _CS 代替:

The _CI means "case insensitive" - if you want case-sensitive, use _CS in its place:

Latin1_General_CS_AS

所以你的 T-SQL 命令是:

So your T-SQL command would be:

ALTER DATABASE test2 -- put your database name here
   COLLATE Latin1_General_CS_AS   -- replace with whatever collation you need

您可以使用以下命令获取服务器上所有可用排序规则的列表:

You can get a list of all available collations on the server using:

SELECT * FROM ::fn_helpcollations()

您可以使用以下命令查看服务器的当前排序规则:

You can see the server's current collation using:

SELECT SERVERPROPERTY ('Collation')

相关文章