IsNumeric

  • gauravjha57

    Valued Member

    Points: 66

    Comments posted to this topic are about the item IsNumeric

  • Hany Helmy

    SSChampion

    Points: 13436

    Eazy 🙂

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Easy one. We didn't have to explain why though 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This was removed by the editor as SPAM

  • Ed Wagner

    SSC Guru

    Points: 286969

    Stewart "Arturius" Campbell (1/5/2015)


    Simple one to start the week with, thanks Gaurav

    Yep, a simple one to kick-start the brain after some time off.

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Thank you for the post. Even though knowing the result, mind still looks for tricks.

    (I had an item in my sql to-do list; "revisit cast and convert local_help page" from more than 3 weeks... and today I strikethrough'ed it.) 🙂

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Easy one, thanks.

  • Andre Ranieri

    SSCrazy

    Points: 2819

    +1 - no pun intended.

  • SQLRNNR

    SSC Guru

    Points: 281243

    To be pedantic, none of the answers are correct.

    The question specifically states

    What will return the following statements and why ?

    Even if you ignore the second half the required response (the why of the question), none of the answers return those two statements.

    Should the question be asking

    what will be returned by the following two statements?

    Then we might be talking about a potential correct answer.

    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

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    A nice and easy question, but the explanation is not fully correct.

    Allthough Books Online does not explicitly document this (or I overlooked it), ISNUMERIC is a function that expects a string input parameter. In the second query, the actual input parameter is an integer constant. So what SQL Server will do is first implicitly convert the integer to a character expression, and then run that through ISNUMERIC.

    To see this, run this fragment with the actual execution plan enabled:

    CREATE TABLE tab1 (col1 int);

    SELECT ISNUMERIC(col1) FROM tab1;

    DROP TABLE tab1;

    In the execution plan, bring up the properties of the compute scalar operator and check the "Defined Values" attribute. On my test box, it reads:

    [Expr1004] = Scalar Operator(isnumeric(CONVERT_IMPLICIT(varchar(12),[AdventureWorks2012].[dbo].[tab1].[col1],0)))

    So the value in col1 is first implicitly converted to varchar(12), before being fed to the isnumeric function.

    (Note: With the query as given, this cannot be verified. No actual execution plan will be given for SELECT ISNUMERIC(123), and an estimated execution plan shows a "SELECT WITHOUT QUERY" icon, which means that the result was precomputed during compilation. Similarly, a query such as SELECT ISNUMERIC(123) FROM tab1 will result in a plan where the ISNUMERIC(123) expression is pre-computed - you will see a Compute Scalar operator that assigns the constant 1 to the internal placeholder Expr1004).


    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/

  • manik_anu

    SSCrazy

    Points: 2367

    Allthough Books Online does not explicitly document this (or I overlooked it), ISNUMERIC is a function that expects a string input parameter. In the second query, the actual input parameter is an integer constant. So what SQL Server will do is first implicitly convert the integer to a character expression, and then run that through ISNUMERIC.

    Nice explanation.. thanks

    Manik
    You cannot get to the top by sitting on your bottom.

Viewing 11 posts - 1 through 11 (of 11 total)

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