August 5, 2015 at 2:38 am
Depends on how the query is run, can't answer that by looking at the function alone
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2015 at 3:58 am
Also, you know that a TOP without an ORDER BY is not guaranteed to get the same results each time, right?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 5, 2015 at 4:26 am
@GilaMonster It was one of the question I got in my exam and I could not answer it
August 5, 2015 at 4:27 am
@Grant Fritchey, yes I am aware of that.
This was a question I got in my exam and I could not answer it
August 5, 2015 at 4:43 am
Based on this information, we can't answer it either. The question seems pretty suspect to me. I'd suggest following up with whoever provided it and see what they thought the answer was.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 5, 2015 at 4:48 am
@Grant Fritchey thank you,I also asked around at the office but no one knows the answer
August 5, 2015 at 5:09 am
As is, it's impossible to answer.
Answering the question requires at minimum the query that the function is to be used with.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2015 at 6:13 am
Yeah, what Gail said.
It's not that we don't know the answer. It's that the question, as you have it phrased, can't be answered. There's no way to know how many times this thing gets executed based on the definition. Here's a completely different example:
CREATE PROCEDURE dbo.MyProc
AS
BEGIN
SELECT * FROM dbo.MyTable;
END
How many times will this execute?
You can't possibly know. It's just the definition of a stored procedure. It doesn't tell you what code is calling the stored procedure or how many times that code is executed. See what I mean?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 5, 2015 at 6:32 am
@Grant Fritchey thank you, I do get your concern very well and at first I was not aware that the question is missing some query calling the function but this is how it was rephrased.
August 5, 2015 at 6:57 am
fnndala2 (8/5/2015)
I have the following SQL-Query:CREATE FUNCTION fn_SelectTopEmployees ()
RETURNS TABLE
AS
RETURN
(
SELECT TOP(10) EmployeeID
FROM Employees
);
When I run the query, how many times between 1 and 10 is the user-defined function and the select statement run?
The correct answer is zero. The UDF and select statement won't run as the code just defines the function.
If you run a different query that uses the function, the answer will depend on the query itself. 😉
August 5, 2015 at 7:27 am
Well, Luis is right. It won't run and therefore will return zero rows. But that question is an extremely poor demonstration of SQL Server knowledge. However, the provided answers were between 1 and 10. Unless the person asking the question is just trying to be all tricksy (and good gosh, better to ask straight-forward knowledge questions), I still think the question, as defined here, is broken.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 5, 2015 at 7:31 am
Grant Fritchey (8/5/2015)
Well, Luis is right. It won't run and therefore will return zero rows. But that question is an extremely poor demonstration of SQL Server knowledge. However, the provided answers were between 1 and 10. Unless the person asking the question is just trying to be all tricksy (and good gosh, better to ask straight-forward knowledge questions), I still think the question, as defined here, is broken.
I also think the question is broken, that's why I gave an out-of-the-box answer. 😀
August 5, 2015 at 8:10 am
fnndala2 (8/5/2015)
I have the following SQL-Query:CREATE FUNCTION fn_SelectTopEmployees ()
RETURNS TABLE
AS
RETURN
(
SELECT TOP(10) EmployeeID
FROM Employees
);
When I run the query, how many times between 1 and 10 is the user-defined function and the select statement run?
Heh... this is why I hate tests. By itself, this question reeks. But, it might also be what is known as an "ace breaker" ("ace" being a perfect score on the test) and they normally based on some bit of esoteric knowledge given only during one of the lectures to see if you were actually paying attention. Neither the question nor the answer may actually be correct but they are testing based on what they taught.
On the other hand, the query that uses the function might be listed earlier on the test as "Reference the following query for the next x questions" and you've simply missed it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply