what are the rules for using COLLATE?

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
2
down vote

favorite












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(),
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


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






sql-server sql-server-2016 permissions scripting collation







share|improve this question





















  • 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.)
    – Jeroen Mostert
    1 hour ago

















up vote
2
down vote

favorite












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(),
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


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






sql-server sql-server-2016 permissions scripting collation







share|improve this question





















  • 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.)
    – Jeroen Mostert
    1 hour ago













up vote
2
down vote

favorite









up vote
2
down vote

favorite











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(),
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


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






sql-server sql-server-2016 permissions scripting collation







share|improve this question













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(),
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


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






sql-server sql-server-2016 permissions scripting collation




sql-server sql-server-2016 permissions scripting collation






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked 2 hours ago









marcello miorelli

5,2031659123




5,2031659123











  • 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.)
    – Jeroen Mostert
    1 hour ago

















  • 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.)
    – Jeroen Mostert
    1 hour ago
















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.)
– Jeroen Mostert
1 hour ago





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.)
– Jeroen Mostert
1 hour ago











1 Answer
1






active

oldest

votes

















up vote
4
down vote



accepted










COLLATE operates per predicate. So, 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.



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 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 more columns that can potentially have mixed Collations. You shouldn't need to worry about single columns and string literals as the literals will be coerced into the Collation of the column.



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.





share|improve this answer






















    Your Answer







    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "182"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    convertImagesToLinks: false,
    noModals: false,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













     

    draft saved


    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218824%2fwhat-are-the-rules-for-using-collate%23new-answer', 'question_page');

    );

    Post as a guest

















    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    4
    down vote



    accepted










    COLLATE operates per predicate. So, 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.



    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 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 more columns that can potentially have mixed Collations. You shouldn't need to worry about single columns and string literals as the literals will be coerced into the Collation of the column.



    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.





    share|improve this answer


























      up vote
      4
      down vote



      accepted










      COLLATE operates per predicate. So, 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.



      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 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 more columns that can potentially have mixed Collations. You shouldn't need to worry about single columns and string literals as the literals will be coerced into the Collation of the column.



      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.





      share|improve this answer
























        up vote
        4
        down vote



        accepted







        up vote
        4
        down vote



        accepted






        COLLATE operates per predicate. So, 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.



        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 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 more columns that can potentially have mixed Collations. You shouldn't need to worry about single columns and string literals as the literals will be coerced into the Collation of the column.



        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.





        share|improve this answer














        COLLATE operates per predicate. So, 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.



        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 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 more columns that can potentially have mixed Collations. You shouldn't need to worry about single columns and string literals as the literals will be coerced into the Collation of the column.



        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.






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited 1 hour ago

























        answered 2 hours ago









        Solomon Rutzky

        46k476162




        46k476162



























             

            draft saved


            draft discarded















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218824%2fwhat-are-the-rules-for-using-collate%23new-answer', 'question_page');

            );

            Post as a guest
































































            Comments

            Post a Comment

            Popular posts from this blog

            What does second last employer means? [closed]

            Image
            Clash Royale CLAN TAG #URR8PPP .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0; up vote -3 down vote favorite Say, for example I have worked in the following companies, Company A => 2010 - 2011 Company B => 2011 - 2012 Company C => 2012 - Till Date Company D => Have offer Now assume Company D is asking to submit documents from my second last employer. So from the list of companies given, which company would be qualified as second last employer? software-industry employer share | improve this question asked Aug 10 '14 at 5:16 Rajaprabhu Aravindasamy 559 1 6 13 closed as off-topic by Jim G., jmort253 ♦ Aug 11 '14 at 1:58 This question appears to be off-topic. The users who voted to close gave this specific reason: "Questions seeking advice on company-specific regulations, agreements, or policies should be directed to your manager or HR department. Questions that address...
            Read more

            Installing NextGIS Connect into QGIS 3?

            Image
            Clash Royale CLAN TAG #URR8PPP .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0; up vote 1 down vote favorite Just acquired free version of NEXTGIS and trying to install the plugin for QGIS. It tells me it was not designed for version 3 of QGIS. How do I set up the QGIS-NEXTGIS interface then? qgis qgis-plugins nextgis share | improve this question edited 3 hours ago PolyGeo ♦ 51.9k 17 77 233 asked 4 hours ago IanGS 16 4 New contributor IanGS is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. add a comment  |  up vote 1 down vote favorite Just acquired free version of NEXTGIS and trying to install the plugin for QGIS. It tells me it was not designed for version 3 of QGIS. How do I set up the QGIS-NEXTGIS interface then? qgis qgis-plugin...
            Read more

            One-line joke

            A one-liner is a joke that is delivered in a single line. A good one-liner is said to be pithy - concise and meaningful. [1] Comedians and actors use this comedic method as part of their act, e.g. Jimmy Carr, Tommy Cooper, Rodney Dangerfield, Norm Macdonald, Ken Dodd, Stewart Francis, Zach Galifianakis, Mitch Hedberg, Anthony Jeselnik, Milton Jones, Shappi Khorsandi, Jay London, Mark Linn-Baker, Demetri Martin, Groucho Marx, Dan Mintz, Emo Philips, Tim Vine, Steven Wright and Henny Youngman and Arnold Schwarzenegger. [ citation needed ] Many fictional characters are also known to deliver one-liners, including James Bond, who usually includes pithy and laconic quips after disposing of a villain. [ citation needed ] Examples "A baby seal walks into a club." "A dyslexic man walks into a bra." (Anonymous) "There are three types of people, those who can count and those who can't." "Venison's dear, isn't it?" (Jimmy Carr) "An escala...
            Read more