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...
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