Collation, Unicode & Joins
-
Comments posted to this topic are about the item Collation, Unicode & Joins
-
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 🙂
-
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 🙂
-
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 😉
-
This was removed by the editor as SPAM
-
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
-
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. -
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. -
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 🙂
-
Learned something today.
Thanks!
---------------
Mel. 😎 -
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
-
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.
-
Question is good, but why would someone use a join with different collations?
Igor Micev,My blog: www.igormicev.com
-
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.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply