Database Collation

  • Thomas Abraham

    SSChampion

    Points: 10761

    cengland0 (9/12/2011)


    I got it right but for the wrong reason. The reason specified was for case sensitivity but I thought of other reasons it wouldn't run. The question said "always" but it wouldn't run under these situations either:

    * Network issue

    * Hard drive failure

    * Failure to connect to the database

    * Server being restored from backup at the time

    There are many reasons it wouldn't run but once I thought of a single one, it made the statement false.

    "Always" is what did it for me too - what about not having permission to read from that table?

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Ken Wymore

    SSCoach

    Points: 16668

    Nice question. I have only worked with a case sensitive SQL DB once so I don't usually keep collation in mind.

  • TomThomson

    SSC Guru

    Points: 104773

    Thomas Abraham (9/12/2011)


    cengland0 (9/12/2011)


    I got it right but for the wrong reason. The reason specified was for case sensitivity but I thought of other reasons it wouldn't run. The question said "always" but it wouldn't run under these situations either:

    * Network issue

    * Hard drive failure

    * Failure to connect to the database

    * Server being restored from backup at the time

    There are many reasons it wouldn't run but once I thought of a single one, it made the statement false.

    "Always" is what did it for me too - what about not having permission to read from that table?

    Can't happen - you always have permission to read from that view in the database you are currently connected to; I think you may not have permission to see any of the rows (which may be what you intended?) so it may run and return an empty record set.

    Tom

  • mtassin

    SSC-Insane

    Points: 23099

    Tom.Thomson (9/11/2011)


    Good easy question.

    I got it wrong because I am not case-sensitive when scanning SQL :blush::laugh:

    Ditto... at least this time when I got it wrong, I understood why I got it wrong and just forehead slapped for not reading more carefully.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • sknox

    SSChampion

    Points: 12296

    Sudhir Dwivedi (9/12/2011)


    @Richard Warr:

    45% incorrect answers would imply that it wasn't an easy question! Well done to those who knew the correct answer or, like me, guessed there would be a catch somewhere.

    Yes, I am surprised too. As the title -"Database Collation" itself is a big hint.

    Note to self: Start reading question titles!

    I, too, got it right but for the wrong reason.

  • Thomas Abraham

    SSChampion

    Points: 10761

    Tom.Thomson (9/12/2011)


    Thomas Abraham (9/12/2011)


    "Always" is what did it for me too - what about not having permission to read from that table?

    Can't happen - you always have permission to read from that view in the database you are currently connected to; I think you may not have permission to see any of the rows (which may be what you intended?) so it may run and return an empty record set.

    That was what I intended. So does that mean there is no way to prevent users from reading from that table?

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • cengland0

    SSCertifiable

    Points: 6102

    Thomas Abraham (9/12/2011)


    That was what I intended. So does that mean there is no way to prevent users from reading from that table?

    Remove their permissions from the whole server. If they cannot sign in, they cannot read that table.

  • SanjayAttray

    SSChampion

    Points: 13157

    Not an easy question. If the category was set to General, I would had selected YES, but when I saw question in Database Collation category, that stumped me. Executed query on some servers, all got result except one which is in Latin_General_Bin.

    Good question to start Monday morning.

    SQL DBA.

  • marklegosz

    SSCrazy

    Points: 2816

    I think those of us who have had the "pleasure" of working with case sensitive collations had a distinct advantage in answering this question.

    But a great question for those not on the know that different collations can really ruin your day/week/life.

    cheers!

  • BarbW

    SSCarpal Tunnel

    Points: 4759

    I didn't get it right; I didn't read the header as part of the question. The code executed in SQL 2005 with the default collation.

  • TomThomson

    SSC Guru

    Points: 104773

    marklegosz (9/12/2011)


    I think those of us who have had the "pleasure" of working with case sensitive collations had a distinct advantage in answering this question.

    But a great question for those not on the know that different collations can really ruin your day/week/life.

    cheers!

    It's eminently possible for different collations to ruin your day even if you avoid case-sensitivity like the plague. One of the most helpful things I've found for learning this is to have installers working from printed scripts who either can't read or don't think it matters if they don't fix the server default collation.

    Tom

  • marklegosz

    SSCrazy

    Points: 2816

    Tom.Thomson (9/12/2011)


    It's eminently possible for different collations to ruin your day even if you avoid case-sensitivity like the plague. One of the most helpful things I've found for learning this is to have installers working from printed scripts who either can't read or don't think it matters if they don't fix the server default collation.

    I hear you on that one. Its even worse when they don't know what collations are, and then you have to explain them and why it matters. I find making them fix their own mistakes is a great way to encourage them to more careful in the future.

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    Nice easy question. Thank you.

    I've got it right because I'm thinking that the sys.sysobjects view is created inside any databases as a duplicate object from the model database. But the creation of any objects, being them system objects or not, will follow the same collation rules from the database.

    [edit] Edited because of errors. Bloody monday. 🙂

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • SQLRNNR

    SSC Guru

    Points: 281252

    Thomas Abraham (9/12/2011)


    paul s-306273 (9/12/2011)


    Thomas Abraham (9/12/2011)


    BTW, can anyone else see a question for October 14, 2011 in their list of "previous" questions?

    Yes - I can see it too!

    If he's going to put up questions a month in advance, the least Steve could do is make the query return that day's winning lottery numbers! :hehe:

    They (the lotto numbers) were posted - but only for a limited time.

    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

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    It's easy question, but i got it wrong.

    Before stating the question, i was thinking why this question tag as "database Collation". After given answer i got my answer.

    Anyway, Good question

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

Viewing 15 posts - 16 through 30 (of 33 total)

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