SQL Server Coding Standards: Working with User Defined Functions

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Prepared by Bihag Thaker

Prefix the User Defined Function Name with ‘udf_’.

Try to include only letters in identifiers by using Pascal Casing and avoid the use of special characters in identifiers. Underscore (_) character may be used for element separation in an identifier.

Here element means Application Prefix, type of Operation, Table Name or Entity Name etc. However, word separation in single element should be achieved with PascalCasing only.

Use proper, meaningful and self-explanatory identifiers for user defined functions. Following naming convention should be used for stored functions:

‘udf_’ + + ‘_’ + +

Here, part can be optional. can be a verb like ‘Get’, ‘Check’, ‘Validate’ or ‘Return’ and so on. Some of the examples are:

1 2	udf_Person_ValidateEmailAddress() udf_HR_CalculateNetSalary()

Use schemas to separate different sets of user defined functions across multiple applications when possible. For example, if schema Person is used instead of prefix ‘Person_’ and schema HR is used instead of prefix ‘HR_’, then above user defined functions should be as follows:

1 2	[Person].[udf_ValidateEmailAddress]() [HR].[udf_CalculateNetSalary]()

Do not create Scalar-Valued Function extensively. Avoid use of Scalar-Valued Functions in queries as much as possible because they degrade the performance of the queries.

Do not create Scalar-Valued Function just for the purpose of code reusability. If same functionality can be achieved with some inline calculation, then avoid functions. Code Reusability should not come at application’s performance cost.

Do not create Multiline Table-Valued Function as they tend to degrade the performance of the queries. If the same functionality can be achieved with Inline Table-valued Function, then avoid using Multiline Table-valued Function.

While creating stored procedures, specify the name of the schema explicitly within which the stored procedure is to be created even if it is dbo.

Following is the sample template of User Defined Function. It has been provided here only to have a basic idea of coding structure and standards that a stored procedure should follow: