英文:
SQL Management Studio 19: what determines if action Script SP appends "collate database_default" or not?
问题
我注意到对于一些我使用SQL Management Studio Version 19访问的数据库，即使这些数据库具有相同的默认排序设置（这里是："Latin1_General_CI_AS"），通过将存储过程脚本生成到新窗口的输出列表会发出用于该存储过程的临时表的定义，其中一个数据库上使用了"collate database_default"，而另一个数据库上没有使用。
即使原始的创建存储过程没有在主体中声明的临时表上定义排序规则，上述操作也会添加它（或不添加，取决于我不知道的某种条件）。
示例：
如果原始存储过程包含：
create table #tmp_flatfile
(
PersonalNr varchar(30) null,
)
然后运行脚本存储过程到新窗口的结果行将如下所示：
create table #tmp_flatfile
(
PersonalNr varchar(30) collate database_default null,
)
但有时它们不会包含"collate database_default"。
我不知道是什么决定了这种行为。
另一个问题是为什么首先添加这个，因为它只定义所使用的排序规则是默认的。
英文:
I noticed that for some databases that I access with SQL Management Studio Version 19, even if those databases have the same default collation set (here: "Latin1_General_CI_AS") the output listing generated by Script Stored Procedure to a New Window emits defintions of temporaty tables that are used by that SP with "collate database_default" on one database and not on the other database.
Even if the original create procedure didn't define collation on the temp tables declared in the body, the action mentioned above adds it (or not, depending on some unknown by my condition).
Example:
If the original SP contains:
create table #tmp_flatfile
(
PersonalNr varchar(30) null,
)
Then the lines resulting from running Script SP to a New Window will look like this:
create table #tmp_flatfile
(
PersonalNr varchar(30) collate database_default null,
)
But sometimes they will not contain "collate database_default".
I have no idea what determines this behaviour.
Other question is why this is added in the first place, as it only defines that collation used is the default one.
答案1
得分: 1
暂时表格是在TempDB中生成的，而不是存储过程所在的数据库。TempDB的排序规则与服务器的排序规则相同。
如果您的数据库和TempDB的排序规则不同，您将无法在不在JOIN子句中使用COLLATE来强制排序规则的情况下与临时表格JOIN。如果这样做，您将无法使用任何覆盖JOIN列的索引。
如果您使用ASCII类型，比如varchar，您可能会遇到数据混乱或丢失的情况，因为非USASCII文本在不同的代码页之间进行转码。
这可能听起来是一个不寻常的情况，但即使是Latin1_General_100_CI_AI和Latin1_General_CI_AI也是两种不同的排序规则。如果您将Latin1_General_CI_AI的备份还原到Latin1_General_100_CI_AI的服务器上，您会发现JOIN可能会失败。
这是我艰难地发现的事情。
如果没有collate database_default，您将有两个非常痛苦的选择：
- 将数据库中的所有列的排序规则更改为与服务器匹配，或者更糟糕的是，
- 通过重建系统数据库来更改服务器的排序规则。然后，您还必须更改所有其他数据库的排序规则。
英文:
Temporary tables are generated in TempDB, not the database where the stored procedure is running. TempDB's collation is the same as the server's.
If the collation of your database and TempDB are different, you won't be able to JOIN with a temporary table at all without forcing the collation with COLLATE in the JOIN clause. If you do that, you won't be able to use any indexes that cover the JOIN columns.
If you use ASCII types like varchar you can end up with mangled or lost data too, as non-USASCII text gets transcoded between the different codepages.
This may sound as an unusual case, but even Latin1_General_100_CI_AI and Latin1_General_CI_AI are two different collations. If you restore a Latin1_General_CI_AI backup to a Latin1_General_100_CI_AI server you'll find that JOINs may fail.
That's something I found the hard way.
Without collate database_default you'd have two very painful options:
- Change the collation of all columns in your database to match the server or worse,
- Change the server's collation by rebuilding the system databases. And then you'd have to change the collation of all other databases too.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论