New FAQ... 1

[vongrunt]

Last few week we had some interesting threads about random numbers & stuff.

And I had two hours free (... server is still crunching monthly batches).

So... faq183-6047

Comments, suggestions and constructive critics are welcomed as usual.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mrdenny]

FAQ looks good.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[DrSql]

Its has some really helpful tips.

Dr.Sql
Good Luck.

 

[ESquared]

Arguably no less important, certain ND functions cannot be used within user-defined functions (UDFs). List includes RAND(), GETDATE(), GETUTCDATE() and NEWID(). Why is that so, we can only guess . Perhaps they are... too nondeterministic? Anyway, if you were about to write function called fn_rollDice - now it's time to stop

Not exactly. You can cheat. Use views to get around nondeterminism. This stuff only tested in SQL Server 2000.

--Current date function
CREATE VIEW MyDate AS SELECT TheDate = GetDate()
GO
CREATE FUNCTION dbo.Now() RETURNS datetime AS BEGIN RETURN (SELECT TheDate FROM MyDate) END
GO
PRINT dbo.Now()
DROP FUNCTION dbo.Now
DROP VIEW MyDate

--Random number function
CREATE VIEW MyRand AS SELECT TheRand = Rand()
GO
CREATE FUNCTION dbo.RandInt(@From int, @To int) RETURNS int AS BEGIN RETURN (SELECT (@To - @From + 1) * TheRand + @From FROM MyRand) END
GO
PRINT dbo.RandInt(10, 20)
DROP FUNCTION dbo.RandInt
DROP VIEW MyRand

You can also use objects to get around nondeterminism. Here's an example using the Windows Scripting Host object:

CREATE FUNCTION dbo.SpecialFolder(@FolderName varchar(100))
RETURNS varchar(500)
AS
BEGIN
   DECLARE
      @Result int,
      @ShellObj int,
      @OutString varchar(500)
   EXEC @Result = sp_OACreate 'WScript.Shell', @ShellObj OUTPUT
   IF @Result = 0 EXEC @Result = sp_OAGetProperty @ShellObj, 'SpecialFolders', @OutString OUTPUT, @FolderName
   IF @ShellObj <> 0 EXEC SP_OADestroy @ShellObj
   RETURN @OutString
END
GO
PRINT dbo.SpecialFolder('Desktop')
DROP FUNCTION dbo.SpecialFolder

All of these are non-deterministic functions.

Erik

 

[ESquared]

I should warn you not to use any DLL-based object in a production environment without asking your database admin. They run in-process, so they can crash the server if they have an unhandled error.

You can prove it's a DLL being loaded by trying to use context option 4 on sp_OACreate and observing that it fails. See BOL for more info.

There might be some method of calling a DLL from an EXE that could isolate crashes from the server, but I haven't looked into that yet.

Erik

 

[vongrunt]

> Not exactly. You can cheat. Use views to get around nondeterminism. This stuff only tested in SQL Server 2000.

FYI originally I had some doubts about that hack - too much "breaking the tool" syndrome present, plus UDFs aren't speed demons anyway so view will make things even worse. Maybe it is worth mentioning in FAQ but with some "I-told-you-so" warnings... your opinion?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[ESquared]

Before we can assess it, we have to understand why the SQL Server design team decided that UDFs must be deterministic. Is it just an expression of some deeply held but unrealistic idealism about functions? Or are there larger issues?

I can certainly understand why most functions should be deterministic. I'd be irritated if floor(1.234) gave 1 sometimes and 2 other times. But GetDate() is a perfect example of a function where nondeterminism is the entire point. I'd be just as irritated if it always gave 'Jan 23, 8267 16:24:58.997'.

Can UDFs be used in calculated table columns? That could explain it. But in that case, instead of banning nondeterminism in UDFs entirely, there should be a provision for writing either kind. Something like:

CREATE FUNCTION Blah () NONDETERMINISTIC
RETURNS datetime
AS
BEGIN RETURN GetDate() END

And instead of saying "Invalid use of 'getdate' within a function." it might say "Use of 'getdate' within a function requires function creation keyword 'NONDETERMINISTIC'."

As I demonstrated with the windows scripting host object, though, you could still break determinism if any access to objects is allowed. And that's a problem because there may be some objects which have functions or properties that are in fact deterministic (such as regexp operations, maybe). There's no conceivable way for SQL Server to enforce this when accessing outside code.

And so what if I choose to use a nondeterministic UDF in a calculated table column? It's my responsibility to ensure that my database performs the way I want it to. If I use such things it should be expected I know the potential side-effects (such as values changing unexpectedly, or not changing when expected, or joins against the column failing or improperly joining, or updates or deletes missing rows or selecting too many rows, and so on...).

My instinct says that requiring UDFs to be deterministic is more about protecting uneducated people from confusing results rather than about any dire database integrity requirement. But I really don't know.

 

[vongrunt]

> I can certainly understand why most functions should be deterministic. I'd be irritated if floor(1.234) gave 1 sometimes and 2 other times. But GetDate() is a perfect example of a function where nondeterminism is the entire point.

Probably because such functions depend primarily on time dimension. All other "allowed" nondeterministic functions depend on environment and current state - but not time itself.

> And instead of saying "Invalid use of 'getdate' within a function." it might say "Use of 'getdate' within a function requires function creation keyword 'NONDETERMINISTIC'."

SQL Server calculates UDF determinism anyway; this clause would be nothing but formality. There must be some (time-dependent?) reason why GETDATE() and stuff aren't allowed in a first place. Originally I thought this is some historical anti-n00b measure . Or maybe something related to distributed/heterogeneous/federated query issues. Now I don't do anymore .

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[DotNetGnat]

vongrunt, thats a very useful FAQ....thanks and have a star

-DNG

 

[ESquared]

>Or maybe something related to distributed/heterogeneous/federated query issues.

That's what I'm talking about. If these issues exist, inform us, and let us do what we want. Don't just say "no non-determinism in functions.