COLLATION

  • deepzzzz

    SSCrazy

    Points: 2890

    Comments posted to this topic are about the item COLLATION

    Thanks

  • BrainDonor

    SSCoach

    Points: 19197

    An interesting question about commands that I was not aware of.

    However, your explanation of fn_helpcollations is not entirely accurate for those of us using 2008. BOL describes it as 'Returns a list of all the collations supported by SQL Server 2008' with a note 'Deprecated collations that are no longer supported or installed in SQL Server 2008 cannot be queried with fn_helpcollations'. I have no idea how the 2005 BOL describes it, but if you don't specify the version you need to be aware of all of the possible variations in the documentation.

    Steve Hall
    Linkedin
    Blog Site

  • cengland0

    SSCertifiable

    Points: 6102

    Curious why it was being converted to Char in the query.

  • vk-kirov

    SSCertifiable

    Points: 7686

    Conversion to CHAR is wrong, because in this case we get a collation name which is truncated to 30 symbols: Chinese_Hong_Kong_Stroke_90_CS instead of Chinese_Hong_Kong_Stroke_90_CS_AS_KS_WS, SQL_Latin1_General_CP1250_CI_A insted of SQL_Latin1_General_CP1250_CI_AS etc.

    The type of a collation name is nvarchar(128) (http://msdn.microsoft.com/en-us/library/ms190305.aspx), so the correct statement should be "SELECT CONVERT(nvarchar(128), ...)".

  • DigitalTeapot

    Valued Member

    Points: 54

    Hi

    The following TSQL statement would also work (SQL Server 2005 and later):

    SELECT collation_name FROM sys.databases WHERE name = N'MyDatabase'

    Regards,

    DigitalTeapot

  • Peter Rijs

    Hall of Fame

    Points: 3380

    I'm also curious for the reason to convert to char, as the following syntax will work just fine for me:

    SELECT DATABASEPROPERTYEX('MyDatabase','collation')

    It is very clear what answer should be picked on this question, since the other 2 answers do not return (a part of) the database collation (though server collation might be, but is not necessarily, the same value as the database collation).

    Peter Rijs
    BI Consultant, The Netherlands

  • SanDroid

    SSChampion

    Points: 10068

    Nice question.

    I learned something, there were no errors or typos with the answer, and the reference data supported the documentation.

    The only negative comment I have is that this code:

    SELECT CONVERT(char, SERVERPROPERTY('collation'),'MyDatabaseName')

    Returned an error.

  • cengland0

    SSCertifiable

    Points: 6102

    SanDroid (11/3/2010)


    Nice question.

    I learned something, there were no errors or typos with the answer, and the reference data supported the documentation.

    The only negative comment I have is that this code:

    SELECT CONVERT(char, SERVERPROPERTY('collation'),'MyDatabaseName')

    Returned an error.

    It's okay that it returned an error because that wasn't the correct answer. The author of the question probably included that as a distractor in an attempt to get some people to select that answer.

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    Good question. Thanks to VK to pointing out the Char issue (reinforcing what I know.....but frequently forget).

  • Michael Poppers

    SSCrazy

    Points: 2119

    cengland0 (11/3/2010)


    SanDroid (11/3/2010)


    Nice question.

    I learned something, there were no errors or typos with the answer, and the reference data supported the documentation.

    The only negative comment I have is that this code:

    SELECT CONVERT(char, SERVERPROPERTY('collation'),'MyDatabaseName')

    Returned an error.

    It's okay that it returned an error because that wasn't the correct answer. The author of the question probably included that as a distractor in an attempt to get some people to select that answer.

    Yes, he might have been better off listing the possible choice as SELECT CONVERT(char, SERVERPROPERTY('collation','MyDatabaseName')) and perhaps that was his intention :-).

  • cengland0

    SSCertifiable

    Points: 6102

    Michael Poppers (11/3/2010)


    Yes, he might have been better off listing the possible choice as SELECT CONVERT(char, SERVERPROPERTY('collation','MyDatabaseName')) and perhaps that was his intention :-).

    Agreed. I work in a training organization and consider myself a quiz expert. Writing many quiz applications and writing the actual quizzes too. I can say with 100% certainty that it was a bad distractor because anyone that knows how to use the convert command would know that you don't put the Database name as the second parameter. You can then remove that possible answer as one that you should select.

    It could have been offered as

    SELECT SERVERPROPERTY('collation','MyDatabaseName') and just leave off the Convert portion.

  • SanDroid

    SSChampion

    Points: 10068

    Michael Poppers (11/3/2010)


    and perhaps that was his intention :-).

    Good point. It could have been his attention from the start.

    It would not be the first time the QOTD was differant from what the Author thought he had posted.

  • Sean Lange

    SSC Guru

    Points: 286417

    I will jump on the bandwagon of not getting the convert to char but will point out that I never like code that just uses the default size for datatypes. I don't like to have to stop and think (or more likely lookup) what the default size is for a given datatype.

    This is so much easier to read exactly what is being returned:

    SELECT CONVERT(char(30), DATABASEPROPERTYEX('AdevntureWorks', 'Collation'))

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • SQLRNNR

    SSC Guru

    Points: 281210

    Thanks for the question

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Koen Verbeeck

    SSC Guru

    Points: 258924

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply