en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
ספט8

Written by: ronen ariely
08/09/2013 10:16 RssIcon

Introduction

RAND / NewID and some more build in function have a side-effecting, and cannot be used directly in a UDF. 

try to execute this:

CREATE FUNCTION UsingRandFunc()
RETURNS float
AS BEGIN
  RETURN rand()
END
GO
Msg 443, Level 16, State 1, Procedure UsingRandFunc, Line 10
Invalid use of a side-effecting operator 'rand' within a function.


One of the workaround is to create a View and your own UDF as random function like this:

IF OBJECT_ID (N'ArielyRandView') IS NOT NULL
    DROP VIEW ArielyRandView;
GO
CREATE VIEW ArielyRandView
AS
    --SELECT CONVERT(int, RAND()*10000) as RandNumber
    SELECT RAND() as RandNumber
GO
 
IF OBJECT_ID (N'ArielyRandFunc') IS NOT NULL
    DROP FUNCTION ArielyRandFunc;
GO
CREATE FUNCTION ArielyRandFunc()
RETURNS float
AS BEGIN
  RETURN (SELECT RandNumber FROM ArielyRandView)
END
GO

now we can use our random function and execute the original code:

CREATE FUNCTION UsingRandFunc()
RETURNS float
AS BEGIN
  RETURN dbo.ArielyRandFunc()
END
GO


* I have this View on my Accessories DB. I have a database with general functions, Views, SP's, CLR's and so on… all I want to use as Accessories. As I don’t like to use system database for this and this is very easy to copy to each of my servers at once. This view is there…

Now you can use your "ArielyRandFunc" function instead of the build in RAND function.

Comments

* using RAND with 'select from table' will bring you the same value for all the records, while using your own UDF random function will bring you a new value for each record (this is only one sample when this is helpful). Try this:

select RAND(),[dbo].[ArielyRandFunc]()
from dbo.[2121212121212]

* using View & UDF is less optimal then using direct functions most of the time. There for if you can use direct build in function try it fist. For example you can use: ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) as random number Instead of a function and use it directly as random function.

* I have tested using SQLQueryStress, using 2 threads and 100000 Iterations those two queries and i got that using NEDWID is much faster & Much less CPU. I am not sure if this will be the case for any use, I actually did not expect these results, as do a large number of "type converting" used when using NEWID (this is not as a result of using int Vs float... i have tried the function using int in the view by using "SELECT CONVERT(int, RAND()*10000)" in the view).

Query 1: select [dbo].[RandFn]() AS [RandomNumber]  from dbo.[MyTable] 
Query 2: select ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber] from dbo.[MyTable]