Use SQL NEWID in SQL Functions as SQL Random Generator
SQL NewID function is used for selecting random row from a resultset in SQL Server databases.
Using with TOP N keyword in SELECT statements where SQL NEWID is in the ORDER BY statement, random records are selected from a table or from a set of rows.
"SELECT TOP 1 FullName FROM Customers ORDER BY NEWID ()" is a sample sql NEWID usage.
You can find a more complex use of sql NEWID in an other tsql example valid for MS SQL Server 2005 and SQL Server 2008 database instances at SELECT TOP N Random Rows For Each Category in a SQL Table.
SQL Server administrators and T-SQL developers frequently require SQL functions that return random row, random number, etc but something which is randomized.
Since user defined sql functions are easy to use in sql SELECT statements, it is useful to move MSSQL NEWID functionality into sql functions.
But there is a problem here. It is not allowed to use undetermenistic functions in SQL Server functions. Let me explain.
Let's try to create sample function whose source code is shown below.
CREATE FUNCTION GetRandomProduct()
RETURNS nvarchar(50)
AS
BEGIN
RETURN (SELECT TOP 1 Name FROM Production.Product ORDER BY NewId())
END
GO
When you execute the above sql CREATE FUNCTION script, SQL Server will throw the following sql exception message.
Msg 443, Level 16, State 1, Procedure GetRandomProduct, Line 6
Invalid use of a side-effecting operator 'newid' within a function.
You can see from the message itself that tsql NEWID operator is not allowed for a direct use in sql functions.
Let me share a work around with you sql developers and administrators which will let you use NEWID in sql function.
This work-around will enable us to build sql functions that row random rows from sql tables.
So it will be possible to create random row generator or random string generator, etc.
Here is the t-sql trick which will enable developers to use sql NEWId in sql functions.
First create a sql VIEW object using CREATE VIEW command.
The sql script of GetNewId view is given below.
As you can see the GetNewId sql view only returns single row with single NEWId() uniqueidentifier.
CREATE VIEW dbo.GetNewID
AS
SELECT NewId() AS [NewID]
GO
In the below T-SQL function, developers will notice that ORDER BY NEWID() is replaced with the newly created sql view.
Now the ORDER BY clause is in the form "ORDER BY (SELECT [NewId] FROM GetNewID)"
CREATE FUNCTION GetRandomProduct()
RETURNS nvarchar(50)
AS
BEGIN
RETURN (
SELECT TOP 1 Name
FROM Production.Product
ORDER BY (SELECT [NewId] FROM GetNewID)
)
END
GO
And if you execute the sql CREATE FUNCTION statement, you will see that the user defined sql function GetRandom bla bla bla is created successfully without any sql error.
Now let's use the NEWID in sql random function in a tsql example code.
SELECT dbo.GetRandomProduct()
Here is an other example sql codes which returns a random product for each employee using sample MS SQL Server 2008 AdventureWorks database.
SQL Server NEWID operator is working successfully in this sql function SELECT statement, too.
SELECT
e.EmployeeID, c.FirstName, c.LastName,
dbo.GetRandomProduct() AS [Random Prize]
FROM HumanResources.Employee e
INNER JOIN Person.Contact c ON c.ContactID = e.ContactID
Below is the screenshot where you can see the result of the sql random function. The most important point in this sql function script is the use tsql NEWID view. I named this sql view as dbo.GetNewID.
As summary, although sql NEWId operator is not allowed for use in sql functions, by using sql NEWID() operator in a VIEW which is called by the SQL function random rows can be selected easily.