September 8, 2012 at 9:32 pm
good question.
September 9, 2012 at 11:35 pm
Simply Nice. 🙂
(as the either way the implicit conversion is allowed, it will work if the data in the col is numbers, but one of the row contains the chars so the real char cannot be converted in to number, if the data is number but sitting inside the varchar type then implicit conversion takes care of itself)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
September 10, 2012 at 2:05 am
Easy monday question, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 10, 2012 at 2:33 am
Easy QOTD to start the week - thanks
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
September 10, 2012 at 2:56 am
This was removed by the editor as SPAM
September 10, 2012 at 3:08 am
I do like these easy questions to start the week 😀
Good question, thanks.
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]
September 10, 2012 at 3:42 am
Good question - though I am waiting for the first person to claim that "error, error" should also be considered correct because they executed the code on a server or in a database with a case-sensitive collation.
Too bad that the explanation does not contain a link to the Books Online page that explains why this query fails - after all, from a performance perspective it might make more sense to convert the numeric value to varchar (especially if the search column is also indexed) rather than perform all the varchar values in the table. The reason that a slower approach is chosen is documented here: http://msdn.microsoft.com/en-us/library/ms190309.aspx.
September 10, 2012 at 3:55 am
Thanks - definitely something that most developers should have come across (many times!) in the course of their career but also captures something important about implicit conversion in SQL Server.
September 10, 2012 at 4:13 am
Thanks. I needed an easy one to start me off today.
September 10, 2012 at 4:47 am
Hugo Kornelis (9/10/2012)
Too bad that the explanation does not contain a link to the Books Online page that explains why this query fails - after all, from a performance perspective it might make more sense to convert the numeric value to varchar (especially if the search column is also indexed) rather than perform all the varchar values in the table. The reason that a slower approach is chosen is documented here: http://msdn.microsoft.com/en-us/library/ms190309.aspx.
Thank you Hugo, I flipped over to the discussion for any BOL pointers for the WHY on this behaviour.
September 10, 2012 at 5:03 am
Hugo Kornelis (9/10/2012)
Good question - though I am waiting for the first person to claim that "error, error" should also be considered correct because they executed the code on a server or in a database with a case-sensitive collation....
Hello Hugo, like how you mean? If the collation was CI then would the result be the 'E, E'? (to my knowledge the first select statement always returns valid row), and if the column collation is also applied to that table it still wouldn't make any difference.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
September 10, 2012 at 5:16 am
Raghavendra Mudugal (9/10/2012)
Hugo Kornelis (9/10/2012)
Good question - though I am waiting for the first person to claim that "error, error" should also be considered correct because they executed the code on a server or in a database with a case-sensitive collation....Hello Hugo, like how you mean? If the collation was CI then would the result be the 'E, E'? (to my knowledge the first select statement always returns valid row), and if the column collation is also applied to that table it still wouldn't make any difference.
With a case sensitive collation (CS), you'd get errors on both SELECT queries. In the CREATE TABLE and INSERT statement, the table name is Zip_code (uppercase Z). In the SELECT queries, that has changed to zip_code (lowercase z). In a case sensitive collation, those would be different tables.
September 10, 2012 at 5:34 am
Hugo Kornelis (9/10/2012)
Raghavendra Mudugal (9/10/2012)
Hugo Kornelis (9/10/2012)
Good question - though I am waiting for the first person to claim that "error, error" should also be considered correct because they executed the code on a server or in a database with a case-sensitive collation....Hello Hugo, like how you mean? If the collation was CI then would the result be the 'E, E'? (to my knowledge the first select statement always returns valid row), and if the column collation is also applied to that table it still wouldn't make any difference.
With a case sensitive collation (CS), you'd get errors on both SELECT queries. In the CREATE TABLE and INSERT statement, the table name is Zip_code (uppercase Z). In the SELECT queries, that has changed to zip_code (lowercase z). In a case sensitive collation, those would be different tables.
I got it now, you are focusing on the table name here, and i was looking into the output (and was wondering how the collation will really make this happen and consider 'aaaa' as a valid comparison with number? :w00t: )
One good point here, from anywhere I copy the sql code I quickly change then all to upper case, so here for me the table name was also changed into 'ZIP_CODE' which did not made any difference when I created the new DB with CS collation to check this). Point to be noted here: "Keep the original SQL code as it is."
Thank you, Hugo.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
September 10, 2012 at 7:39 am
Good Start for Monday...:)
Best,
Naseer Ahmad
SQL Server DBA
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply