February 10, 2015 at 10:04 pm
Comments posted to this topic are about the item What is the "same query"?
February 11, 2015 at 12:06 am
I have checked the rows in sys.syscacheobjects before/after running the selects, found 5 new rows with all the selects mentioned in QOD.
select * from sys.syscacheobjects where objtype ='adhoc' and sql like '%bob%'
I answered 5 but the answer is 4, dont know where should I get the data for adhoc cache objects
Thanks for the intereting question.
February 11, 2015 at 1:30 am
pmadhavapeddi22 (2/11/2015)
I have checked the rows in sys.syscacheobjects before/after running the selects, found 5 new rows with all the selects mentioned in QOD.select * from sys.syscacheobjects where objtype ='adhoc' and sql like '%bob%'
I answered 5 but the answer is 4, dont know where should I get the data for adhoc cache objects
+1
I run this query:
SELECT usecounts, cacheobjtype, objtype, a.*
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) as a
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%'
AND [text] LIKE '%PlanName%'
and objtype='Adhoc'
ORDER BY usecounts DESC;
It returns 5 rows. The bug is in the comment:
SELECT *
FROM Plans
WHERE PlanName = 'Bob'
-----------------------
SELECT *
FROM Plans
WHERE PlanName = 'Bob'
/* Same as first select */
-----------------------
SELECT *
FROM Plans
WHERE PlanName = 'Bob'
-----------------------
select *
from Plans
where PlanName = 'Bob'
-----------------------
SELECT *
FROM Plans
WHERE PlanName = 'Bob'
-----------------------
February 11, 2015 at 4:55 am
Answer is 5, the first statement, there is a space after the *, not in the last.
February 11, 2015 at 6:35 am
Okay, good to know I am not nuts (or at least more so than usual). I didn't run it to check the actual count, but I had thought that extra space mattered...
February 11, 2015 at 6:45 am
rob.bailey 52577 (2/11/2015)
Answer is 5, the first statement, there is a space after the *, not in the last.
Good eye. I am uncertain how that space got in there, but it would be enough to make the first different from the last.... I hope that points are awarded for those who find the space and give the answer as 5. I find this odd because I tested the code in 2008R2 and 2012 before submitting it.
I meant not to include terminal spaces on a line as part of the problem because that makes the question not just tricky but downright deceptive: I like a QOD to be solvable on inspection, and the space is not apparent when viewing the browser.
This flaw in my question does highlight the potential impact of characters we don't see.... Can anyone think of other cases where unseen characters cause unexpected results? One comes to my mind....
February 11, 2015 at 7:01 am
pmadhavapeddi22 (2/11/2015)
I have checked the rows in sys.syscacheobjects before/after running the selects, found 5 new rows with all the selects mentioned in QOD.select * from sys.syscacheobjects where objtype ='adhoc' and sql like '%bob%'
I answered 5 but the answer is 4, dont know where should I get the data for adhoc cache objects
Thanks for the intereting question.
Got 5 rows, although the answer might be wrong, but the question is interesting & different, thanx a lot.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
February 11, 2015 at 7:10 am
Can anyone think of other cases where unseen characters cause unexpected results? One comes to my mind....
Yeah, I remember once upon a time a tested piece of code (turned out to be not tested @ all) went to production server in the Constants table with an extra trailing space for 1 critical parameter, bad news came very fast, application is crashing, spent some time to figure out that extra space @ the end of the parameter, this is what I call: Learning, the hard way. 🙂
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
February 11, 2015 at 7:25 am
rob.bailey 52577 (2/11/2015)
Answer is 5, the first statement, there is a space after the *, not in the last.
+1
February 11, 2015 at 8:52 am
This is an excellent question. Simple example that teaches a very important point.
February 11, 2015 at 8:58 am
jshahan (2/11/2015)
This is an excellent question. Simple example that teaches a very important point.
Agreed. It would be nice if the actual answer was one of the options 😀
February 11, 2015 at 9:30 am
Carlo Romagnano (2/11/2015)
pmadhavapeddi22 (2/11/2015)
I have checked the rows in sys.syscacheobjects before/after running the selects, found 5 new rows with all the selects mentioned in QOD.select * from sys.syscacheobjects where objtype ='adhoc' and sql like '%bob%'
I answered 5 but the answer is 4, dont know where should I get the data for adhoc cache objects
+1
I run this query:
SELECT usecounts, cacheobjtype, objtype, a.*FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) as a
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%'
AND [text] LIKE '%PlanName%'
and objtype='Adhoc'
ORDER BY usecounts DESC;
It returns 5 rows. The bug is in the comment:
SELECT *
FROM Plans
WHERE PlanName = 'Bob'
-----------------------
SELECT *
FROM Plans
WHERE PlanName = 'Bob'
/* Same as first select */
-----------------------
SELECT *
FROM Plans
WHERE PlanName = 'Bob'
-----------------------
select *
from Plans
where PlanName = 'Bob'
-----------------------
SELECT *
FROM Plans
WHERE PlanName = 'Bob'
-----------------------
Not just the presence of the comment. There is also a space after 'Bob' in that same query.
The correct answer should be 5. But because the OP meant for us to consider that buggy query to be exactly the same as the first, I could see how the answer might be 4.
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
February 11, 2015 at 10:10 am
One of those occasions when it pays to answer the question without running the code!
February 11, 2015 at 12:15 pm
I should have gone with my gut. Was going to answer 4 because of the comment in the code about the one select being the same as the first. But then I ran it all and checked the cached objects and saw 5 so I figured that had to be correct.
Nice question though!
February 11, 2015 at 11:53 pm
Very Interesting question.Thanks.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply