I am putting a script together, that gives me the permissions on database objects.

SELECT  permission_order=710
       ,permission_type = 'Object Level Permissions'
       ,db = db_name(),
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,
'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

I want to use the word collate as few times as possible and still have a script that runs for multi-databases, multi-collations server.

How can I achieve that? What is the rule for applying collate?

enter image description here

  • 1
    Aside: there's COLLATE DATABASE_DEFAULT to, well, use the default of the current database, which can save on some inappropriate hard-coding. (It probably wouldn't do any good in this particular case, but then, I don't see COLLATE Latin1_General_CI_AS doing that either.) Sep 28 '18 at 14:49

COLLATE operates per predicate or per expression, depending on context. COLLATE is mainly used to control how the string values are being compared or sorted. Hence it is most commonly used in JOIN, WHERE, and HAVING predicates, as well as GROUP BY and ORDER BY clauses (in which case it can be used per column / expression). It needs to be applied to string columns that could possibly have differing Collations. Non-string types (including XML) do not use COLLATE, and columns that are guaranteed to have the same Collation do not need it. For JOIN, WHERE, and HAVING predicates, it only needs to be specified on one side of the operator since the other side will be coerced into the specified Collation.

It generally wouldn't be used in a SELECT list unless there was a reason to change the Collation of the selected column / expression, which would mainly apply to VARCHAR data as you can change the Code Page, but I really don't see much application for this usage. The most likely reason to do this would be if the client requesting the data needs a different Code Page, but I can't think of a reason that this would ever be needed.

However, one reason to use it in the SELECT list (and possibly other places) is when doing string concatenation involving two or more columns that can potentially have mixed Collations. This scenario would apply to both VARCHAR and NVARCHAR data. You shouldn't need to worry about single columns and string literals as the literals will be coerced into the Collation of the column.

Another reason you might need COLLATE in a SELECT list is when using UNION, INTERSECT, or EXCEPT, and the Collation between columns / expressions in the same position between at least two of the queries is not the same.

So, looking at your query:

  1. You don't need COLLATE in these places:
    • role_=dp.name collate Latin1_General_CI_AS,
      because you are just selecting a single column, nothing is being mixed.
    • Obj = sys.schemas.name + '.' + so.name collate Latin1_General_CI_AS,
      because both columns are database-level meta-data from the same database which is guaranteed to be the same Collation, plus a string literal that is already the database's default Collation (same as the two column's it is being concatenated with), but even if it wasn't already the same, it would be coerced into the Collation of the two columns. Only improvement here would be to prefix the literal with an upper-case N since the expression is NVARCHAR due to the schema names and object names being of type sysname which is an alias for NVARCHAR(128).
    • Permission = permission_name collate Latin1_General_CI_AS,
      because you are just selecting a single column, nothing is being mixed.
  2. You do need COLLATE in the string concatenation because you are mixing database-level meta-data (which uses the database's default Collation) and system-level meta-data (i.e. state_desc and permission_name) which comes from the hidden mssqlsystemresource database and usually has a Collation of Latin1_General_CI_AS_KS_WS. It should be fine to have COLLATE specified only once per expression since it should have the highest precedence and force both literals and columns into the stated Collation.

Finally, regarding which Collation to specify: DATABASE_DEFAULT, CATALOG_DEFAULT, or one of your choosing (such as you are doing here): given that you are working on NVARCHAR data (no chance of changing the Code Page / character set) and not using COLLATE for sorting or comparison (no chance of changing how the query works between different databases), it really doesn't matter; it's all the same in this particular scenario. You would use DATABASE_DEFAULT or CATALOG_DEFAULT if you needed the query (or that particular predicate or ORDER / GROUP item) to be sensitive to local database and change behavior depending on where the query is being executed.

Other notes:

  1. I would suggested enclosing the result set column names in square brackets (i.e. [permission_order] instead of permission_order, [role_] instead of role_, etc).
  2. You have CHAR(13) at the end which should be CHAR(10) like the rest of those newlines in that concatenation.
  3. Even better would be to use NCHAR(10) and prefix each string literal piece of the concatenation with an upper-case N since you are concatenating sysname / NVARCHAR columns which will force the entire string into NVARCHAR, hence those CHAR() references and string literals are being implicitly converted anyway.
  4. Also good would be to wrap the schema / object names in QUOTENAME() (and remove your explicit delimiters — [ and ] — in the string literals) as QUOTENAME has the benefit of escaping embedded delimiters.
  • 1
    When you say "per predicate" do you not mean "per expression"?
    – usr
    Sep 28 '18 at 17:19
  • 3
    @usr Hi there. I believe "predicate". But to clarify in case I'm using terminology incorrectly, consider this simple example: SELECT 1 FROM sys.all_objects WHERE [name] = N'objects' COLLATE Latin1_General_100_BIN2. The predicate is both sides of the "x = y", and an expression is just one side, both "x" and "y", each being a separate expression, correct? If yes to both, then yes, per predicate since both sides need to be of the same Collation, so COLLATE will force both sides of the predicate, not just the expression it is directly attached to. Else you would get a Collation mismatch error. Sep 28 '18 at 17:28
  • I would say it's per expression and the collation propagates automatically throughout the query. Collation really is a part of the type system. x COLLATE C1 is a different type than x COLLATE C2. Types in T-SQL are parameterized for example numeric(a, b) but also strings by their length and collation. All T-SQL expressions have a static type (int, bit, ...) even though you don't normally declare it. Types just flow. Like var x = GetString(); in C# where x is string without you saying it.
    – usr
    Sep 28 '18 at 20:11
  • @usr Re: your statement of "it's per expression and the collation propagates automatically throughout the query.": Firstly, collation is not per-query, so it doesn't propagate out that far. It goes from COLLATE -> column -> string literal, all within an expression, then when involved in a predicate, over to the other side of the predicate where it either matches, will coerce if needed and possible, or will error if needed but not possible. Secondly, yes, Collation is, on that level, per expression. BUT, this question is about how to apply the COLLATE keyword, which is different. Sep 28 '18 at 21:46
  • 1
    I think your answer is great :)
    – usr
    Sep 29 '18 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.