SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by mverma4you / TSQL / Latest Posts

RE: TSQL

Ray Herring — Wed, 22 Apr 2009 08:23:53 GMT

The answer is clearly both Yes and No. From BOL (2005)[quote]All mathematical functions, except for RAND, are deterministic functions. This means they return the same results each time they are called with a specific set of input values. RAND is deterministic only when a seed parameter is specified. For more information about function determinism[/quote]So clearly the answer is YES IF you provide a seed value.However another BOL (2005) reference states [quote]The following built-in functions from other categories are always nondeterministic[/quote]and then includes RAND in the list.

RE: TSQL

Jan Van der Eecken — Tue, 21 Apr 2009 01:31:50 GMT

The QoTD did not mention which version of SQL it was referring to. That's why further up I said that the link given in the explanation mentions SQL2000, and that under SQL2K8 (and SQL2K5, which I subsequently confirmed) the only functions out of that list that cannot be used are RAND() and NEWID().Hope this clears things up :-)Jan

RE: TSQL

piyush.dhangar — Tue, 21 Apr 2009 01:19:18 GMT

Jan,I am using SQL Server 2000 and it does not allow me to use getDate() inside a function, when I compile your function it gives me error Msg 443, Level 16, State 1, Procedure usfn_GetDateTime, Line 7Invalid use of 'getdate' within a function.

RE: TSQL

Jan Van der Eecken — Tue, 21 Apr 2009 01:10:36 GMT

Piyush,I beg to differ. Try this:[code]CREATE FUNCTION [dbo].[usfn_GetDateTime] ( )RETURNS datetimeASBEGIN RETURN GETDATE ( )ENDGOSELECT [dbo].[usfn_GetDateTime]()[/code]Although GETDATE() is clearly non-deterministic this compiles properly and the SELECT statement returns the expected result. All that really happens is that your own UDF will be flagged as non-deterministic if it contains calls to other non-deterministic functions (be they built-in or another UDF) or extended stored procedures. This in turn puts some restrictions on where such a UDF can be used.Regards,Jan

RE: TSQL

piyush.dhangar — Mon, 20 Apr 2009 07:03:37 GMT

Answer is NO , explanation is you can not use NON Deterministic functions inside a UDF

RE: TSQL

Douglas Duncan — Wed, 15 Apr 2009 18:07:42 GMT

[quote][b]Jan Van der Eecken (4/15/2009)[/b][hr]Douglas, you are quite correct when you say that MSDN lists all of the above as built-in functions that cannot be used in a UDF because they are non-deterministic. However, you should note that in the heading of the article it also says [b]Creating and Maintaining Databases (SQL Server 2000)[/b]. Under SQL 2K8 (and presumably SQL2K5 as well, didn't try that) only two of those can not be used, namely RAND() and NEWID(). So, the limitation these days is really that one cannot use built-in functions that potentially may have side-effects.[/quote]Thanks for the correction Jan. I didn't even pay attention to the version that the text was for when posting my note. I just blindly used the link provided as the reference. It would be nice if the questions listed which version of SQL they pertained to since there are situations where an answer could be different based off the version.Thanks to these questions and people like yourself posting on these boards I have learned more about SQL than I would have done on my own.

RE: TSQL

Jan Van der Eecken — Wed, 15 Apr 2009 05:06:51 GMT

[quote][b]Douglas Duncan (4/11/2009)[/b][hr]The correct answer is no, and I would imagine that the people in power will correct the issue soon. In addition to the explanation given in the answer, the following which is taken directly from the MSDN page used as a reference states you cannot use RAND (in addition to several other built-in functions) in UDFs:Built-in functions that can return different data on each call are not allowed in user-defined functions. The built-in functions not allowed in user-defined functions are:@@CONNECTIONS@@CPU_BUSY@@IDLE@@IO_BUSY@@MAX_CONNECTIONS@@PACK_RECEIVED@@PACK_SENT@@PACKET_ERRORS@@TIMETICKS@@TOTAL_ERRORS@@TOTAL_READ@@TOTAL_WRITEGETDATEGetUTCDateNEWIDRANDTEXTPTR[/quote]Douglas, you are quite correct when you say that MSDN lists all of the above as built-in functions that cannot be used in a UDF because they are non-deterministic. However, you should note that in the heading of the article it also says [b]Creating and Maintaining Databases (SQL Server 2000)[/b]. Under SQL 2K8 (and presumably SQL2K5 as well, didn't try that) only two of those can not be used, namely RAND() and NEWID(). So, the limitation these days is really that one cannot use built-in functions that potentially may have side-effects.

RE: TSQL

tosscrosby — Tue, 14 Apr 2009 06:17:44 GMT

[quote][b]Vinay (4/13/2009)[/b][hr]When we study on the question and give answer and found that the answer is wrong we feel bad... so if we answer correct we should get the responce otherwise there will be confusion in our mind abt the concept for future....thats my thining.....Thanx.Vinay[/quote]My thinking is, if I know my answer is correct, there is no confusion about the concept. I treat it as a trick question designed to get me to think about it a little more in-depth and polish my skills a bit. Microsoft, and forums like these, have made it so easy to find resolutions without a lot of effort. QOTD forces you to think a bit and apply what you've learned to come up with the correct answer. Right or wrong, the old brain starts grinding and you have to apply yourself as opposed to simply waiting for a reply to a post (I know I've done that many times). Just my 2 cents so early in the morning...

RE: TSQL

Steve Jones - SSC Editor — Mon, 13 Apr 2009 09:20:32 GMT

The wrong answer was marked correct. This has been fixed and points awarded back.

RE: TSQL

Vinay Thakur-585143 — Mon, 13 Apr 2009 08:36:36 GMT

When we study on the question and give answer and found that the answer is wrong we feel bad... so if we answer correct we should get the responce otherwise there will be confusion in our mind abt the concept for future....thats my thining.....Thanx.Vinay

RE: TSQL

Lynn Pettis — Mon, 13 Apr 2009 08:31:11 GMT

There is, of course, a way around this problem. Simply build a view that returns the value of the function RAND() and use the view in your UDF.You can find an example using NEWID() in my blog [url=http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/04/04/a-variable-length-random-string.aspx][b]A Variable Length Random String[/b][/url].

RE: TSQL

Lynn Pettis — Mon, 13 Apr 2009 08:27:18 GMT

Well, as of now (8:26 MDT) the answer hasn't been corrected.Still says Yes is the correct answer.

RE: TSQL

ghollis — Mon, 13 Apr 2009 07:34:33 GMT

I am assuming that this will be corrected in a while, with No being set to the correct answer?

RE: TSQL

Mauve — Mon, 13 Apr 2009 07:27:28 GMT

[quote][b]Noel McKinney (4/13/2009)[/b][hr]It appears the correct answer is determined using the RAND() function.[/quote] Best explanation yet for the correct, er, incorrect answer!:-D

RE: TSQL

Noel McKinney — Mon, 13 Apr 2009 06:44:16 GMT

It appears the correct answer is determined using the RAND() function.

RE: TSQL

dgabele — Mon, 13 Apr 2009 06:36:36 GMT

I agree. I think the correct answer is No...

RE: TSQL

Richard M. — Mon, 13 Apr 2009 06:34:37 GMT

I love it when the explanation contradicts the shown correct answer.......

RE: TSQL

Ronald H — Mon, 13 Apr 2009 06:15:24 GMT

No! Yes! .... Maybe? should be a third option then... As what a former poster said, it sounds like SQL Server has a female intuition inside :)

RE: TSQL

Kevin Brown-392626 — Mon, 13 Apr 2009 06:15:01 GMT

The question is sort-of tricky. the real answer is "Indirectly" there are tricks to use rand() when you normally shouldn't be able to...

RE: TSQL

rgillings — Mon, 13 Apr 2009 06:03:34 GMT

Seems to have been an oversight. Answer is clearly No...

RE: TSQL

Bill Mell — Mon, 13 Apr 2009 04:12:18 GMT

The answer is No, as the explanation describes, yet if you answer no it is incorrect?How can this be?Is there some secret I am missing here?

RE: TSQL

Raamamohan — Mon, 13 Apr 2009 03:27:10 GMT

Yes I do agree. But Answer is "NO". Why they have given "Yes" and explanation 'No'. Its totally contradiction.

RE: TSQL

mithun.gite — Mon, 13 Apr 2009 03:00:05 GMT

[quote][b]mverma4you (4/13/2009)[/b][hr]don't cry too much this is not your Board exam. Regarding the point moderator will check for the point.:-D. I can't modified the answer of this question and motive of QOD it just to update yourself on various SQL topic. So even the question’s answer was as wrong but you learn something about the RAND() function.[/quote]What kind of explanation is that ???

RE: TSQL

mverma4you — Mon, 13 Apr 2009 02:12:53 GMT

don't cry too much this is not your Board exam. Regarding the point moderator will check for the point.:-D. I can't modified the answer of this question and motive of QOD it just to update yourself on various SQL topic. So even the question’s answer was as wrong but you learn something about the RAND() function.

RE: TSQL

mithun.gite — Mon, 13 Apr 2009 01:15:19 GMT

I think there is a approval process before this question comes in "QOD"....

RE: TSQL

hayzer — Mon, 13 Apr 2009 00:22:07 GMT

that was confusing!like a lady who changes her mind very every now and then.. :-Dbut anyway, i think they should give the points on this one...

RE: TSQL

Balachandra — Sun, 12 Apr 2009 23:50:05 GMT

HiCan we get any poinsts for such questions ?

RE: TSQL

ChiragNS — Sun, 12 Apr 2009 23:41:44 GMT

RE: TSQL

Douglas Duncan — Sat, 11 Apr 2009 21:03:53 GMT

The correct answer is no, and I would imagine that the people in power will correct the issue soon. In addition to the explanation given in the answer, the following which is taken directly from the MSDN page used as a reference states you cannot use RAND (in addition to several other built-in functions) in UDFs:Built-in functions that can return different data on each call are not allowed in user-defined functions. The built-in functions not allowed in user-defined functions are:@@CONNECTIONS@@CPU_BUSY@@IDLE@@IO_BUSY@@MAX_CONNECTIONS@@PACK_RECEIVED@@PACK_SENT@@PACKET_ERRORS@@TIMETICKS@@TOTAL_ERRORS@@TOTAL_READ@@TOTAL_WRITEGETDATEGetUTCDateNEWIDRANDTEXTPTR

RE: TSQL

David-420660 — Sat, 11 Apr 2009 15:56:46 GMT

I agree. The answer is wrong according to the given explanation.

RE: TSQL

mister.magoo — Sat, 11 Apr 2009 11:11:12 GMT

The answer is wrong - you click No - and get told you are wrong.then the explanation goes on to agree that you cannot use RAND...@?

TSQL

mverma4you — Sat, 11 Apr 2009 11:10:10 GMT

Comments posted to this topic are about the item [B]TSQL[/B]