在查詢中使用 COLLATE 的規則是什麼？

我正在將一個腳本放在一起，它為我提供了對數據庫對象的權限。

SELECT  permission_order=710
      ,permission_type = 'Object Level Permissions'
      ,db = db_name(),
login_=null,
role_=dp.name collate Latin1_General_CI_AS,
Obj = sys.schemas.name + '.' + so.name collate Latin1_General_CI_AS,
Permission = permission_name collate Latin1_General_CI_AS,
[script]=
'IF OBJECT_ID ('  + '''' + '['+ sys.schemas.name + '].[' + so.name + ']'  + '''' + ') IS NOT NULL ' + CHAR(10) + state_desc + 
' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + so.name + '] to [' + dp.name + ']' collate Latin1_General_CI_AS + CHAR(10) +
'ELSE ' + CHAR(10) +
'print ' + '''['+ sys.schemas.name + '].[' + so.name + '] - does not exist'''
+ CHAR(13)

from sys.database_permissions a
INNER JOIN  sys.objects so on a.major_id = so.object_id
INNER JOIN sys.schemas on so.schema_id = sys.schemas.schema_id
INNER JOIN sys.database_principals dp on a.grantee_principal_id = dp.principal_id
WHERE dp.name NOT IN ( 'public', 'guest')
AND a.class = 1

我想collate盡可能少地使用這個詞，並且仍然有一個為多數據庫、多排序伺服器執行的腳本。

我怎樣才能做到這一點？申請規則是什麼collate？

COLLATE根據上下文，按謂詞或按表達式操作。COLLATE主要用於控制如何比較或排序字元串值。因此，它最常用於JOIN、WHERE和HAVING謂詞以及GROUP BY和ORDER BY子句（在這種情況下，它可以按列/表達式使用）。它需要應用於可能具有不同排序規則的字元串列。非字元串類型（包括 XML）不使用COLLATE，並且保證具有相同排序規則的列不需要它。對於JOIN, WHERE, 和HAVING謂詞，它只需要在運算符的一側指定，因為另一側將被強制轉換為指定的 Collat​​ion。

它通常不會在SELECT列表中使用，除非有理由更改所選列/表達式的排序規則，這主要適用於VARCHAR數據，因為您可以更改程式碼頁，但我真的看不到太多應用程序這種用法。這樣做的最可能原因是請求數據的客戶端需要不同的程式碼頁，但我想不出需要這樣做的原因。

但是，在SELECT列表（可能還有其他地方）中使用它的一個原因是在進行涉及可能具有混合排序規則的兩個或多個列的字元串連接時。這種情況將適用於VARCHAR和NVARCHAR數據。您不必擔心單列和字元串文字，因為文字將被強制轉換為列的排序規則。

您可能需要COLLATE在SELECT列表中使用的另一個原因是，當使用UNION,INTERSECT或時EXCEPT，至少兩個查詢之間相同位置的列/表達式之間的排序規則不同。

因此，查看您的查詢：

1. COLLATE這些地方

不需要：

• role_=dp.name collate Latin1_General_CI_AS,

因為您只是選擇一列，所以沒有混合任何內容。

• Obj = sys.schemas.name + '.' + so.name collate Latin1_General_CI_AS,

因為兩列都是來自同一個數據庫的數據庫級元數據，保證是相同的排序規則，加上一個已經是數據庫預設排序規則的字元串文字（與它連接的兩列相同），但即使它已經不一樣了，它將被強制轉換為兩列的排序規則。這裡唯一的改進是用大寫作為文字前綴，N因為表達式是NVARCHAR由於模式名稱和對象名稱的類型sysname是NVARCHAR(128).

• Permission = permission_name collate Latin1_General_CI_AS,

因為您只是選擇一列，所以沒有混合任何內容。 2. 您確實需要COLLATE字元串連接，因為您正在混合數據庫級元數據（使用數據庫的預設排序規則）和系統級元數據（即state_desc和permission_name），它們來自隱藏mssqlsystemresource數據庫並且通常具有Latin1_General_CI_AS_KS_WS. COLLATE每個表達式只指定一次應該沒問題，因為它應該具有最高優先級並強製文字和列進入規定的排序規則。

最後，關於指定哪個排序規則：DATABASE_DEFAULT，CATALOG_DEFAULT或您選擇的一個（例如您在此處所做的）：假設您正在處理NVARCHAR數據（沒有機會更改程式碼頁/字元集）並且不COLLATE用於排序或比較（沒有機會改變查詢在不同數據庫之間的工作方式），這真的沒關係；在這種特殊情況下都是一樣的。如果您需要查詢（或該特定謂詞或/項目）對本地數據庫敏感並根據執行查詢的位置更改行為，您將使用DATABASE_DEFAULT或者。CATALOG_DEFAULT``ORDER``GROUP

其他注意事項：

1. 我建議將結果集列名括在方括號中（即，[permission_order]代替permission_order，[role_]代替role_等）。
2. 最後，您CHAR(13)應該CHAR(10)與該串聯中的其他換行符一樣。
3. 更好的做法是使用大寫的連接的每個字元串文字部分NCHAR(10) 並N為其添加前綴，因為您正在連接sysname/NVARCHAR列，這將強制整個字元串變為NVARCHAR，因此這些CHAR()引用和字元串文字無論如何都會被隱式轉換。
4. 同樣好的是將模式/對象名稱包裝在QUOTENAME()（並刪除您的顯式分隔符 -[和]- 在字元串文字中），因為QUOTENAME有轉義嵌入式分隔符的好處。

引用自：https://dba.stackexchange.com/questions/218824