SQL SERVER: 为临时表定义相同的数据库排序规则

Question

我有一个在SQL Server中的脚本。在这个脚本中，我创建了一个临时表，就像这样：

CREATE TABLE #tmpTbl(ID char(36) NOT NULL, Field1 char(36) NOT NULL, Field2 varchar(50) NOT NULL, Field3 varchar(50))

我想要为整个临时表设置排序规则，我这样做：

CREATE TABLE #tmpTbl(ID char(36) collate Modern_Spanish_CI_AS NOT NULL , Field1 char(36) collate Modern_Spanish_CI_AS NOT NULL , Field2 varchar(50) collate Modern_Spanish_CI_AS NOT NULL , Field3 varchar(50) collate Modern_Spanish_CI_AS)

正如您所见，我为每一列应用排序规则。因此，我尝试做以下事情:

1. 有没有可能一次性对临时表的所有列应用排序规则，而不是对每一列都单独应用排序规则?
2. 在不手动指定执行脚本的数据库具有的Modern_Spanish_CI_AS排序规则的情况下，是否有任何方法通过变量从数据库获取排序规则，然后将其设置为临时列?

例如，在最后一种情况下:

DECLARE @Collation = getdatabasecollation  --> Get current database collation where script is executed.

然后：

CREATE TABLE #tmpTbl(ID char(36) collate @Collation NOT NULL , Field1 char(36) collate @Collation NOT NULL, Field2 varchar(50) collate @Collation NOT NULL , Field3 varchar(50) collate @Collation)

如我在第一点中所述，将这个排序设置为整个临时表。

- Willy

---

如果您不使用ASCII类型，则无需设置排序规则。 - Panagiotis Kanavos

@PanagiotisKanavos：对于所有字符列，排序规则都是相关的，而不管它们实际包含的字符是什么。将具有不同排序规则的列组合的表达式将导致错误。您不能选择“不设置排序规则”；如果您不这样做，则将使用数据库默认值。只要所有数据库使用相同的排序规则，这将起作用，这是常见但绝非普遍的。 - Jeroen Mostert

@JeroenMostert 相关性是肯定的，但问题并不大。使用ASCII时，如果排序规则不正确，甚至无法进行连接或搜索。这是一个严重的问题。而使用nvarchar，在极少数情况下，当从多个数据库中连接表时，可能会错过索引。但查询仍将正常工作。而我确实在非美国系统上工作。 - Panagiotis Kanavos

我不确定你所说的“ASCII”是什么意思。如果你指的是CHAR/VARCHAR，那么是的，它们支持的字符受到排序规则的限制，而NCHAR/NVARCHAR则没有。无论使用哪种字符类型，排序规则仍会影响比较，并且在混合不同排序规则时仍会导致错误（即使这些排序规则在支持的字符方面是兼容的）。 - Jeroen Mostert

在特定情况下，如果 SQL Server 安装的默认排序规则不是 Modern_Spanish_CI_AS，并且创建了一个使用 Modern_Spanish_CI_AS 的用户数据库，则 tempdb 将具有不兼容的排序规则。即使您始终使用 NCHAR/NVARCHAR，当连接表时也需要解决此处所述的问题。 - Jeroen Mostert

Answer 1

只需使用COLLATE DATABASE_DEFAULT

CREATE TABLE #tmpTbl
  (
     ID     CHAR(36) COLLATE DATABASE_DEFAULT NOT NULL,
     Field1 CHAR(36) COLLATE DATABASE_DEFAULT NOT NULL,
     Field2 VARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL,
     Field3 VARCHAR(50) COLLATE DATABASE_DEFAULT NULL
  ) 

- Martin Smith

---

但是如果我的数据库的排序规则是Modern_Spanish_CI_AS，那么DATABASE_DEFAULT会返回Modern_Spanish_CI_AS吗？如果将来我将数据库排序规则更改为另一个排序规则，比如Latin1_General_CI_AS，那么DATABASE_DEFAULT是否会返回新的Latin1_General_CI_AS呢？ - Willy

@user1624552 是的。您还可以在COLLATE子句中使用database_default选项，以指定临时表中的列使用当前用户数据库连接的排序规则默认值，而不是tempdb。 - Martin Smith

当然，如果您创建了临时表，然后更改默认数据库排序规则，则已存在的临时表不会更改排序规则。它适用于表创建时间。 - Martin Smith

非常感谢Martin，我在创建临时表列时将COLLATE DATABASE_DEFAULT设置为所有列，现在它可以完美运行了！ - Willy