SQL 在临时表列 TSQL 中设置排序规则的正确方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23504023/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Correct way to set collation in temporary table column TSQL
提问by J3FFK
I have a temporary table which gets data inserted using bulk insert. However, when I want to update data from temp table to a normal table it gives collation problems. I know how to solve this by using something like:
我有一个临时表，它使用批量插入来插入数据。但是，当我想将临时表中的数据更新到普通表时，它会出现整理问题。我知道如何使用以下方法解决这个问题：
UPDATE RegularTable
SET r.Column1 = t.ColumnA
FROM RegularTable r INNER JOIN #TEMP t ON
r.Column1 COLLATE DATABASE_DEFAULT =
t.ColumnA COLLATE DATABASE_DEFAULT
But, is there a way to set the collation in the temporary table immediately so you don't have to use collate in the join? Something like:
但是，有没有办法立即在临时表中设置排序规则，这样您就不必在连接中使用排序规则？就像是：
CREATE TABLE #TEMP
Column1 varchar(255) COLLATE database_default,
Column2 varchar(60)
Is this correct coding and do you have to set the collation once per table or per column? And if the collation is set in the table, can you exclude the collate from the join then?
这是正确的编码吗，您是否必须为每个表或每列设置一次排序规则？如果在表中设置了排序规则，那么您可以从连接中排除排序规则吗？
回答by Christian Hayter
You can use COLLATE database_defaultin the temp table definition using the syntax you describe, and that will make each column collation-compatible with your database.
您可以使用COLLATE database_default您描述的语法在临时表定义中使用，这将使每列排序规则与您的数据库兼容。
You have to set it explicitly per column. There is no table-level default collation. There is a database-level default collation, but for tempdbthis is always equal to the default collation of the modeldatabase, which by default is the server collation.
您必须为每列明确设置它。没有表级默认排序规则。有一个数据库级别的默认排序规则，但tempdb它始终等于model数据库的默认排序规则，默认情况下是服务器排序规则。
If you set the collation on the table column, you can still override it in a query, as you have already experienced.
如果您在表列上设置排序规则，您仍然可以在查询中覆盖它，就像您已经经历过的那样。
回答by IngoB
We ran into the same problem right now. Instead of adding the collation to each temp table join, we just changed the temp table creation to a table variable declaration.
我们现在遇到了同样的问题。我们没有将排序规则添加到每个临时表连接，而是将临时表创建更改为表变量声明。
