Call now: (800) 766-1884  


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA

 SQL Server Scripts
 Scripts Menu




  SQL Server Tips by Gama and Naughter


Nondeterministic UDF’s

Any UDF that returns values by calling an XP is considered nondeterministic because it is possible that the XP is nondeterministic. Although all the nondeterministic system functions are not allowed in a UDF, they can be called indirectly through a view.


Example: a UDF that returns a random number between one and ten.

Using rand() would cause an error when trying to create the UDF but calling an auxiliary view will work fine. This is the code for the view:




SELECT ROUND(RAND() * 9 + 1, 0) AS RandomNumber


This is the UDF:


--UDF that returns a random number




RETURN (SELECT RandomNumber from VIEWRandom)



Inline Table-valued UDF's

This type of UDF works like a “parameterized view” because it consists of a single SELECT statement usually affected by the input parameters of the UDF. There are no more statements other than one SELECT or more if UNION is used.


Example: In database Pubs, create a UDF that will return the names of all employees hired between two dates. These two dates will be the input for the UDF:


CREATE FUNCTION UDFEmployeeHireDateRange(@FromDate datetime, @ToDate datetime)

--returns the employees hired within a certain time frame



SELECT fname, lname, hire_date

FROM  dbo.employee

WHERE (hire_date >=@FromDate ) AND (hire_date <=@ToDate )


Calling the UDF:


SELECT * FROM dbo.UDFEmployeeHireDateRange('1-1-91', '4-15-91')


There is one problem with dates, their format depends on the collation and different users might have different settings. The above call would cause an error if the date format were different from the US standard. To make this call capable of running without caring about the collation, it is preferable to use the ISO8601 (yyyy-mm-dd) format:

SELECT * FROM dbo.UDFEmployeeHireDateRange('1991-1-1', '1991-4-15')

The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter


Burleson Consulting Remote DB Administration







Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.

Burleson Consulting
SQL Server database support


Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter