An Introduction to SQL Server Scalar UDFs

Now that we have encrypted the function, there is no way we can decrypt it again. So be careful while you encrypt a function. Review or execute the following script and note the output:

SELECT    

        sysobjects.name AS [Function Name],

        syscomments.text AS [Function Definition]

FROM

        sysobjects INNER JOIN

        syscomments ON sysobjects.id = syscomments.id

WHERE

        (sysobjects.xtype = 'FN')

Output:

Function Name  Function Definition

--------------------------------------------------

fnSchemaTest    Create function fnSchemaTest ….

fnEncrypTest     ????????????????????????????…

fngetEmail         Create function fngetEmail  (…

As you can see, you cannot view the definition of fnEncrypTest, since it is encrypted.

I personally feel that we should use scalar UDFs under the conditions where we need the single output with 0 or more inputs. Views cannot perform this operation, since they cannot accept input parameters. While stored procedure can in such situations, the main disadvantage of a stored procedure is that the output of the stored procedure cannot be used in SELECT statements, whereas the output of scalar user-defined functions can be used in SELECT statements. Performance-wise, SPs and scalar UDFs are same because both are compiled and optimized in the same way. An important point I would like to highlight is that UDFs are not in SQL 92 standards, while they are included in SQL 99 standards.

Published with the explicit written permission of the author. Copyright 2004.