asked
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...
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.
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
The bottom line in it all is that SQL Server does not want to allow any scenarios where "wrong" results are possibly created.
Also by using Synonym, we can use temporary table in function
http://www.sqlservercentral.com/articles/UDF/74231/
