Programming in MS SQL Server 2012

User Defined Function

UDF - User-defined functions are subroutines that can defines by one or more Transact-SQL statements to encapsulate code for reuse. User-defined functions are created using the CREATE FUNCTION statement, modified using the ALTER FUNCTION statement, and removed using the DROP FUNCTION statement. By default, function categories into three types like Scalar Function, Table-valued Function and System Functions.
Advantage of Function
You can create the function once, store it in the database, and call it any number of times in your program. User-defined functions can be modified independently of the program source code.
Similar to stored procedures, Transact-SQL user-defined functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times.
CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.
An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. The function can then invoked in the WHERE clause to reduce the number or rows sent to the Application or Database users.
Limitations of Function
User-defined functions cannot be used to modify base table information. The DML statements INSERT, UPDATE, and DELETE cannot be used on base tables.
SQL functions that return non-deterministic values that are not allowed to be called from inside User-defined functions. An example, a GETDATE() is a non-deterministic function, the function return value is variant whenever call GETDATE().
Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported.
User-defined functions cannot contain an OUTPUT INTO clause.
Error handling is restricted in a user-defined function. A user-defined function does not support TRY ... CATCH, @ERROR or RAISERROR statements.
User-defined functions cannot call a stored procedure, but can call an extended stored procedure.
User-defined functions cannot make use of dynamic SQL or temp tables, but table variables are allowed.
SET statements are not allowed in a user-defined function.
User-defined functions are not support following System Functions...

@@CONNECTIONS	@@PACK_SENT	GETDATE()
@@CPU_BUSY	@@PACKET_ERRORS	GETUTCDATE()
@@IDLE	@@TIMETICKS	NEWID()
@@IO_BUSY	@@TOTAL_ERRORS	RAND()
@@MAX_CONNECTIONS	@@TOTAL_READ	TEXTPTR()
@@PACK_RECEIVED	@@TOTAL_WRITE	-

* * * * *

Your Comment To AUTHOR