2

Today I used the approach in this answer to great success, to replace names, insurance numbers and addresses with randomized garbage in multiple instances of the same database schema, depending on a "test" / "production" flag in the data.

Background: Trying to do

CREATE FUNCTION dbo.FailsToCreate() 
RETURNS uniqueidentifier 
AS 
BEGIN 
  RETURN NEWID()
END

inevitably fails with

Msg 443, Level 16, State 1, Procedure FailsToCreate, Line 6 [Batch Start Line 27] Invalid use of a side-effecting operator 'newid' within a function.

Now we can be badass enough to do

CREATE VIEW dbo.vwGuessWhat AS SELECT NEWID() Fooled

which surprisingly allows us to make it work with

CREATE FUNCTION dbo.SuddenlyWorks() 
RETURNS uniqueidentifier 
AS 
BEGIN 
  RETURN (SELECT Fooled FROM vwGuessWhat)
END

Documentation is silent about consequences. It merely lists the functions that cannot be used, and does not mention a possibility to bypass the limitation.

Can I safely continue to use this approach in production code, or is there a danger in bypassing SQL Server's validation that will cause it to malfunction?

Cee McSharpface
  • 8,493
  • 3
  • 36
  • 77
  • What does the documentation say? And why would you believe a random person's opinion over the official documentation anyway? –  Dec 07 '17 at 16:48
  • [the documentation](https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/user-defined-functions) is silent about consequences. It merely lists the functions that cannot be used, and does not mention a possibility to bypass the limitation. Why you think this question will attract opinionated answers? I'd like to see hard facts, like "in scenario X it will malfunction because of Y". – Cee McSharpface Dec 07 '17 at 16:57
  • Then that is **Off-Topic: Too-Broad**. Please read [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask) before attempting to ask more questions. –  Dec 07 '17 at 16:59
  • Please read [What types of questions should I avoid asking?](http://stackoverflow.com/help/dont-ask) before attempting to ask more questions. –  Dec 07 '17 at 16:59
  • *You cannot call a non-deterministic function from inside a user-defined function.*, that is pretty clear from a simple google search, first page. –  Dec 07 '17 at 17:03
  • I see. One of the top results includes https://stackoverflow.com/a/31468879/1132334, which is the same kind of undocumented workaround, avoiding the SQL server error message, not telling if that's a safe thing to do. therefore I originally thought this could be a legitimate question to ask. – Cee McSharpface Dec 07 '17 at 17:06
  • 2
    @dlatikay I actually agree that it is a legitimate question; however, I think the answer is "it isn't defined, or: it is defined as illegal" (depending on how you interpret it) - the only people who could answer this are the SQL Server devs. Either way, *personally* I wouldn't use something if the behaviour was either not defined, or defined as illegal. – Marc Gravell Dec 07 '17 at 17:07
  • 1
    in that work around you are **not** calling a non-deterministic function from inside a UDF, you are SELECTing a value from a view, which is not the same thing, even if the view is providing you what you want. –  Dec 07 '17 at 17:10
  • What are you trying to achieve? One (ugly) workaround - if you want to be absolutely sure - was to *inject* the non-deterministic value. Create a parameter for your function and call it like `dbo.TheFunction(NEWID())`. But you have to deal with a possible `NULL` in this case... – Shnugo Dec 07 '17 at 17:23
  • 1
    My personal opinion (uhm, this seems to be opinion-based in a way...): I'd use the `VIEW` approach. Many people do it in order to create a random number (e.g. by taking the first 4 bytes of `NEWID()` and cast them to `INT`. This is because a repeated call to `RAND()` would come back with the same result for all calls...) If Microsoft would *repair* this, a lot of productive code would break... – Shnugo Dec 07 '17 at 17:26

1 Answers1

1

There is no risk per se. You present an interesting way to circumvent a limitation with SQL functions.

On a seperate note, one of the many problems with scalar udfs is that they kill parallelism. In other words, queries that use dbo.SuddenlyWorks() will always run serially, even if you use Adam Machanic's make_parallel() or traceflag 8649.

If you wanted a parallel plan you would need to make dbo.SuddenlyWorks() an inline table valued function.

Alan Burstein
  • 7,770
  • 1
  • 15
  • 18