Collation, Unicode & Joins

  • Sean Pearce

    SSCoach

    Points: 15750

    Comments posted to this topic are about the item Collation, Unicode & Joins

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • robertjtjones

    SSC-Addicted

    Points: 484

    Good question - although it does ask for the "number of rows or the error" technically the number of rows returned in the result set is 1 🙂

  • Heals

    SSCrazy

    Points: 2234

    When I ran this - I got 1 row with a value of 10 which threw me a bit - although the query returns a count of the total rows, it still only actually returns one row as the result set. Tom-ay-to / Tom-ar-to I suppose 🙂

  • Sean Pearce

    SSCoach

    Points: 15750

    robertjtjones (7/23/2014)


    Good question - although it does ask for the "number of rows or the error" technically the number of rows returned in the result set is 1 🙂

    Well I am a little bit upset because that was never part of my question. The sentence in brackets was added by an editor of SQLServerCentral.

    The submitted question asked "What will the following query return?".

    I have mailed the webmaster requesting the editors change be removed.

    -- EDIT --

    Thanks Ed 😉

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • This was removed by the editor as SPAM

  • TomThomson

    SSC Guru

    Points: 104773

    Good question (but a pity about the unwanted editing).

    I almost fell for the collation error on skim reading the code, but on looking mor carefully because that seemed too easy I realised that was wrong because integers don't have a collation. So then it was just a matter of remembering what the first 4 databases are.

    Tom

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    TomThomson (7/23/2014)


    ......realised that was wrong because integers don't have a collation. So then it was just a matter of remembering what the first 4 databases are.

    +1 (had same thought)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Thank you Sean, for the post, interesting one.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Toreador

    SSChampion

    Points: 11256

    TomThomson (7/23/2014)


    So then it was just a matter of remembering what the first 4 databases are.

    That's why I don't like the question. It came down to a test of whether you know what the database ids of the system databases are - and I don't. Is it even documented anywhere? For all I knew they could be negative integers, or start at 0.

    Since I didn't know what the UNICODE function did, I guessed that the join was somehow doing some sort of UNION, and that the database IDs started at 0, hence 10 rows. So got the right answer using faulty reasoning 🙂

  • SqlMel

    SSCrazy

    Points: 2891

    Learned something today.

    Thanks!

    ---------------
    Mel. 😎

  • stephen.long.1

    SSCrazy

    Points: 2577

    Excellent question, Sean, thanks! I had to do some digging on the differences in the collation styles and the names of the first four databases (although I should have known those). I initially dismissed 10, thinking that it implied some sort of (AA),(AB,BB),(AC,BC,CC),(AD,BD,CD,DD) combination that you would get from a triangular join (d1.database_id<=d2.database_id), which was not present in the join conditions. However, once I realized that the join criteria is combining 3 m's with 3 m's and 1 t with 1 t, then 3x3+1x1=10 makes sense.

    Of course, you would never see this type of query in the "real world" (or at least I hope not:hehe:), but this question made me think and do some research, which is very good. Gold star for Sean!:-D

  • Sean Pearce

    SSCoach

    Points: 15750

    stephen.long.1 (7/23/2014)


    Excellent question, Sean, thanks! I had to do some digging on the differences in the collation styles and the names of the first four databases (although I should have known those). I initially dismissed 10, thinking that it implied some sort of (AA),(AB,BB),(AC,BC,CC),(AD,BD,CD,DD) combination that you would get from a triangular join (d1.database_id<=d2.database_id), which was not present in the join conditions. However, once I realized that the join criteria is combining 3 m's with 3 m's and 1 t with 1 t, then 3x3+1x1=10 makes sense.

    Of course, you would never see this type of query in the "real world" (or at least I hope not:hehe:), but this question made me think and do some research, which is very good. Gold star for Sean!:-D

    Thank you 🙂

    I enjoyed writing that one and am glad it is well received.

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Question is good, but why would someone use a join with different collations?

    Igor Micev,
    My blog: www.igormicev.com

  • Sean Pearce

    SSCoach

    Points: 15750

    Igor Micev (7/23/2014)


    Question is good, but why would someone use a join with different collations?

    Simply to test ones knowledge of string comparison in a fun question, nothing more.

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Sean Pearce

    SSCoach

    Points: 15750

    Toreador (7/23/2014)


    TomThomson (7/23/2014)


    So then it was just a matter of remembering what the first 4 databases are.

    That's why I don't like the question. It came down to a test of whether you know what the database ids of the system databases are - and I don't. Is it even documented anywhere? For all I knew they could be negative integers, or start at 0.

    Some Documentation

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

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

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