Home What's at risk when we circumvent SQL Server's limitation for side effect operations in UDF?
Reply: 1

What's at risk when we circumvent SQL Server's limitation for side effect operations in UDF?

dlatikay
1#
dlatikay Published in 2017-12-07 16:44:28Z

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?

Alan Burstein
2#
Alan Burstein Reply to 2017-12-07 19:03:30Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.303149 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO