T-SQL

  • Comments posted to this topic are about the item T-SQL

  • good question.

    Alex S
  • 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.

  • 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

  • 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

  • This was removed by the editor as SPAM

  • 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]

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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.

  • Thanks. I needed an easy one to start me off today.

  • 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.

  • 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.

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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.

  • 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