User-defined function

Question

User-defined function

Viewing 6 posts - 16 through 22 (of 22 total)

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

sgmunson SSC Guru Points: 110651 More actions · Answer 1

fnndala2 (8/5/2015)
@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.

Then perhaps the answer is: "The query within the function will run exactly once for each time the function is called, and may well return entirely different results on each execution because of the lack of an ORDER BY clause." If the test was either oral or required a written out answer, that could be stated, but with a multiple choice test, who knows.... You have to wonder if they meant to ask how many records will be returned, but even then, it's impossible to answer without knowing the number of records in the table being selected from. If there are at least 10 records in that table, the you'll get 10 records back, but if not, you'll get less, but always get all the records in the table. Sounds to me like whoever created this question has either set up a "trick question", or doesn't know what they're doing.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Grant Fritchey SSC Guru Points: 399330 More actions · Answer 2

sgmunson (8/6/2015)
Sounds to me like whoever created this question has either set up a "trick question", or doesn't know what they're doing.

Why not both?

"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

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

Luis Cazares (8/5/2015)
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. 😀

So broken out of the box? Time to return to sender.;-)

I would also agree, the question is worthless as stated.

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

SQLRNNR SSC Guru Points: 281344 More actions · Answer 4

Jeff Moden (8/5/2015)
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.

I guess if that is the case, then it is not totally worthless. I have seen my fair share of exams like that.

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

sgmunson SSC Guru Points: 110651 More actions · Answer 5

Grant Fritchey (8/6/2015)
sgmunson (8/6/2015)
Sounds to me like whoever created this question has either set up a "trick question", or doesn't know what they're doing.
Why not both?

At the time I wrote that, I thought about the "ace breaker" concept as well as the idea that it might be to see if you catch the error, so I was willing to allow for the possibility that it might well be one or the other... However, that said, it wouldn't take much to convince me that it's both. If some of the folks operating as recruiters these days are any example, very little convincing indeed...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

SQLRNNR SSC Guru Points: 281344 More actions · Answer 6

fnndala2 (8/6/2015)
@jeff Moden that might be true....SQL-Server training takes the whole day and I might missed some of the query I should reference from .
Thank you:-)
I wrote the test again today and thank God there was no question like this and I passed:-)

I am curious, what test was this? Was it a Microsoft test?

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