0

What does this statement mean?

SELECT ... COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]

It is part of a script I'm trying to adapt for listing out the SQL Server logins in an instance to be migrated into another instance. Here is the whole code:

 SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
               CASE 
                    WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 
                        + CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
                    ELSE ' FROM WINDOWS WITH'
                END 
       +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP 
LEFT JOIN sys.sql_logins AS SL ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
        AND SP.name NOT LIKE '##%##'
        AND SP.name NOT LIKE 'NT AUTHORITY%'
        AND SP.name NOT LIKE 'NT SERVICE%'
        AND SP.name <> ('sa')
        AND SP.name <> 'distributor_admin'
2

The AS [-- Logins To Be Created --] is merely a column alias for the result set since what is being selected is an expression, not a column. Expressions do not have a column name by default, so this is a way of labeling that result set column for cleaner output.

If you execute that query you will see that the single column in the result set is labeled:
-- Logins To Be Created --.

This has nothing to do with collation in any way. The COLLATE SQL_Latin1_General_CP1_CI_AS is potentially extraneous and unnecessary. If I remove it (from the query posted in the question) the query works just the same. However, in some cases (for other queries, not this one) a COLLATE clause/keyword is needed to explicitly force a common collation across all columns being concatenated (which is happening in this particular expression) if there are multiple columns and not all columns being used in the concatenation (or expression in general) use the exact same collation. In that case, without the COLLATE {any_collation_should_work} then the query would get a collation conflict error.

0

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.