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.

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |