I have some questions on udf?

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