如何使用SQL设置列的排序规则？ [英] How to set collation of a column with SQL?

问题描述

最初，我在本地计算机上创建了SQL Server数据库。我将其排序规则设置为 Latin1_General_CI_AI ，并且一切正常。当我将完成的工作移至Web托管SQL Server时，我遇到了问题：它们使用了不同的数据库排序规则。那我现在该怎么办？

Originally, I created my SQL Server database on a local computer. I set its collation to Latin1_General_CI_AI and everything worked well. When I moved the finished work to the web hosting SQL Server, I encountered problem: they use a different database collation. So what can I do now?

更具体地说，我需要 Latin1_General_CI_AI ，但是他们有 Czech_CI_AS 。当比较捷克语言的字符串时，这两个有很大不同（令人惊讶的是，我需要使用latin1 general，而不是Czech，以获得正确的结果。）

To be more specific, I need Latin1_General_CI_AI, but they have Czech_CI_AS. These two differ significantly when comparing strings in Czech language (surprisingly I need latin1 general, not Czech, to get correct results.)

当我尝试更改数据库的排序规则时，服务器抱怨我没有用户权限。我试图联系支持台，但没有运气。我可以帮忙吗？

When I tried to change collation of my database, the server complained that I don't have user permission to do that. I tried to contact support desk, but no luck. Can I help myself?

我知道每个表列都可以有自己的排序规则，所以也许我应该将所有字符串列设置为 Latin1_CI_AI 。但是我不知道该怎么做。我只有SQL访问数据库（遗憾的是没有SQL Server Management Studio）。

I know that possibly each single table column can have its own collation, so maybe I should set up all my string columns to Latin1_CI_AI. But I don't know how to do this. I have only SQL access to the database (unfortunately no SQL Server Management Studio).

推荐答案

更改数据库的排序规则

ALTER DATABASE MyDataBase COLLATE [NewCollation]

更改列的排序规则

ALTER TABLE MyTable ALTER COLUMN Column1 [TYPE] COLLATE [NewCollation]

但是何时可以执行此操作有很多限制，特别是这被拒绝了

在某些情况下，您可以在SSMS中执行更多操作。

语法文档列出了限制：

But there are a number of limitations on when you can do this, very notably that this is denied if the column is used in any index.
You can do more in SSMS in some cases.
The syntax docs list the restrictions:

• https://docs.microsoft.com/zh-cn/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2017


• https://docs.microsoft.com/zh-cn/sql/t-sql/statements/collat​​ions?view=sql-server-2017

• https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2017
• https://docs.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-2017

这篇关于如何使用SQL设置列的排序规则？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！