x

I have some questions on udf?

Q1) why dml operations are not allowed in user defined functions for database tables?

Q2) Why cannot we have non deterministic functions e.g. getdate() in UDF?

Q3) Why functions cannot use ntext, image and timestamp data types as return type?

Q4) Why cannot a function have output parameter?

Q5) Why we cannot use Try..Catch block inside a function?

Q6) Why Temporary table (#temp) and Derived table cannot be created on function?

Q7) Why cannot we use print statement in udf?

Q8)We cannot call stored procedures(exception for Extentended stored procedures ) from udf. Why?

Thanks in advance...
more ▼

asked Jun 28, 2011 at 09:03 PM in Default

learner gravatar image

learner
302 17 18 20

Let me add a comment to @Magnus Ahlkvist's excellent answer. UDFs are a very limited tool. They look attractive at first because in theory it allows you to encapsulate your code. But they really don't function very well. Scalar UDFs can effectively become a cursor when applied to your queries. Table Valued UDFs can work relatively well if they're not overly complex, but people tend to nest them (have one function call another, call another, etc.) or JOIN them, and this leads to major issues. Multi-Statement Table Valued UDFs have no statistics so they only work well with really small data sets (say, less than 100), and perform extremely poorly with larger data sets and suffer very badly when you nest them or JOIN them (worse than the Table Valued UDFs).

All that said, what are you trying to do with UDFs that you need all that functionality? I'd suggest you should stick with stored procedures.
Jun 29, 2011 at 04:46 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

Hi,

Here's my attempt to answer your questions.

  • Q1: DML operations are not allowed because a UDF in SQL Server must not change the state of the database. A DML operation will change the actual database. A UDF should enly return a value. To change state, Stored Procedures should be used.
  • Q2: The fact is that you CAN use getdate() and other non deterministic functions in UDFs from SQL Server 2008. Before that, it was not allowed. The reason is pure definition - in SQL Server 2000 and SQL Server 2005 a UDF was defined as deterministic and therefore it wouldn't make sense to allow it to be NON deterministic.
  • Q3: I actually don't know why, but I know that from SS2005 you should use varbinary(max) and nvarchar(max) instead of image and ntext, and they can be returned from UDF.
  • Q4: An output parameter will be changed by the function, and that's in a sense the same thing as DML - it will change the state. UDFs should return things, not change things.
  • Q5: It wouldn't make much sense to use error handling inside a UDF. A UDF is not allowed to insert anything to log tables, it's not allowed to print error messages etc. Therefore error handling is to be handled from the calling context.
  • Q6: You can use derived tables in a UDF (see below). A #temp table however would change state of the database (the table is created in tempdb).
  • Q7: PRINT causes side effects (namely that something is printed to the console) and that's not allowed in UDFs.
  • Q8: A stored procedure might produce side effects (change the state) and can therefore not be called from a UDF.

EDIT

I forgot to post the example of derived tables in UDF. Here it goes.

CREATE FUNCTION dbo.fnTest()
RETURNS int
AS
BEGIN
    DECLARE @i int
    SELECT @i=c FROM (SELECT COUNT(*) AS c FROM sys.indexes) AS t
    RETURN @i
END
/EDIT
more ▼

answered Jun 28, 2011 at 11:37 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.4k 16 19 33

The bottom line in it all is that SQL Server does not want to allow any scenarios where "wrong" results are possibly created.
Jun 29, 2011 at 05:52 AM Blackhawk-17
@Magnus Ahlkvist should get +8 for the litany of answers
Jun 29, 2011 at 10:46 PM KenJ
Also by using Synonym, we can use temporary table in function http://www.sqlservercentral.com/articles/UDF/74231/
Jul 21, 2011 at 04:12 AM learner
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x713
x343
x237
x8

asked: Jun 28, 2011 at 09:03 PM

Seen: 2346 times

Last Updated: Jun 28, 2011 at 09:16 PM