SA0014 : Avoid 'fn_' prefix when naming functions

Introduction

Using the fn_ prefix for custom functions in SQL Server can cause confusion with system-defined functions, making code harder to understand and maintain.

Description

In SQL Server, naming conflicts can arise when developers use the fn_ prefix for their user-defined scalar functions. This prefix is commonly associated with system functions provided by Microsoft, which could lead to confusion or unexpected clashes as new functions are introduced in updates.

User-defined function with a potentially problematic prefix:

CREATE FUNCTION fn_MyFunction(@param INT) RETURNS INT
AS
BEGIN
    RETURN @param + 1;
END;

This example is problematic because by prefixing with fn_ , the function name might conflict with future system functions or create confusion, requiring additional maintenance work or debugging.

Increased risk of namespace clashes with future SQL Server updates that introduce new system functions.
Potential confusion among developers and DBAs, leading to errors or difficulties in maintaining code.

How to fix

Avoid using the fn_ prefix when naming user-defined scalar functions to prevent potential naming conflicts with system functions in SQL Server.

Follow these steps to address the issue:

1.Identify user-defined scalar functions in your database that use the fn_ prefix in their names.

2.Rename these functions to exclude the fn_ prefix and avoid any potential confusion or conflicts. Ensure the new name is meaningful and follows your team’s naming conventions.

3.Update any database objects, scripts, or applications that reference the renamed functions to reflect the new names.

4.Test the updated functions and dependent objects to confirm that they work correctly with the new names.

Renaming a potentially problematic user-defined function:

ALTER FUNCTION MyFunction(@param INT) RETURNS INT
AS
BEGIN
    RETURN @param + 1;
END;

-- Update references in queries
-- UPDATE some_table SET result = MyFunction(column_value);

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

2 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE FUNCTION dbo.fn_myfunction
(
     @value AS int
)
RETURNS int
WITH EXECUTE AS  CALLER
AS
 BEGIN
    SET @value=@value + 1
END;

Analysis Results

	Message	Line	Column
1	SA0014 : Avoid ‘fn_’ prefix when naming functions.	1	20

Analysis Rules