January 4, 2015 at 1:04 am
Eazy 🙂
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
January 4, 2015 at 9:53 am
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
January 5, 2015 at 3:23 am
This was removed by the editor as SPAM
January 5, 2015 at 5:36 am
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.
January 5, 2015 at 5:43 am
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.
January 5, 2015 at 7:56 am
Easy one, thanks.
January 5, 2015 at 4:01 pm
+1 - no pun intended.
January 5, 2015 at 5:01 pm
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
January 6, 2015 at 12:45 am
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).
January 6, 2015 at 6:10 am
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply